In VBA, there are several ways to run a macro from a macro, and even run a macro from another workbook. On forums, these methods are used interchangeably depending on the commenter’s personal preferences. This can lead to confusion for anybody trying to learn VBA. Depending on the method for running/calling the macro, the syntax is slightly … Read more
In Power Query, when we connect to a local file, it’s reasonably obvious how we do that; it’s built directly into the UI. We just need to navigate to the location… Bam. Done! However, what if we want Power Query to get data from OneDrive or SharePoint? How do we do that? It’s not obvious. … Read more
This post looks at how to Resize an Excel sheet. It’s a common requirement for users and is especially useful when creating dashboards for different-size monitors. Every office I have ever worked in has been an equipment battleground. It didn’t matter what the equipment was; it just had to be better than everybody else. A new keyboard, … Read more
Sankey diagrams are used to show flow between two or more categories, where the width of each individual element is proportional to the flow rate. These chart types are available in Power BI, but are not natively available in Excel. However, today I want to show you that it is possible to create a Sankey diagram … Read more
Imagine a scenario where you have two or more lists in Excel and from that, you want to list all possible combinations in one table. This is known as a cartesian join. The question might come in a simple form, such as: “How can we get a complete list of all General Ledger and Cost … Read more
You’re probably already aware that Excel has two main calculation modes: automatic and manual. I’m sure everybody would prefer to use automatic calculation; however, formula complexity and large data sets can cause spreadsheet calculation to be slow. As a result, some users set Excel to manual calculation mode. The differences between these two calculation modes can … Read more
VBA UserForms are a key tool for managing user interactions. When UserForms are well designed, they guide users through the options and settings without any help file or guidance. However, from my own UserForm development, I know one of the most overlooked aspects is how to close VBA UserForms. Is it best to hide or unload the … Read more
In 2020, I attended an online meetup where Danielle Stein Fairhurst shared a technique to automate Goal Seek in Excel with a VBA macro. I had never thought of this before; it had my mind buzzing with ideas of how I could use it. I have since implemented this technique on a few projects and … Read more
In this post, we look at how to calculate the percentage of a total in Power Query. But we will also take this one step further, to consider how to calculate the percent of a category. In standard Excel, these calculations are simple because we are so used to the formulas to achieve this. For many, Power … Read more
From stock parts to holiday villas, from employees to logos, there are plenty of reasons to insert and automatically change an image based on a cell. You may think of this as a lookup function that returns a picture, rather than a value. Ultimately, the purpose is to link an image or picture to a cell; … Read more