An Ultimate Guide To Excel Power Pivot

Power Pivot is an Excel add-in that is used to perform powerful data analysis and create sophisticated data models. It can handle large volumes of data from several sources and all of this within a single Excel file. In this article, you will learn the following topics.

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

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

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.

customer Info

Fig: Customer Info

order info

Fig: Order Info

How to Get the Excel Power Pivot Add-In

  • Open Excel.
  • Select File > Options.

add in

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

addin powerpoint

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

add in

Adding Data to the Data Model

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

data mode

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

data model

  • Repeat Step 1 with the Order Info table.

data model 3

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

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

Creating Relationships Between Tables

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

power

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

rela

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

rela

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.

power pivot

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

pivot

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.

pivot 3

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

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 certification 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! 

About the Author

Aryan GuptaAryan Gupta

Aryan is a tech enthusiast who likes to stay updated about trending technologies of today. He is passionate about all things technology, a keen researcher, and writes to inspire. Aside from technology, he is an active football player and a keen enthusiast of the game.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.