How Power Query and Power Pivot Can Make Excel More Fun and Speedy

We’ve always wrestled with how to show someone why we think Power Query and Power Pivot can change their (working) life. In addition to creating big picture concept videos on how Power Query and Power Pivot can automate data preparation steps, update reports in two steps aka add-new-raw-data-file-to-folder-and-refresh, link tables without VLOOKUP, and answer questions on-the-fly, we figured we had to show exactly how Power Query and Power Pivot work and how they significantly improve what we can do in “normal” Excel.

After all, as number-crunching folks, we know the devil is in the details, and we won’t believe it till we see it. Even better, is if we can get our hands on the file and tear it apart to see how it works.

So, this template and the accompanying five-part tutorial is for you to do just that.

What’s so special about it?

Dynamically Calculate Year-on-Year, Last 12 Months, Year-To-Date, and Financial-Year-To-Date figures

This template is set up to dynamically calculate and compare figures across different time periods and categories. We cover common measures such as Year-on-Year (“YOY”), Last 12 Months (“L12M”), Year-To-Date (“YTD”), and Financial-Year-To-Date figures (“FYTD”). We use a sample set of data that shows sales quantity by product category.

Using just “normal” Excel, we first show you how to set it up so that when the new month or year data comes in, you don’t have to shift your referenced data ranges or structurally add in new columns to accommodate the updated data. This cuts down your preparation time for each data update.

What’s cool about this template is that it also allows you to quickly and easily update the calculations based on your choice of reference point in time. For example, you could set the latest date to “201808” and get YTD figures calculated as of August 2018. In the very next instance, you could change the reference date to “201711” can get YTD figures re-calculated as of November 2017. This gives you the flexibility to go back and forth in time to see how the figures have changed instead of switching between multiple versions of the same file saved at different times.

Then, we show you how using Power Query and Power Pivot, we can achieve the same outcomes, but faster, more accurately, and flexibly.

We recommend using the template in three ways:

1. Just Plug and Play

Replace the sample data with your own and follow the instructions in the template to dynamically calculate YOY, L12M, YTD, and FYTD figures using normal Excel formulas and / or Power Query and Power Pivot.

2. Learn How to Set Up Dynamic Calculations Using “Normal” Excel

In the first three parts, we’ll show you how to work smart using just good old Excel.Get the step-by-step low-down on how to set up a structure once using “normal” Excel so you don’t have to fiddle with the formula and references later whenever new data comes in:

[1/5] How to set up a reference date table and summarise figures to get absolute and year-on-year (YOY) monthly figures

[2/5] How to calculate Last 12 Months (L12M) and Last 12 Months Average (L12M_Avg) figures

[3/5] How to calculate Year-To-Date (YTD) and Financial-Year-To-Date (FYTD) figures

3. Learn How to Set Up Dynamic Calculations Using Power Query and Power Pivot

In Part 4 and 5, we walk you through how you can use Power Query and Power Pivot to set up and calculate all the above measures a lot quicker than the “normal” Excel functions. These tools take the ideal concept of “set up once, use forever more” to a whole new level.

[4/5] The Power Series Way, Part 1: Power Query

[5/5] The Power Series Way, Part 2: Power Pivot

Ready to get into the nitty-gritty details and find out for yourself? Download the template, install the free Excel Power Query and Power Pivot add-ins, and you’re ready to go with Part 1 of this five-part tutorial!