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
Category: Blog
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
Interpolate with Excel
Interpolation is the process of estimating data points within an existing data set. As this is an Excel blog, then clearly the question we want to answer is: can we interpolate with Excel. This is a common question. In fact, it was the following question from a reader which first made me look into this Read More
Save Excel as PDF with VBA
Since Excel 2010, it has been possible to save Excel files as PDF documents. PDF was then and continues to be, a common file format for distributing documents. The code examples below provide the VBA options to automate the creation of PDFs from Excel. They can be used on their own or as part of Read More
Dynamic arrays and VBA user defined functions (UDFs)
Office 365 subscribers now have Excel’s new calculation engine available to them, meaning lots of users suddenly have access to dynamic arrays. This got me thinking about whether VBA user-defined functions (UDFs) can make use of the new functionality too. I’m not an expert in this; I’m just sharing as much as I’ve learned to Read More
