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
Category: Blog
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
Change the Power Query source based on a cell value
If you’re like me, you build Power Query solutions in a test environment first; then, when ready, it’s released into the wild. This means the paths of all source files must be updated. Or maybe you’re a consultant who creates solutions for others; when you distribute the workbook to your client, you have to provide Read More
100 Excel VBA Macros
With macros, we can automate Excel and save time; big tasks or small tasks, it doesn’t matter. All that matters is that we’ve become more efficient. In this post, I share 100 VBA macro codes that you can use today. If you’ve never used VBA before, that’s fine. Part 1 contains instructions of how to Read More
