An Ultimate Guide To Excel Power Pivot

What Is a Power Pivot?

According to Microsoft, “Power Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.”

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

Sample Data

We will use two data sets for our learning purpose. One is the Customer Info table, and the other is the Order Info table. Both the tables have CustomerID as the common field.

Fig: Customer Info

Fig: Order Info

How to Get the Excel Power Pivot Add-In

• Open Excel.
• Select File > Options.

• Select Add-Ins.
• Select the Manage drop down menu, then select COM Add-ins.

• Select Go.
• Select Microsoft Power Pivot Excel. 
• Select OK. It adds the Power Pivot tab to Excel.

Adding Data to the Data Model

• Select the range of Customer Info table. Then, select Add to Data Model from the Power Pivot tab.

• You will notice that the new pop-up window will appear. This is the Power Pivot window.

• Repeat Step 1 with the Order Info table.

You will notice that it creates a new tab in our Power Pivot tab for each data added to our data model.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

Creating Relationships Between Tables

• Select the Power Pivot window. Go to Home, then select Diagram view.

• The imported tables will appear as separate boxes in the Diagram view.

• Drag the column heading from one table to another table that contains the common field.

Create Pivot Tables Using Data Models

Now that we have successfully established the relationship between the tables in our data model, we will create the PivotTable.

• In the Power Pivot window, go to Home > PivotTable.

• The Create PivotTable dialog box will appear. Select New Worksheet and then select OK. 

Now that we have created the pivot table using the data model, let's calculate each company's total freight value. To do that, drag the company name from Customer Info to the Rows section and freight to the Values section.

Gain the knowledge you need to turn your organization’s data into a tactical asset to generate business value. Sign up for our Business Analytics for Strategic Decision-Making program by IIT Roorkee!

Conclusion

In this article, we have discussed what Power Pivot is and how to use Power Pivot through an example.

You’ve learned to import the data into the model, create relationships and a measure, and then use them in PivotTables. Power Pivot is an extremely useful and powerful tool, and this article offers you an introduction to its capabilities and what you can do with it.

Boost your analytics career with powerful new Microsoft Excel skills by taking the Business Analytics with Excel course, which includes Power BI training

This Business Analytics for Strategic Decision Making with IIT Roorkee course teaches you the basic concepts of data analysis and statistics to help data-driven decision making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.

Do you have any questions for us? Feel free to ask them in this article's comments section, and our experts will promptly answer them for you!