Recently, one of our academy members asked how to get data from an Excel workbook when sheet or Table names change. By default, Power Query uses names to filter down to the correct data. Therefore, if the sheet or Table names change every time a report downloads, that will cause errors. We don’t want to … Read more
I was recently asked to add a QR code to a document. While I had seen them before, I had never needed to create one from scratch. But, it turns out it’s pretty simple and free in Excel. So, in this post, we look at 3 easy ways to create QR codes in Excel. Table … Read more
Office Scripts have workbook scope. Which means they can’t move data from one workbook to another. In fact, they don’t even know another workbook exists. However, they can pass data to Power Automate, which has the ability to pass data to other workbooks. So, in this post, we look at how to move data between … Read more
Filtering is a common task in Power Query. Usually, we filter by a single value, or maybe a small number of known values. These filter values are hardcoded into the underlying M code. Therefore, to change the filter, we have to edit the query. But what if we don’t know which items we want to … Read more
Normally, we build user interfaces so that one button runs one macro. But what if we want the user to select which macro to run? How can we handle that scenario? Well, that’s what we are looking at in this post, how to run any macro from one button. Yes, you heard me. One button … Read more
SharePoint is becoming a common data source for many organizations. With the increase of users on Microsoft 365 licenses, I think we can assume this will continue to grow. Teams, OneDrive, and Lists are all built on the SharePoint platform; therefore, it is easy to interact with SharePoint without knowing it. Given this increase, I … Read more
For a finance professional in industry, nothing is more annoying than writing the period-end or forecast commentary. (OK, maybe the words “last-minute change to provisions” is more annoying, but the commentary is right up there). You’ve created the reports, found the reasons for the variances, and now you need to write it as a text … Read more
In this post, we look at how to browse for file path with VBA, and then insert the selected filepath into a cell. This post is inspired by a question I received: “If you would want to add a browse button right beside the cell that contains the file path so that you won’t need … Read more
VBA in Excel does not restrict us to just a single application. VBA gives us access to the windows environment too. With this we can perform a lot of common file-based actions. One of the most common is to rename a file. In this post, we will look at 5 examples of renaming files with … Read more
In a previous post, we looked at various methods to remove additional spaces in Excel. One of the methods involved creating a custom function in Power Query. As this is a more advanced method, I wanted to go a bit deeper and describe the method to remove spaces in Power Query. Table of Contents Excel … Read more