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

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 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

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