Denice R. Preston
Excel’s data-mining capabilities have been stretched to the limit by the emergence and proliferation of big data. This is reflected in the fact that the software has a 1,048,576-row hard limit, and its processing speed has slowed to a crawl. Microsoft’s Power Pivot feature was released in 2010. It has transformed Excel when it comes to the extraction, combination, and analysis of large data sets.
Excel Power Pivot: The Emperor’s New Clothes tutorial
Excel has become the standard in many fields, including finance, financial analysis and markets, and investment. Excel’s capabilities and functionality have been stretched to the limit with the exponential growth and arrival of Big Data. This is due to decades of data accumulation and accumulation as well as the advent of inexpensive cloud storage.
Excel of older generations has limitations in terms of infrastructure and processing, such as the row limit of 1,048,576 columns or its inevitable slowdown when dealing with large data sets, tables, and interconnected worksheets. This reduced its effectiveness as a big data tool. Microsoft introduced a new feature to Excel in 2010 called Power Pivot. Power Pivot brought next-generation business analytics and business intelligence functionality to Excel. It allowed it to extract, combine, and analyze virtually unlimited datasets without affecting the processing speed. Even though it was released eight years ago, most financial analysts do not understand how to use Excel Power Pivot, and many are unaware that it exists.
This article will demonstrate how you can use Power Pivot to solve common Excel problems and look at some of the key benefits that this software offers. This Power Pivot Tutorial is intended to be a guide on what you can do with this tool. At the end of the tutorial, we will look at some examples where Power Pivot has proven invaluable.
What is Power Pivot? Why is it useful?
Power Pivot was originally an Excel add-in for Excel 2010 and 2013. It is now available as a native feature in Excel 2016 and 365. Microsoft explains that Power Pivot allows you to import millions of rows of data into one Excel workbook. You can also create relationships among heterogeneous datasets, calculate columns and measures with formulas, build PivotTables or PivotCharts, and analyze the data further without IT assistance.
Microsoft’s Power Pivot uses DAX as its primary expression language, although other languages can be used for specific situations. Microsoft explains that DAX is a set of functions, operators, and constants that can be used to create formulas or expressions in order to calculate and return values. DAX is a tool that allows you to create new data from the existing information in your model.
To summarize, Power Pivot’s key advantages over basic Excel are:
Excel is limited to a maximum of just under a million rows.
This allows you to import multiple sources of data into a single workbook without creating multiple sheets with version control or transferability problems.
You can manipulate, analyze, and draw conclusions from the data without having to slow down your computer.
You can visualize data using PivotCharts in Power BI.
Power Pivot Excel is a powerful tool that can help you with all of these tasks.
Importing large datasets
Excel’s major limitation is its inability to handle large datasets. Excel now allows us to load data with more than one million rows directly into Power Pivot.
For this demonstration, I created a sample dataset with two years of sales data for a sporting goods retailer. The dataset had nine product categories and was divided into four regions. The dataset contains two million rows.
Using the Data tab on the ribbon, I created a New Query using the CSV (see Creating a new query below). The functionality was previously called PowerQuery. However, Excel 2016 or 365 is more tightly integrated with the Data tab.
It took less than a minute to go from a blank Excel workbook to loading two million rows in Power Pivot. You can see that I was able to perform some data formatting using the first row as the column names. Power Query has evolved from an Excel add-in into a fully integrated feature of the Data tab in the toolbar over the last few years. Power Query allows you to pivot, flatten, and cleanse your data using its set of options.
Importing data from multiple sources
Power Pivot’s ability to import data easily from multiple sources is another key benefit. Many of us used to create various worksheets to accommodate our different data sources. This process often involved writing VBA codes and copying/pasting data from disparate sources. Power Pivot, however, allows us to import data directly from Excel without the problems mentioned above.
To integrate data, you can combine multiple data sources, either using the Query function or the Power Pivot Window. You can, for example, pull actual sales data from an SQL server and production-cost information from an Excel worksheet using the Query into Power Pivot. You can then combine the two datasets, matching the production batch numbers to calculate per-unit gross profit margins.
Working with large datasets
Power Pivot Excel’s ability to work and manipulate large datasets in order to make relevant analyses and conclusions is another key benefit. Below, I’ll go through some common examples to help you get a feel for the tool.
MEASURES
Excel junkies can’t help but agree that pivot tables are one of the best and most useful tools in Excel, yet also one of the hardest to use. It’s frustrating, especially when you have to work with large data sets. Power Pivot allows us to create PivotTables quickly and easily, even when we are working with large data sets.
Notice how the Power Pivot Window is divided into two panes in the image below, titled Create Measures. The data is displayed in the top pane, while the measures are shown in the bottom pane. An effort is the result of a calculation performed on all data. I entered a measurement by typing it into the highlighted cell.
This will create a new measure that sums the values in the Amount column. In the same way, I can enter another step into the cell below.
Watch how quickly you can create a familiar Pivot Table using a large dataset.
DIMENSION TABLES
We have become experts at manipulating Excel formulas. We are experts at VLOOKUP and SUMIF. Even the dreaded Index(MATCH() ) is no problem. By using Power Pivot, we can toss out a lot of this.
By choosing “Add to Data Model,” this table is loaded into Power Pivot (see em>Adding a User-created Table to a Power Pivot Model/em> above). This table is then loaded into Power Pivot by selecting “Add to Data Model” (see Adding a User Created Table to a Power Pivot Model).
I created a date table to go with our dataset. (See Creating A Date Table). Power Pivot makes it simple to quickly create a table of dates to consolidate data by quarters, months, and days. The user can also create a more custom date table to analyze by weeks, fiscal years, or any organization-specific groupings.
Calculated Columns
Calculated columns are another option for calculations. These formulas are familiar to Excel users since they are similar to procedures used in data tables. Below, I’ve created a calculated column that sorts the Accounting Data Table by Amount (see Create a Calculated Cell). The formula is intuitive: Sales under $50 are labeled as “Small,” and those above $50 are marked as “Large.”
