How to Import and Use Data into Power Pivot in Excel

Get FREE Advanced Excel Exercises with Solutions!

PivotTable is a useful tool 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. One of its limitations is that you can only handle as many as 1.048 million rows of data. Luckily, Excel offers another kind of tool – Power Pivot which allows you to gather, store, model, and analyze a huge amount of data in Excel. In this article, I am going to discuss how to import and use data into Power Pivot in Excel. I hope it will be the most decorated article on this topic you will ever see.

So, let’s begin.


What Is Power Pivot?

Power Pivot is also PivotTable. It just 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 files, 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 that you will learn while working with this tool.


Enable Power Pivot in Excel

Power Pivot is not set as a default tab in Excel. You need to enable it manually. For this, you just need to follow the following procedures.

Steps:

  • First of all, go to the File tab.
  • From there, select Options.

Import Data Power Pivot

An Excel Options wizard will appear.

  • Click on the Add-ins option.
  • Select COM Add-ins from the drop-down list named Manage.
  • After that, click on Go…

Enable Power Pivot in Excel

  • Check the Microsoft Power Pivot for Excel box from the COM Add-ins wizard.

Thus, Power Pivot will be enabled and we will have the Power Pivot tab.

Enable Power Pivot in Excel

Notice

Remember Power Pivot is available only on the Excel versions after Excel 2013. If you are using any previous versions like Excel 2010, you need to go to Microsoft Download center, download the Power Pivot add-in for Excel 2010, and install it.


Import External Data Using Power Pivot Add-in

In Power Pivot, we can import external data from different sources. We can import data from the following sources:

  1. From Database
  2. From Data Service
  3. Other Sources
  4. Existing Connections

Import External Data Using Power Pivot Add-in

You can import external data from any of these sources based on your needs. To import data, you need to follow some steps. Those are mentioned below:

Steps:

  • Go to the Power Pivot tab first.
  • Next, click on Manage from the ribbon.

A new window named Power Pivot for Excel will appear.

  • After that, go to Get External Data under the File tab.
  • Then, you have to choose an option based on your source file type. In my case, I have decided to import data from a Text That’s why I chose From Other Sources.

Import External Data Using Power Pivot Add-in

  • Select Text File from Table Import Wizard.
  • Along with that, press the Next button.

  • Now, insert your file location in File Path.
  • Define the column pattern in Column Separator.
  • Moreover, check the Use first row as column headers box.
  • Afterward, click on the Finish button.

Import External Data Using Power Pivot Add-in

Next, the confirmation of importing will appear in the box.

  • Press Close to successfully finish the data importation

Thus, we can simply import data in Power Pivot.

Import External Data Using Power Pivot Add-in


Switch from Power Pivot to Original Workbook

We might need to switch from Power Pivot to Excel’s Original Workbook. For this, you just need to simply click on the Excel sign on the title bar.

Switch from Power Pivot to Original Workbook


Use Power Pivot to Create a PivotTable

We can use the imported data into Power Pivot to create a PivotTable. Just follow the order mentioned below.

Steps:

  • Click on the PivotTable option under the File tab in the Power Pivot window.

Use Power Pivot to Create a PivotTable

  • Next, select the location where you want to have your PivotTable. I have picked Sheet2 and cell B2.
  • Then, press OK.

  • Define your row and columns from the PivotTable Fields.

Use Power Pivot to Create a PivotTable

Thus, we can use Power Pivot to create a PivotTable.

You can modify your PivotTable according to your choice.


Summarize PivotTable Using PivotChart

We can decorate our imported data from Power Pivot with a PivotChart. For this, we are going to use the PivotTable that we have created in the previous section.

Steps:

  • First, put the cursor on a cell of the PivotTable (i.e. B4).
  • Next, go to PivotTable Analyze tab.
  • Select PivotChart from the ribbon.

Summarize PivotTable Using PivotChart

An Insert Chart wizard will appear.

  • Choose your PivotChart I have selected Line Pattern.
  • Finally, press OK to finish the process.

Thus, we can summarize the PivotTable with a PivotChart.

Summarize PivotTable Using PivotChart


Download Practice Workbook


Conclusion

In this article, I have tried to give a complete overview of how to import data in Power Pivot from external sources as well as create PivotTable and PivotChart with those imported data. If there is any problem regarding this article, comment below to have your answer. You can visit our site to gather more knowledge on Excel.


<< Go Back to Power Pivot Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Zhiping Yan
Zhiping Yan

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 a 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... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo