Every financial analyst is a whiz with Excel. However, as storage becomes cheaper, our organizations are accumulating more data. And ever-more data makes it harder to work with Excel—we either reach the 1,048,576 row hard limit, or the document slows to a crawl trying to process everything.
Often, the decision is whether to lose some of the carefully curated details, or to work in a tediously slow workbook. Sometimes, we have to get creative in order to combine two large datasets. We resort to using convoluted formulas and waiting forever for the calculations to be resolved.
Fortunately, you no longer have to make these decisions. Excel’s Power Pivot functionality provides a way to extract, combine, and analyze large datasets. Despite the fact that it was released with Excel 2010, most financial analysts I meet still do not know how to use Power Pivot, and many do not know it even exists.
In this article, I will show you how to use Power Pivot to overcome common Excel issues, and take a look at additional key advantages of the software using some examples. This Power Pivot for Excel tutorial is meant to serve as a guide to what you can achieve with this tool, and at the end, I will explore some sample use cases where Power Pivot could prove invaluable.
What Is Power Pivot and Why Is It Useful?
Power Pivot is a feature of Microsoft Excel that was introduced as an add-in to Excel 2010 and 2013, and is now a native feature for Excel 2016. As Microsoft explains, Power Pivot for Excel “enables you to import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and PivotCharts, and then further analyze the data so that you can make timely business decisions without requiring IT assistance.”
The primary expression language that Microsoft uses in Power Pivot is DAX (Data Analysis Expressions), although others can be used in specific situations. Again, as Microsoft explains, “DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your model.” Fortunately for those already familiar with Excel, DAX formulas will look familiar, since many of the formulas have a similar syntax (e.g.,
For clarity, the key benefits of using Power Pivot vs. basic Excel can be summarized as the following:
- It lets you import and manipulate hundreds of millions of rows of data where Excel has a hard constraint of just over a million rows.
- It allows you to import data from multiple sources into one single source workbook without having to create multiple source sheets that suffer from version control and transferability issues.
- It lets you manipulate the imported data, analyze it, and draw conclusions without slowing down your computer to a snail’s pace.
- It lets you visualize the data with PivotCharts and Power BI.