I can’t even begin to count the number of times I have created a unique list in Excel. I have performed it manually using the remove duplicates from the ribbon, Pivot Tables and using complex formulas, but that is now a thing of the past. When Microsoft announced changes to Excel’s calculation engine in September Read More
Category: Blog
Dynamic arrays in Excel
Excel has changed… like seriously, changed. Every time we used Excel in the past, we accepted a simple operating rule; one formula one cell. Even with advanced formulas, it was still necessary to have a cell for each. But this has changed; Excel now allows a single formula to fill multiple cells. This is possible Read More
Loop through selected sheets with VBA
Looping through worksheets is one of the most common actions performed by those who use VBA to automate Excel. However, we don’t always want to apply a macro to every worksheet; sometimes, we only want it for each selected sheet. Depending on the action being performed, this can cause an error. This occurs where the Read More
How to add a drop-down list in Excel
We come across drop-down lists on the internet and other software applications all the time. They are useful because they limit the options available to the user, as only pre-validated items can be selected. In this post, we’ll look at different ways to add a drop-down list in Excel. In Excel, there are three different Read More
Switch chart between monthly and quarterly
In this post, I want to answer a question asked by a reader. The question was from Nick: “I have a chart on a separate chart sheet, where I’d like to add a developer object (button, option box or checkbox) to control whether data is shown quarterly or monthly. Any idea how to do that?” Read More
Power Query – Filter with wildcards
In Power Query, there is one key feature that is missing, the ability to filter using wildcards. Some have got around this with complex M code, while others suggest using the Text filter functions, such as Text.Contains, to create a similar effect. However, what I’m about to show you is an easier way to filter Read More
Resetting the scroll bar in Excel
Have you ever experienced the issue where Excel’s scroll bars become tiny? A small movement in the mouse can change the view by hundreds of rows or columns. This makes the worksheet exceptionally difficult to navigate around. The problem might reveal itself with an error message when trying to insert new rows or columns. In Read More
Power Query – Combine rows into a single cell
Last week we saw how to split a delimited cell into separate rows. This week we are going to do the opposite; combine rows into a single cell with Power Query. While having a flat table is excellent for data manipulation, but it’s not always the way a user wants to view the information. If Read More
Power Query – Split delimited cells into rows
This is the first of two posts showing how to split and combine rows from/to delimited cells. In days gone by, it would take a long time for us to resolve these scenarios. But with Power Query, it’s now a quick and easy process, which can be updated with a simple refresh. In this first Read More
Automated Goal Seek
I recently attended an online meetup where Danielle Stein Fairhurst shared a technique involving Excel’s Goal Seek feature operated by a macro. This got my mind buzzing with ideas, so I wanted to share it with you, along with a few of my own developments. I’ve not used the technique extensively to date, but it Read More
