How to Import Data into PowerPivot & Create Pivot Table/Pivot Chart

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 anymore 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.

Power Pivot, Download Power Pivot Add-in, Import Data

Figure 1.1

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.

Power Pivot, Download Power Pivot Add-in, Import Data

Figure 1.2

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 DatabaseLoads data from an Access or SQL Server database (for which data is relational or dimensional)
From Data ServiceImport data from a website that has an OData data feed or Microsoft Azure Marketplace
Other SourcesLoad data from all other supported data sources such as Excel files, text files, databases like Oracle and Teradata
Existing ConnectionsFrom a list of previously used external sources
Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 2.1

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 it using approach discussed above again.

Read More: How to Use Pivot Table Data in Excel Formulas

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 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.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 3.1

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.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 3.2 [click on the image to get a full view]

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 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).

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 3.3 [click on the image to get a full view]

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 existing worksheet to place our Pivot Table.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 4.1 [click on the image to get a full view]

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.

Read More: Excel Pivot Table Calculated Field (How to Insert & Edit)

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.

  1. 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.
  2. 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.
  3. Values Fields dragged here will be summarized in the table. Domestic, international and worldwide box offices were dragged here for our problem.
  4. 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 Filter area.
Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 4.2 [click on the image to get a full view]

A pivot table similar that in Figure 4.3 will be created following above instructions. Now let’s format cells to increase readability. Select column B, C, D and then right click on any cell within the select range. Select Format cells to open Format Cells dialog box. Fill the prompted Format cells dialog box as shown in Figure 4.3.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 4.3 [click on the image to get a full view]

Finally, the pivot table will be displayed as below in Figure 4.4. You can see that in 1977, the sum of Domestic box offices is $460, 998, 007.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 4.4

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 calculation like average, max, count, etc.  For our problem, we choose Average.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 4.5

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”.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 4.6

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).

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 5.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 line chart. And in the top of this dialog box, we can determine the appearance of our charts.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 5.2

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.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 5.3

If you click on the pen symbol, you can set 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.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 5.4

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.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 6.1 [click on the image to get a full view]

After clicking on OK, you will see that a slicer similar that in middle of Figure 6.2 will be displayed in the worksheet. It has all values of Year.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 6.2 [click on the image to get a full view]

If you click on 1977, the pivot table, slicer and pivot chart will be changed. The excel will only summarize on data of 1977.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 6.3 [click on the image to get a full view]

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.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 6.4 [click on the image to get a full view]

We can also select non-consecutive years like 1977, 1996 and 2008. If you click on the upper right corner of Slicer box, the excel will summarize on whole data and the slicer does not work anymore.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 6.5 [click on the image to get a full view]

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.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 7.1

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 Format Style field box and then select your own color in color field.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 7.2 [click on the image to get a full view]

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.

Power Pivot, Download Power Pivot Add-in, Import Data, Pivot Table, Pivot Chart

Figure 7.3

Download working file

Download the working file from the link below.

Read More:

 How to Create Pivot Table Data Model in Excel 2013

How to Create an Average Calculated Field in Excel Pivot Table

How to Create a Pivot Chart in Excel 2013!


I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as an SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

We will be happy to hear your thoughts

      Leave a reply