Observation is a dying art.
Stanley Kubrick
Category Archives: Excel Blog
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.
Tell me the facts and I’ll learn. Tell me the truth, and I’ll believe. But tell me a story and it will live with me forever.
Native American Proverb
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.
Action is the foundational key to all success.
Pablo Picasso
Extracting Data from the Web with Power Query
Power Query can import table-based data direct from the web. As an example, we can extract the public holiday dates for Western Australia from the WA government website. This process is sometimes called web scraping.
Fake Data in Excel via Python Part 2
Following on from last week’s post on creating some fake data we will expand on the functionality with a few setting changes.
Fake Data in Excel via Python – Part 1
Python in Excel is new. Since it is the year of the snake let’s slither into Python in Excel.
My wish for you is that you continue. Continue to be who you are, to astonish a mean world with your acts of kindness. The effect you have on others’ lives is the highest expression of your own.
Maya Angelou
Source: Letter to My Daughter
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.
Simplicity is the glory of expression.
Walt Whitman
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.
Introduction to Power Query 2025
In January 2025 I ran a free webinar on Power Query – the best way to import data into Excel.
170 attended and over 100 people provided feedback with an average score of 92%.
Some of the feedback is shown below.
Introduction to Power Query
10/10
Really enjoyed this webinar – the conversion of MYOB style reports is such a useful tool to know.
C Stevenson
January 2025
Introduction to Power Query
10/10
Terrific session – very clear and concise and very useful information. The explanation of the meaning of town names in WA was a bonus!
A Roberts
January 2025
You can download the materials for the session using the button below the video.
Excel Day Type Custom Function
Let’s say you need to classify a series of dates as either Weekday, Weekend or Holiday. Here’s a formula and a custom function you can try.
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!
Custom Function to Increment Dates
Excel can increment easily add days to a date but months, quarters and years require a slightly different calculation. Here’s a single function that does all three.
Excel Custom Function for Common Dates
Excel has an EOMONTH functions that returns the end of month. You can hack this function to return the start of month as well. I thought I would make it easy to return the first, last and middle of the month in one simple function.
Sequential Rank Custom Function for Excel
Excel does not have a rank function that returns sequential numbers. All Excel’s ranking functions can return duplicated ranking numbers. Here is a solution to creating a sequential ranking system with no duplicates.
Forever is composed of nows.
Emily Dickinson, Source: Poem 690
Formatted Table Running Total Custom Function
I have shared a running total formula for a formatted table many years ago. Here is a better solution that I have converted into a custom function.
Mastery is the best goal because the rich can’t buy it, the impatient can’t rush it, the privileged can’t inherit it, and nobody can steal it. You can only earn it through hard work. Mastery is the ultimate status.
Derek Sivers – How to Live
Identifying Locked Cells in Excel
The default setting in Excel is for all cells to be locked. If you want users to input in a cell you must unlock that cell before you apply sheet protection. You can use a formula to identify locked and unlocked cells.
Be sincere, be brief, be seated.
Franklin D. Roosevelt (FDR) 32nd US President
Add Hierarchy to Excel’s SORT Function
The SORT function allows you to specify which column to sort by. You can also include a hierarchy to sort by more than one column.
Stories are memory aids, instruction manuals and moral compasses.
Aleks Krotoskm, broadcaster, journalist, and social psychologist
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.