Power Pivot For Excel

Executive Summary

What Is Power Pivot? * Introduced to Excel 2010 and 2013 as an add-on, but now native to the application, Power Pivot is part of Microsoft's business intelligence stack capable of (but not limited to) big data analytics work without specialty infrastructure or software.

* According to Microsoft, "Power Pivot 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 further analyze the data so that you can make timely business decisions without requiring IT assistance."

* Power Pivot was created in direct response to the big data demands of contemporary business intelligence needs, which prior generations of Excel—given their 1,048,576 row limit or processing speed shortcomings—struggled to cope with.

* Power Pivot is expressed by Microsoft using DAX (Data Analysis Expressions), which is a collection of functions, operators, and constants usable in a formula or expression to calculate/return one or more values.

PivotTable

What Are the Benefits to Power Pivot vs. Basic Excel? * Power Pivot lets you import and manipulate hundreds of millions of rows of data whereas Excel has a hard constraint of just over a million rows.

* Power Pivot allows you to import data from multiple sources into one single source workbook without having to create multiple source sheets and deal with potential version control and transferability issues.

* Power Pivot lets you manipulate the imported data, analyze it, and draw conclusions without slowing down your computer system, as is typical of Basic Excel.

* Power Pivot lets you visualize and manipulate your big datasets with PivotCharts and Power BI, where basic Excel lacks these capabilities.

How Can a Finance Expert or Excel Consultant Help Your Business? * By working alongside you as a thought partner to design, structure, build, and deliver a range of financial models, budgets, and big-data analyses/projects, all en route to decisions around captive projects, mergers and acquisitions, or strategic investments.

* By creating customized models unique to your business, using Power Pivot and other specialty excel functions.

* By also creating prefabricated, go-to templates that can be adapted uniquely by almost anyone across your organization, for almost any purpose, using Power Pivot and other specialty Excel functions.

* By training individuals or groups within your organization on everything from the basics of excel, modeling and analysis to advanced quantitative methods using Power Pivot, Power Pivot tables, Power Pivot charts, and PowerQuery.

* By actualizing each of these and more, alongside the design, creation, and delivery of a polished and professional PowerPoint presentation, ahead of strategic decisions.

Download the data set here to follow along with the tutorial.

The Emperor’s New Clothes: Power Pivot Tutorial

Across the various fields and sub-sub-fields that span finance, financial analysis, financial markets, and financial investing, Microsoft Excel is king. With the arrival and exponential growth of big data, however, driven by decades of data aggregation and accumulation, the advent of cheap cloud storage and the rise of the internet of things—i.e., eCommerce, social media, and the interconnectedness of devices—Excel’s legacy functionality and capabilities have been pushed to their limits.

More specifically, older-generation Excel’s infrastructure and processing limitations such as its row-limit of 1,048,576 rows, or inevitable processing slow-down where large data sets, data tables and interconnected spreadsheets are concerned, reduced its usability as a effective big data tool. In 2010 however, Microsoft added a new dimension to Excel, called Power Pivot. Power Pivot offered next-generation business intelligence and business analytics functionality to Excel in its ability to extract, combine, and analyze almost limitless datasets without processing speed impairment. Despite its release eight years ago, however, most financial analysts 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 tutorial is meant to serve as a guide for what you can achieve with this tool, and at the end, it will explore some sample use-cases where Power Pivot often proves 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 and 365. 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., SUM, AVERAGE, TRUNC).

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.

In the following sections, I’ll run through each of the above and show you how Power Pivot for Excel can be helpful.