In a previous post in this series, we briefly looked at the if statement in Power Query; now, we’re going to dig a bit deeper and understand how the Power Query if statement works. In Excel, IF is a core function; it’s one of the first functions we learn and remains popular for advanced users. Read More
Category: Blog
VBA code to copy, move, delete and manage files
Automating processes with VBA can involve copying, moving, deleting and managing lots of files. Thankfully, VBA has many built-in functions to undertake these tasks for us. These files do not necessarily need to be Excel workbooks; they can be any file type. Download the example file I recommend you download the example file for this Read More
How to sum across multiple sheets in Excel – simple method
Have you ever had to sum the same cell across multiple sheets? This often occurs where information is held in numerous sheets in a consistent format. For example, it could be a monthly report with a tab for each month (see screenshot below as an example). Watch the video Watch the video on YouTube. I Read More
Power Query formulas (how to use them and pitfalls to avoid)
Formulas are the lifeblood of Excel; they are essential to achieve even basic tasks. On the other hand, Power Query has been designed so that most transformations are accessed through the intuitive user interface. But Power Query formulas exist; it has a formula language with over 700 functions. Each time we make a transformation with the Read More
How to show hidden data in Excel chart
Imagine you’ve created a beautiful chart for your Excel report. You decide to hide the source data because the report’s users don’t need to see that. Suddenly the information in the chart disappears. So, this raises the question: how to show hidden data in an Excel chart? We answer this question in this post. But, Read More
How to Insert, Move & Delete Pictures with VBA
While it is not often that we have to control pictures or images within Excel, based on the number of Google searches for this topic, it’s clearly something people want to know more about. This post serves as a reference to cover most of the scenarios we’re likely to encounter to copy, insert, move, delete Read More
How to forecast seasonality in Excel (the easy way)
In this post, we look at how to forecast seasonality in Excel. I have previously written about forecasting seasonality using the FORECAST function. However, with Excel 2016, Microsoft gave us an easier method for forecasting seasonality. Rather than using the FORECAST function with a lot of trickery, we can simply use the FORECAST.ETS function by itself. Read More
How to change source data location in Power Query (7 ways)
Maybe you’ve built some queries in a test environment, and now it’s time to start using them for real. The obvious question arises: How to change source data location in Power Query? Or, maybe you’ve linked to a workbook/CSV file that updates every month, so you need to update the data source. Or, perhaps you’re Read More
VBA code to loop through files in a folder (and sub folders)
Listing filenames in a worksheet, printing all the files to PDF, or making changes to every file in a folder, there are many reasons why we may want to loop through files in a folder. VBA provides us with a few ways to achieve it (1) Dir function (2) File System Object. Let’s briefly look Read More
Power Query: Lookup value in another table with merge
Looking up data from a table or worksheet is probably the most common activity undertaken by Excel users to create reports. Learning to use VLOOKUP for many is their first taste of Excel’s power. But what about when using Power Query? There isn’t a VLOOKUP function, so how can we lookup a value from another table with Power Read More