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.
(more…)
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.
(more…)
The recent TRIMRANGE function update to Excel makes it easier to determine the last used row in a column.
(more…)
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.
(more…)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…
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.
(more…)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. https://vimeo.com/1044630953
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!
(more…)
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.
(more…)
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.
(more…)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.
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.
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.
(more…)Hyperlinks in Excel are a great way to navigate around a file, but they can be easily broken if the sheet name changes. Try these solutions using a formula to create hyperlinks that don’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 a…
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 the CELL and INDIRECT function.
(more…)
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.
(more…)
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.
(more…)You can close Excel down (with multiple files open) by double clicking the green 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.
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 =…
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?
(more…)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.
Shapes can be frustrating to work with in Excel until you find out there are two types of selections with shapes.
(more…)
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.
(more…)
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.
(more…)
When making entries in Excel there are a few keyboard shortcuts worth knowing. These can save you time and effort.
(more…)
If you want to hide sheets and then stop people unhiding them, here is yet another technique.
(more…)
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.
(more…)
Sometimes with Excel formatting you just want to clear everything and start again from scratch. You can clear just the formats, and there is an icon you can add to the Quick Access Toolbar to make clearing all the formats earlier.
(more…)