How to Import and Use Data into Power Pivot in Excel

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.

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…

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

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

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.

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

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.

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.

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.

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

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.

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.

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.