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.
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.
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.
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!
Stories are how we learn best. We absorb numbers and facts and details, but we keep them all glued together into our heads with stories.
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 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.
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.
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
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.
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
Webinar Feedback November 2024
Financial Functions Part Two
“This accounting webinar series is one of the best for practical CPD hours. It focuses on things we actually use in our day-to-day work, like Excel models and useful tips, instead of theory or topics we may never touch depending on your area of expertise.
It’s clear, easy to follow, and you come away with skills you can put into practice right away. Highly recommend it for anyone wanting CPD that’s actually helpful.”
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.
Having Actuals, Budget and Variances for each month going across the page is a very common reporting construct. Here is an easy way to sum the correct YTD values as the year progresses.
If you want to automate journal descriptions or create sentences the TEXTJOIN function is your friend. It can combine words and insert the spaces for you.
If you dig a hole and it’s in the wrong place, digging it deeper isn’t going to help.
Print ranges can accept dynamic arrays. This means you can set up print ranges that automatically expand or contract based on dynamic array spill ranges.
Nice feedback
It is always nice to open my email in the morning and receive these types of emails.
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.
