Power Query Tip to Ignore Subfolders
Using the From Folder option in Power Query allows you to import all the files from the selected folder and all its subfolders. What if you didn’t want the subfolders? There is a solution.
(more…)Excel Formula for Duplicates Revisited
I was reviewing a blog post from last year that listed duplicates in a range. I thought I could shorten the formula using the LET function.
(more…)Flexible SUMIFS Function
The SUMIFS function is a conditional sum function that allows you to handle multiple conditions. Here is a technique to handle the situation where a condition maybe blank.
(more…)Alphanumeric Sort in Excel
If you have ever sorted codes like A1 and A100 you have found that Excel’s standard sort doesn’t usually provide the required sort sequence. Here’s a solution.
(more…)Yet Another Blank Cell Solution
When you link to an empty cell Excel it displays as zero. The same happens when you filter a list using the FILTER function. Blank cells display as zeroes. Here’s how to stop that.
(more…)Excel Two Column Lookup – Part 2
In the previous post I showed how to use XLOOKUP to do a two-column lookup. See the link below for the previous post. This time we will see how to do the two column lookup using Power Query.
(more…)Clear Data from an Excel Table
Using formatted tables in Excel is best practice. If you regularly overwrite data in a formatted table here is a quick way to clear the data before pasting in updated data.
(more…)Excel and Financial Years and Months
Excel is built to work with calendar years. Financial years pose a few issues for correctly identifying year and month numbering. I wanted to create some formulas that would work with all financial year ends. Here they are.
(more…)When to Avoid Using Colours in Excel
Like me, you have no doubt seen some colourful Excel files. One task that colour is used for is to mark rows so they can be reviewed. In general, please avoid applying colours manually when marking rows for review. Instead use a code in a separate column.
(more…)Another Blank Cell Issue in Excel
Excel has plenty of issues when it comes to correctly handling blank cells. Here is a solution to one of those issues.
(more…)Pie Chart Other Segment in Excel
Pie charts often get a bad rap in data visualisation circles (sorry) but here is a technique to avoid those multiple small segments.
(more…)UNIQUEIF Excel Formula
I recently had an interesting question in a webinar. Could I create a formula like UNIQUEIF. I like a challenge so here is my answer.
(more…)Sharing a Power Query Between Files
If you have a created a power query to import data you can save it and have another file access it easily.
(more…)Excel Formula for Duplicates
I had question in a webinar recently about a formula to list duplicated values. There may be a shorter formula, but I found and shared a solution.
(more…)Convert Line Chart to Dynamic Step Chart
Excel and Chart legend and MVP Jon Peltier has blogged about how you can convert a line chart into a step chart. I have used the new TRIMRANGE functionality and range names to make a dynamic solution.
(more…)Excel Data Validation Based on Another Cell
I recently had a question on an old blog post about limiting a cell’s entry to returning a whole number based on a calculated value. The requirement was entering a quantity that was a multiple of a carton quantity.
(more…)TRIMRANGE and Range Names in Excel
The new TRIMRANGE range reference structure can be applied to range names to create flexible and dynamic range names.
(more…)Excel What If Techniques [Video]
Learn about the built-in features that allow you to perform What If analysis in Excel. This is a recording of a live free webinar I ran in April 2025. Over 100 people attended. The average feedback score from more than 70 people was over 90%. You can download the materials at the link below the video. Excel What If Techniques10/10I always find your webinars and materials easy to follow and informative. Thank you for continuing to provide the suite of…
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.
(more…)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.
(more…)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.
(more…)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 Query10/10Really enjoyed this webinar - the conversion of MYOB style reports is such a useful tool to know.C StevensonJanuary 2025 Introduction to Power Query10/10Terrific session - very clear and concise and very useful information. The explanation…
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. https://vimeo.com/1044630953
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.
(more…)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.
(more…)Using a Prefix for Excel Table Names
I have been using the tbl prefix for my formatted table names in Excel for a long time. There are a few good reasons I use it.
(more…)Excel Drop Down Improvement
In previous versions of Excel when you had a drop-down list that contained duplicates those duplicates would show up in the drop-down list. This has been fixed in the latest versions of Excel. The duplicates are now removed.
(more…)Excel PivotTable Subtotal Label Trick
I just learned a new trick for labelling subtotal rows in a PivotTable. Hat tip to Ken Puls MVP of Excel Guru for this tip.
(more…)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.
(more…)More Adjusting in Excel
Following on from last week’s post on a single adjustment formula this post will share a more robust solution for including or excluding adjustments.
(more…)