Analyzing large datasets using Power Pivot in Microsoft Excel
The situation in Power Pivot… If you are an Excel user often, you will be familiar with PivotTables. It is used to get quick insights from small amounts of data and can even be converted to graphs that are easy to understand. But even Excel has its limitations. Excel struggles when joining tables, manipulating large data sets from a million rows, or selecting data from multiple sources. It can be frustrating to have Excel quit unexpectedly, run very slowly, or time out and require a forced shutdown! ? Use a power pivot.
In 2010, Microsoft added Power Pivots to Excel to help analyze large amounts of data. Power Pivot can handle hundreds of millions of rows of data, making it a better alternative than Microsoft Access. Think of Power Pivot as a way to use PivotTables on very large datasets.
Also useful when data comes from multiple sources. Power Pivot can be confusing and frustrating as you can import that data into one workbook without having multiple source sheets.
Power Pivot was created to import and analyze data from many sources. In Power Pivot, you can use everything from Microsoft SQL, Oracle, or Access databases to SharePoint list data and text documents.
Accessing Power Pivot
Power Pivot is a free add-in tool within Excel and a permanent built-in feature of Excel 2016 and 365. The first step in using Power Pivot is adding the Excel Ribbon. The latest version of Microsoft Excel (13 & # 39; – 17 & # 39;) has Power Pivot built in, but you may need to activate it.
Activate Power Pivot by clicking File-> Options-> Add-ins-> Microsoft Power Pivot for Excel. Not ready to use There is one more step yet.
You must tell Power Pivot where to import your data. To do this, click the Power Pivot tab under Ribbon-> Data Management-> Import External Data. There are many options in the list of data sources. This example uses data from another Excel file, so select the Microsoft Excel option at the bottom of the list. For large amounts of data, importing takes some time.
After the import finishes, the data is displayed in the Power Pivot main window. Two windows open at the same time: the normal Excel window and the Power Pivot window. But you don’t have to have any data in the opened Excel page.
Creating a Basic Power Pivot Table
Suppose you have a spreadsheet with a list of product sales. Each row is a customer ID and the columns are name, invoice number, date, quantity, and price.
The customer has more than one invoice but only one customer ID, so the customer information must be repeated many times. If there are few invoices, this is not a problem. However, if invoices start adding up to a million, it is less efficient to use this format in native Excel, and more efficient to create Power Pivot.
Open a new Excel workbook to do this. Select Power Pivot on the ribbon, then click Manage-> From Other Source and scroll down to Excel. Use the browse button on the screen to select a file.
Once the file is selected, click Next. It’s a good idea to rename the & # 39; Friends name & # 39; header to a title that describes your data set. In this case, the title has been changed to “Invoice.” Click Done.
Success! The list of invoices has been imported into the Power Pivot Table. Here you can create a PivotTable chart as if you were using a smaller data set (described in the next section). Again, the reason for using Power Pivot is because the data is in a different format (SQL, Access, Oracle) or the Excel file has more than a million rows. Otherwise, if you use the default pivot table function in Excel, it will work without error.
Creating a PivotChart from a Power PivotTable
To create a chart from this Power Pivot, click the PivotTable icon on the Excel ribbon. Select PivotChart (Before creating a chart, select PivotTable to create a regular PivotTable in Excel).
A new workbook opens. Use the Fields toolbar on the right to select fields in the table. In this example, orders from each company are compared monthly. Therefore, the customer name, date, and quantity were selected and included in the pivot chart.
Help: Power Pivot Formulas
Power Pivot has a lot of cool features and benefits. In addition to the usual Excel functions, more than 75 new formulas have been introduced. Here are two good things to know.
= COUNTROWS: Counts the number of rows in the data source. If you have multiple data sources that are related to each other with the same ID as the product name, you can also count the number of rows associated with that identifier.
= SWITCH: The switch is very useful for data that needs editing. For example, instead of the actual name of the month (e.g. January 1, February 2, March 3, etc.), you might have a row with a number representing a specific month. = Real month with all numbers using SWITCH.
Power Pivot automatically uses the = SUM calculation to summarize numeric data. This is a great feature. To change the type of calculation used, right click inside the PivotTable and select Value Summary Based on Value Field Settings-> Tab. As you can see in the image there are = COUNT, = AVERAGE, = MIN, = MAX and many other options.
Tip: Power Pivot and SharePoint
Many organizations use Microsoft Sharepoint. Post Power Pivot dashboards, graphs, and pivot graphs directly to Sharepoint so anyone in your organization can see them. To use it, install the “Power Pivot for SharePoint” plug-in on your corporate Sharepoint site.
Power Pivot may look like an advanced Excel function, but it’s easy to use once you understand how to access features and import datasets. Once the data is entered, you can run a PivotTable or PivotChart on a regular data table in the same way as a dataset. Power Pivot is one of the fastest ways to give you insight into the minimum number of drives or large amounts of data that can crash Excel. So if you have millions of rows in your spreadsheet, find and use Power Pivot.
If you haven’t mastered your PivotTable yet, we recommend that you start your PivotTable before moving to Power Pivot. Get an introduction to the PivotTable here, and watch the webinar recorded for an hour on the PivotTable.
For a detailed tutorial on Power Pivot, check out the following: