A recent update has introduced a new way to refer to ranges in Excel. This update makes it much easier to refer to flexible ranges in Excel.
Category Archives: Tips & Tricks
Keyboard Shortcut to Return to the Formula
Sometimes when you’re building a formula the range that you select is further down the sheet than the formula cell. There is a keyboard shortcut that you can press to return to the formula cell. Shout out to Mike Girvin for sharing this tip in his latest book Excel the Only App that Matters.
Modelling Home Loans in Excel
You may be aware that Excel can calculate loan repayments. But you may not know that it can also work out lots of different loan combinations based on two variables.
Last Used Row Number in a Column
The recent TRIMRANGE function update to Excel makes it easier to determine the last used row in a column.
Insert an Image in a Cell Note
Cell Notes used to be called Cell Comments and they were like a “Post-it” note for a cell. You can insert a picture in a Note. This could be useful to show a picture but not to have it take up space on the sheet or print.
Another Alt key Shortcut in Excel
When you press the Alt key there are numbers and letters that appear above the Quick Access Toolbar. These allow you to access those icons. Here is a trick I learned from Mike Girvin (ExcelisFun) to use the QAT shortcuts multiple times.
When you press the Alt key and then press another key you perform the action once.
If you want to repeat the action and you are only pressing one key for the QAT options, you can hold the Alt key down and press the number multiple times to repeat the action.
In the example below I have the Increase Font icon as my fourth icon on the QAT.
I can select a range and hold the Alt key down and press 4 multiple times to increase the font size with each press of 4.
Excel QR Code Creator
Excel has a built-in Template that can generate a QR code – see how to access it.
Using Text Dates in Excel
If you need to enter a date in a formula you typically use the DATE function to create the date. You can enter the date as Text and Excel will convert it for you.
Excel PivotTable Formats
A recent update to Excel has made a major improvement to Excel PivotTables.
Its the first week of 2025 and I just built my first pivot of the year AND the number format from the source data was applied to the PivotTable – WOOHOO!
I did a short video showing the new feature in action.
Change Excel’s Language
This made me more excited than it should. You can update a setting in Excel Options to change the Excel interface to use English UK spelling which is also Australian spelling. So, Center becomes Centre, and Customize becomes Customise – woohoo!
Grouping Images for Effect in Excel
Using textboxes by themselves can be a good way to add extra content to a spreadsheet. Combining a text box, an icon and an arrow with some colour may make it even better.
Excel Formula Updating Technique [Video]
A recent project required editing many formulas to insert an IF function to display the NA error in certain circumstances. Here’s how I did it.
Are you Partial to Calculations?
Excel has re-badged one of the Calculation Options in the Formulas tab – see below. This is a change relating to the new Python capabilities.
The middle option used to ignore Data Tables (a What If feature on the Data ribbon tab).
The newly named Partial option also ignores Data Tables plus any Python calculations that may take a long time to calculate.
Python calculations are done in the “cloud” and require an internet connection.
Selecting a Formatted Table in a Formula
You can select a formatted table when you have a cell or range selected in the table by pressing Ctr + A. But that shortcut won’t work when creating a formula that refers to a formatted table.
To select the table in a formula you must click a cell in the table and press Ctrl + Shift + Spacebar.
Excel Macro for Yesterday
I frequently use the shortcut Ctr + ; to enter today’s date in a cell. I thought it would be useful to be able to enter yesterday’s date in a cell. I wrote a one-line macro to do it for me.
Excel Hyperlink Formula Solution
Hyperlinks in Excel are a great way to navigate around a file, but they can be easily broken. Try this solution using a formula to create a hyperlink that doesn’t break so easily.
In the image below there is a formula in cell C3 that creates a hyperlink to cell A1.
Here is the formula.
=HYPERLINK("#"&CELL("address",A1),"<link text>")Simply change the cell reference from A1 to whatever cell you want to link to. This works for cell in the current sheet.
Hyperlinks to other sheets
In the image below is an example of a link to another sheet.
The formula is.
=HYPERLINK("#"&CELL("address",Report!A1),"<link text>")Again, change the reference to create a hyperlink that doesn’t break if the sheet name changes.
Pro Tip
To return after following a hyperlink press in sequence, function key F5 and then press Enter. Don’t hold them down just press F5 then press Enter.
Hyperlink Drop Down List
I recently saw a post on LinkedIn about making a drop-down list for a hyperlink interface. It was using a range name and the ADDRESS function. I thought I could streamline it with just the CELL function.
Counting the Number of Cells in an Excel Range
Excel has functions to count the number of rows and columns in a range. It doesn’t have a function to count the number of cells in a range. We can still perform the calculation with the COUNTA function.
Excel’s Magic Percentage Symbol
I recently saw a post of LinkedIn (from Patryk Samborski) that used the percentage symbol with the SEQUENCE function to produce a list of decimals and I thought I would have a play with that idea.
Double Click the Excel Icon
You can close Excel down (with multiple files open) by double clicking the Excel icon – top left of screen.
This works for the other Office apps too.
If you haven’t saved a file Excel will ask if you want to.
To close a single file down use the X on the top right of screen.
Using Emojis in Excel Formulas
You can use conditional formatting to insert symbols in cells. You can also use formulas with emojis. using range names makes it even easier.
To insert an emoji icon in a cell you can use press the Windows key and the full stop.
This opens the Emojis dialog box.
In this example we are going to insert three separate symbols in formulas.
I have named each cell that has an emoji. A1 = Tick, A2 = Cross and A3 = Dash.
You can use these names in formulas throughout the file.
The formula in cell F2 (Sales) is.
=IF(D2>E2,Tick,IF(D2<E2,Cross,Dash))
The formula in cell F3 (Costs) is.
=IF(D3<E3,Tick,IF(D3>E3,Cross,Dash))
The advantages with using formulas instead of conditional formatting is that you can format the cells. Plus using formulas in cells is easier than using formulas in conditional formats.
Naming your emojis makes then easier to use. You can use these emoji icons names in your formulas throughout the file.
Excel and Range References
The typical range reference looks something like A1:A10. You always refer to the top left cell followed by the colon followed by the bottom right cell of the range. Did you know Excel can handle you entering the last cell followed by the first and it corrects it for you?
Clear all formats
To clear all the formats for a cell or a range select the cell/range and press in sequence Alt H E F don’t hold the keys down.
All the formats will be removed.
If you need to see the underlying number for a date this is an easy way to find it.
Working with Shapes and Other Graphic Objects in Excel
Shapes can be frustrating to work with in Excel until you find out there are two types of selections with shapes.
Excel, Laptops and Function Keys
Laptop keyboards tend to re-purpose function keys to handle other features. Often the default for the function keys is the laptop features. You then must press the Fn key to access the software function key options. Look out for a FnLock option.
Excel Sheet Names and Emojis
Did you know you can use Emojis on the sheet tabs? The font on sheet tabs is small, so some emojis may not be that effective, but simple emojis can be.
Useful Excel Keyboard Shortcuts for Data Entry
When making entries in Excel there are a few keyboard shortcuts worth knowing. These can save you time and effort.
Yet Another Post on Hiding Sheets in Excel
If you want to hide sheets and then stop people unhiding them, here is yet another technique.
Sparkline Horizontal Axis Technique
Excel Sparkline charts don’t have a horizontal axis. Here is a technique that creates one in the cell above or below the Sparkline. This works best for the Column Sparkline.
SUMIFS Wildcard Limitation
SUMIFS can use wildcard characters, but the wildcards only work on text-based codes.