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…)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…)Two Column Lookup in Excel – Part 1
It is common to perform a lookup based on one column. Performing a lookup based on two columns is more unusual. This is a formula technique you can use to perform a two-column look up.
(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…)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…)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…)Checkboxes as a Visualisation in Excel
I have covered the new checkboxes in Excel in a previous post. Typically, they are used as a simple user interface to turn off or turn on an option. But I learned recently that you can also use them as a visualisation.
(more…)Resetting the Used Range
The scroll bar on the right of screen allows you to quickly scroll down to the bottom of your data. Sometimes the scroll bar thinks the data goes down to the maximum row number. It keeps track of what’s called the Used Range. The Used Range goes down to the intersection of the last used row and across to the last used column. To see where the Used Range ends press Ctrl + End. The cell selected may be empty.…
Using Colours Effectively in Excel Charts
When I reviewed the charts in the CPA Australia 2024 Annual Report I found another series of charts that could be improved.
(more…)Column vs Bar Chart in Excel
As I was reading the CPA Australia 2024 Annual Report I was interested in the presentation of charts (graphs). A lot of money had been spent on the presentation of the report. There were lots of high-quality photos and other images. I was disappointed with much of the data visualisation.
(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…)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 Referencing in Excel
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.
(more…)Comparing Lists in Excel
I have been going through Mike Girvin’s recent book Microsoft 365 Excel: The Only App That Matters, and found an elegant technique of comparing lists using FILTER and XMATCH. It inspired me to create a custom function to show missing or matching entries.
(more…)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.
(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
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…)Solving a Date – Time Issue in Excel
Often when you import data into Excel the dates may include a time. This can make summarising the data more difficult as time is included as a fraction of a date. You can use an old function to fix the problem. This solution requires the subscription version of Excel.
(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…)Padding Entries in Excel
Another post inspired by the book 101 Ready-to-Use Excel Formulas by Michael Alexander and Dick Kusleika. This one is Formula #22 and covers padding entries with zeroes.
(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…)FILTER Function Technique
An application I use recently updated it’s filtering options to allow you to filter by any filters or all filters. This was a useful addition to the software and I thought that I could apply the same idea to Excel’s FILTER function.
(more…)Benford’s Law in Excel – Part Two
Benford’s law is used in auditing to identify data sets that may have been manipulated or adjusted. In my previous post I created a report to analyse a data set based on Benford’s Law. In this post we will create a single formula to create the report and then convert that into a custom function.
(more…)