Pivot table is useful when we want to analyze or slice data to gain important insights. It is the main way to analyze data. But it also has limitations. For example, you can only handle as many as 1.048 million rows of data. Luckily, Excel offers another kind of tool – PowerPivot which can o allow you to gather, store, model, and analyze a huge amount of data in Excel.
What is PowerPivot?
PowerPivot is still Pivot table and it only extends things that you already know about Excel. It enables you to store and query a large amount of data efficiently. Moreover, it can incorporate data from multiple data sources such as Microsoft Access database, text file, or data imported from a web page. You don’t need to import data as Excel worksheets before analyzing data. With Power Pivot, you can define complex expressions using DAX (a powerful programming language) to create calculated columns. There are also other advantages and I will not list any more here.
How to enable PowerPivot in Excel?
Download PowerPivot for Excel 2010
PowerPivot is available to you if you have Excel 2013. But if you only have Excel 2010, you need to go to Microsoft Download center and download PowerPivot add-in for Excel 2010. After clicking on Download button, the web page will prompt you three choices as shown in the bottom panel of Figure 1.1. 1033\x64\PowerPivot_for_Excel_amd64.msi is for 64-bit operating system while 1033\x86\PowerPivot_for_Excel_x86.msi is for 32-bit operating system. After downloading, install the add-in following instructions.
Enable PowerPivot in Excel
To enable the PowerPivot add-in in Excel 2013, select File from the tab and then select Options -> Add-ins. At the bottom of the Excel Options dialog box, select COM Add-ins and then click on GO button. In the prompted COM Add-ins dialog box, select Microsoft Office PowerPivot for Excel 2013 and then click on OK. You can use a similar approach to enable PowerPivot for Excel 2010.
Get/Refresh External data using the PowerPivot add-in
I’ve already told you that PowerPivot can enable you to incorporate data from multiple sources. This section will focus on how to import external data using PowerPivot. After you enable PowerPivot, PowerPivot tab will appear on the ribbon as shown in the upper panel of Figure 2.1. And you will see prompted Home tab as shown in the bottom panel of Figure 2.1 if you choose Manage. By clicking on Get External Data and corresponding drop-down list options, you can import data from multiple data sources.
|From Database||Loads data from an Access or SQL Server database (for which data is relational or dimensional)|
|From Data Service||Import data from a website that has an OData data feed or Microsoft Azure Marketplace|
|Other Sources||Load data from all other supported data sources such as Excel files, text files, databases like Oracle and Teradata|
|Existing Connections||From a list of previously used external sources|
If the external data is from a database, you can refresh the data in your workbook by clicking on Data -> Connections -> Refresh All. Please note that only rows and existing columns can be refreshed. Even the newly added rows can be included. But if there are new columns in the source database, you need to import them using the approach discussed above again.
Example: How to download data from Text file
Now let’s use Box office.txt to illustrate how to download data from multiple sources using PowerPivot. This text file contains data for the top 100 movies at the worldwide box office. A subset of data is shown in Figure 3.1. For each movie, we have the movie title, domestic, international, or worldwide box offices. And we even know in which year the movie was released.
Follow the instructions in Figure 2.1 to click on PowerPivot -> Manage -> Get External Data -> From Other Sources to open Table Import Wizard dialog box. Move scroll to the bottom and select Text File before clicking on Next. Fill the Table Import Wizard dialog box as shown in the middle panel of Figure 3.2. Since the first row contains column headers, we have to select Use First Row As Column Headers. In the Column Separator field, select vertical bar as the data in the text file is delimited by a vertical bar.After you click Finish and Close to complete the process of importing data from text file into PowerPivot, you will get something similar to that in Figure 3.3. You can see that the data has been downloaded into PiwerPivot. Please note that PowerPivot for Excel and the original workbook (PowerPivot.xlsx) are two different things. You can use the two menus in the red box or blue box to switch between .xlsx file and PowerPivot. For example, if you click on Switch To workbook Excel icon (in the red box) in the upper-left corner of the PowerPivot ribbon, you can return to the original workbook (PowerPivot.xlsx).
Use PowerPivot to create a Pivot Table
By clicking on PivotTable in Home tab of PowerPivot window, Excel will direct you to the workbook and allow you to choose fields where you want to place your Pivot Table. For our problem, we choose A1: I100 of the existing worksheet to place our Pivot Table.After clicking on the OK button in Create PivotTable dialog box, PivotTable Fields dialog box will be prompted as shown in the left panel of Figure 4.2. You can see that there is a little triangle symbol before Box office field and this field will expand if you click on that symbol.
In the expanded field, drag PivotTable headings into boxes as shown in the right panel of Figure 4.2. Here are the explanations for the 4 boxes.
- Row Labels Fields dragged here are listed on the left side of the Pivot table. As we drag Year field was dragged here, the data will be summarized by Year. Suppose that there is another field – country – which will be dragged here after Year, the data will be summarized first by year and then by country within each year.
- Column Labels Fields dragged here have their values listed across the top row of the PivotTable. For our example, we have no fields in the Column Labels area.
- Values Fields dragged here will be summarized in the table. Domestic, international, and worldwide box offices were dragged here for our problem.
- Filter For Fields dragged here, we can easily pick any subset and PivotTable will show calculations based on that subset. We have no fields in the Filter area.
Besides formatting cells, we can even change the way in which how the data will be summarized. Suppose that we select anywhere in column B and right-click on it. Then select Value Field Settings to open Value Field Settings dialog box. You can see that we can also perform different calculations like average, max, count, etc. For our problem, we choose Average.
The pivot table will be changed and it looks like that in Figure 4.6. You can see that only column B has changed as we only selected column B previously. And header was also replaced with “Average of Domestic”.
Summarize Pivot Table using Pivot Chart
Click anywhere inside pivot table as shown in Figure 4.4, ANALYZE tab will be prompted (middle panel of Figure 5.1). Click on ANALYZE tab, you can see PivotChart in Tools group (bottom panel of Figure 6.1).
After you click on PivotChart, an Insert Chart dialog box will be prompted. From Figure 5.2, you can see that there are a lot of chart types for us to choose and for our problem, we will choose a line chart. And in the top of this dialog box, we can determine the appearance of our charts.
After we click on the OK button, a pivot chart like that in Figure 5.3 will be created. The box offices have increased a lot since 2008. And if you click on the chart, a plus, and a pen symbol will be prompted at the right side of the chart. You can use plus symbol to add, remove or change chart elements such as axes, legend, etc.
If you click on the pen symbol, you can set the style and color schema for your chart. For our problem, we chose the third style and you can see the style of the chart was changed.
Insert data slicers for Pivot Table
Sometimes, we may want to filter data and see the calculations based on that subset data. At this time, we can insert slicers for Pivot Table. Similar to inserting pivot chart, we need to click on any cell within pivot table to enable ANALYZE tab. And then click on ANALYZE -> Insert Slicer to open Insert Slicers dialog box. You can see that all the fields are displayed and we choose Year for our problem.After clicking on OK, you will see that a slicer similar to that in the middle of Figure 6.2 will be displayed in the worksheet. It has all values of the Year. If you click on 1977, the pivot table, slicer, and pivot chart will be changed. The excel will only summarize on data of 1977. And if you click on Ctrl and at the same time select 1977, 982, 1993, you will get something similar to that in Figure 6.4. The domestic box office in the year 1993 is less than that in the year 1982 or 1977. We can also select non-consecutive years like 1977, 1996, and 2008. If you click on the upper right corner of the Slicer box, the excel will summarize on whole data and the slicer does not work anymore. Read More: How to create a pivot table report in Excel
Update Pivot Table after adding new data
If you want to add new data and would like your data to be automatically included in your Pivot Table calculations, just right-click on the pivot table and select Refresh.
Conditionally format your Pivot Table
If you look at Column B within Pivot table closely, you will find that it is hard to tell the highest value at a glance. To solve this problem, we can apply conditional formatting to Pivot Table.
Select range B2: B4 and then click on Home -> Conditional Formatting -> New Rule to open New Formatting Rule dialog box. Select Data Bar in the Format Style field box and then select your own color in the color field.After you click on the OK button, you will see that a lot of data bars appear in column B. And from these data bars, you can easily tell that the highest box office occurred in the year 2015 and the lowest was in the year 1996.
Download working file
Download the working file from the link below.