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

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now
Become The Highest-Paid Business Analysis Expert

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.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now
Become The Highest-Paid Business Analysis Expert

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

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Program NameDurationFees
Post Graduate Program in Data Analytics

Cohort Starts: 27 May, 2024

8 Months$ 3,749
Post Graduate Program in Data Science

Cohort Starts: 28 May, 2024

11 Months$ 4,199
Caltech Post Graduate Program in Data Science

Cohort Starts: 29 May, 2024

11 Months$ 4,500
Post Graduate Program in Data Engineering

Cohort Starts: 4 Jun, 2024

8 Months$ 3,850
Data Analytics Bootcamp

Cohort Starts: 11 Jun, 2024

6 Months$ 8,500
Applied AI & Data Science

Cohort Starts: 18 Jun, 2024

3 Months$ 2,624
Data Scientist11 Months$ 1,449
Data Analyst11 Months$ 1,449