MrExcel Consulting
Your One Stop for Excel Tips & Solutions

PowerPivot Alchemy: Patterns and Techniques for Excel

June 2014

PowerPivot Alchemy: Patterns and Techniques for Excel
PowerPivot Alchemy is far less 'techie' than other PowerPivot books...all you have to do is follow the pattern, and you will have something awesome to show for your time and attention.
Bill Jelen,

Order eBook Version »

PowerPivot is an amazing new add-in for Excel 2013 that allows business intelligence pros to process large amounts data. Although it is simple to use in a demo environment, analysts are likely to run into real-life scenarios that are difficult to solve. This guide, dedicated solely to the PowerPivot tool, provides techniques and solutions to real-world problems, including showing the Top N customers by using slicer filters, comparing budget to actuals, drilling across data instead of through data, and joining data from two different sources in a single analysis.

I first saw Power Pivot in 2009. It was amazing. I quickly learned enough to do a demo to wow people from Peoria to Peru. But a 10-minute demo requires far less skill than actually using the product every day. As I wrote PowerPivot for the Data Analyst, I found myself always going back to Rob Collie, who was still a PM on the Power Pivot team, to try to figure out various quirks of Power Pivot.


The book you’re looking at right now is far less techie than some of the PowerPivot books that have hit the data stream. For instance, Rob doesn’t force you to understand how the EARLIER() function works in DAX in order to do something cool with Power Pivot. Yes, you will see the EARLIER() function in this book, but all you have to do is follow the pattern, and you will have something awesome to show for your time and attention."

Bill Jelen,

More from the book:

Broadly speaking, Power Pivot is a numbers-producing machine: Raw data goes in, your formulas and relationships digest it, and magically useful numbers come out. Those numbers are often metrics on a business, and that’s a beautiful thing; very often, those metrics are being “seen” for the first time in the history of the business. Quite often, before a business adopts Power Pivot, it is forced to operate without metrics that are, in hindsight, quite clearly critical. This is a lot like a doctor suddenly having access to patient information such as temperature, pulse rate, and blood pressure—after lacking that information for most of a career. Such a shift is transformational.

So, Power Pivot produces numbers—incredibly important numbers that quite often have never before existed. It’s empowering stuff for sure.

There is a natural tendency among “numbers” people to view the freshly pressed numbers as the final destination. Resist that temptation! In order for numbers to have an impact,..."

PowerPivot Alchemy: Patterns and Techniques for Excel, by Bill Jelen and Rob Collie, Page 1
PowerPivot Alchemy
PowerPivot Alchemy

Table of Contents

  • Dedications v
  • Acknowledgements vii
  • Foreword ix
  • Introduction xi
  • Chapter 1: Dashboard Tricks and Visualization Techniques 1

    • Adding a “Last Refreshed Date” Readout 1
    • Normalizing Your Measures to First/Average/Max Values for Charts 4
    • Bubbling Up Exceptions with “Sarah Problem” 10
    • Ranks and Exceptions That Bubble Up to Subtotals 12
  • Chapter 2: Slicers: The Gateway to Interactivity 23

    • “Initializing” Slicers That Contain Too Many Values to Scroll 26
    • Wingdings and Other Symbolic Fonts in Slicers 28
    • Using Macros to Change Slicer Fonts 40
    • A Simple Trick for Combatting “Stale” Slicers 42
    • User-Friendly Report Sorting with Slicers 46
    • Adding State Alpha Sorting to the Sort-by-Slicer Trick 50
    • Conditional Formatting Controlled via Slicers 60
    • Catching Slicer Selections in a Formula 69
  • Chapter 3: Conquering Common Calculated Column Conundrums 75

    • An Excel Pro’s Primer on Calculated Columns 75
    • Subtotaling Calc Columns and the EARLIER() Function 76
    • Referencing the Previous Row and Similar Calcs 79
    • Referencing Rows “Within Range” of the Current Row 80
    • Totaling Data Table Values in Lookup Tables 90
    • CONTAINSX: Finding Matching Values in Two Tables 92
    • CONTAINSX Revisited: What Is the Match? 94
    • Common Calendar Conundrums 97
    • Custom Calendar Conundrums 100
  • Chapter 4: Modeling and Portable Formulas 103

    • Integrating Data of Different “Grains” 109
    • A Mystifying and Awesome Solution for the Many-to-Many Problem 117
    • Calculating Same-Store Sales in Power Pivot 120
    • Same-Store Sales Continued: Using Store Open/Close Dates 123
    • Campaign Analysis: Seasonally Adjusted Measures 128
    • A/B Campaign Analysis with Start and End Date Slicers 137
    • Customers and Website Visitors: Percentage Who Return 145
    • Calculating Average Customer Age 151
    • Moving Averages, Sums, etc 157
    • Moving Averages Controlled by a Slicer 160
  • Chapter 5: Power Query 171

    • Combining Multiple Worksheets or Workbooks into a Single Power Pivot Table 171
    • Using Power Query to Merge CSV Files 176
    • Using Power Query to “Unpivot” a Table 186
    • Using Power Query to Create a Lookup Table from a Data Table 190
    • Creating a Calendar Table: Advanced Usage of Power Query 193
  • Chapter 6: Power View 199
  • How to Use Power View 199
  • Chapter 7: Power Map 221

About The Authors

Bill Jelen, Excel MVP and the host of, has been using spreadsheets since 1985, and he launched the website in 1998. Bill was a regular guest on "Call for Help with Leo Laporte" and has produced more than 1,200 episodes of his daily video podcast, "Learn Excel from MrExcel." He is the author of 30 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance magazine.

You will most frequently find Bill taking his show on the road, doing half-day Power Excel seminars wherever he can find a room full of accountants or Excellers. Before founding, Jelen spent 12 years in the trenches working as a financial analyst for finance, marketing, accounting, and operations departments of a $500 million public company. He lives near Akron, Ohio with his wife, Mary Ellen, and his sons, Josh and Zeke. You can follow Bill on Twitter as @MrExcel where he shares his Excel knowledge, discoveries and personal adventures in the world.

Rob Collie is Chief Technology officer at Pivotstream LLC, where he and the engineering team provide Excel data pros with a platform to transform their careers. An Excel pro himself, Rob and others at Pivotstream also use PowerPivot to provide actionable business information to dozens of Fortune 500 corporations.

He previously spent 13 years at Microsoft, leading software engineers on projects including PowerPivot v1, multiple versions of Excel, and Windows Installer (MSI) v1. He is the world’s most prolific PowerPivot blogger, running and since late 2009.

As a Microsoft MVP in the community, Rob is also the world’s most prolific PowerPivot blogger, running the and websites since late 2009.

Rob now lives in Cleveland Heights OH with his wife Jocelyn, his kids RJ and Gabby/Ella, and a pack of wild dogs. He is never far from a PowerPivot-equipped computer, his treadmill desk, a fast internet connection, or a pot of coffee. You can follow Rob on Twitter as @PowerPivotPro, where his mixture of alternately serious and irreverent observations often continues well into the small hours of the night

Order eBook Version »