Introduction to Power Pivot in Excel

Excel PivotTables are fantastic for basic analysis, but they have limitations, particularly when working with large datasets or multiple data sources.

If you've ever wished you could handle advanced calculations, work across multiple tables, or analyze millions of rows without performance issues, then Power Pivot is the solution you've been waiting for.

In this post, we'll explore how to unlock Power Pivot's full potential and elevate your data analysis skills.

Introduction to Power Pivot Video

Get the Power Pivot Example File

Try Power Pivot yourself with the example file used in the video:

Why Power Pivot?

Traditional PivotTables allow you to summarize and analyze data effectively, but when it comes to larger datasets or more complex calculations, they can fall short.

Power Pivot is an advanced tool built right into Excel, designed to handle these more demanding tasks.

Here's what makes Power Pivot a game-changer:

• Multiple Data Sources: Integrate data from various sources, such as Excel tables, databases, online services and more.
• Advanced Calculations: Use powerful formulas and data analysis expressions (DAX) for complex calculations using a function language that's modelled on Excel's.
• Speed and Performance: Work with millions of rows of data without slowing down Excel.

Now let's dive into the details of how to use Power Pivot in your everyday workflow.

Enabling Power Pivot in Excel

Before using Power Pivot, you need to ensure it's enabled in your version of Excel. Here's how to do it:

1. Check for Power Pivot in the Ribbon: Go to the Data tab and look for the Data Model button. If you see it, great! You're ready to go.

2. Activate Power Pivot: If Power Pivot is missing, follow these steps:

• Go to the File tab > Options.
• Choose Add-Ins from the sidebar.
• In the Manage dropdown, select COM Add-ins and click Go.
• Check the box next to Microsoft Power Pivot for Excel and click OK.

Once activated, Power Pivot will appear as a new tab in your ribbon:

Loading Data into Power Pivot

Now that Power Pivot is ready to use, let's load some data. For this example, we'll work with a movie dataset, spanning 1976 to 2016, which includes fields like director ID, budget, popularity, and revenue.

The movie data is in an Excel Table named 'Movies'.

There is also a table with the directors' names and gender in an Excel table named 'Directors':

Steps to Load Data:

1. Select the First Table: Click any cell in your first table.
2. Add to Data Model: In the Power Pivot tab, click Add to Data Model.
3. Repeat for Other Tables: Do the same for additional tables (e.g., the Directors table).

By loading these tables into Power Pivot, you bypass the need for complex lookup functions like VLOOKUP or XLOOKUP, reducing formula clutter and keeping your file size down.

Connecting the Tables: Creating Relationships

The magic of Power Pivot happens when you start linking tables using common fields. For example, let's connect our Movies and Directors tables using the director_id field they share.

1. Open Diagram View: In the Power Pivot window, switch to Diagram View to visualize the relationships between tables.
2. Drag and Drop: Simply drag director_id from one table to the other, creating a relationship between them.

Power Pivot automatically detects a one-to-many relationship, where each director in the Directors table only appears once, but they can have multiple movies associated with them in the Movies table.

This ability to connect tables allows you to analyze data across multiple tables in one report, without the need for extra formulas.

Creating a Power Pivot PivotTable

With your data loaded and linked, it's time to build a PivotTable that pulls from both tables:

1. Create the PivotTable: From the Power Pivot window, go to the Home tab and click PivotTable. Alternatively, from the Excel ribbon, go to Insert > PivotTable > From Data Model.

2. Choose Fields: In the field list, you'll now see fields from all tables. Add director_name to the rows and revenue to the values to summarize total revenue by director.

You've now built a report that spans multiple tables, showcasing revenue by director name.

Advanced Techniques: Calculated Columns and KPIs

Now that you've got a basic report, let's take it up a notch by adding calculated columns and Key Performance Indicators (KPIs).

Creating a Calculated Column:

1. Open Power Pivot Window
2. Add a New Column: Scroll to the right until you see an empty column labelled Add Column, select it.
3. Enter a Formula: In the formula bar, type =[revenue] - [budget] to calculate the profit for each movie. Press Enter and rename the column to Profit.
4. Adjust for Inflation: For a more accurate analysis, adjust for inflation with the formula:

=([revenue] - [budget]) * (1 + .0373) ^ (2016 - [year])

This gives you an adjusted profit figure that accounts for inflation over time.

Setting Up KPIs:

1. Create a Measure: In the Power Pivot window, select the Profit column and choose Average from the AutoSum dropdown. This creates a measure that will be used as the baseline for the KPI.

2. Define Thresholds: Right-click the measure and select Create KPI.

3. Set your thresholds—reference another measure, or set absolute values — and customize the colors and icons.

4. Visualize in a PivotTable: Add this KPI to your PivotTable to track how each director's movies perform against the average profit.

Making Your Report Interactive with Slicers

Finally, to make your PivotTable more interactive, let's add Slicers:

1. Insert a Slicer: Select a cell in the PivotTable and go to Insert > Slicer. Choose director_name to easily filter the report by director.

2. Multiple Slicers: You can add more Slicers for fields like year and gender, giving you dynamic control over the data.

Next Steps

Power Pivot is a robust tool that allows you to supercharge your data analysis in Excel. Whether you're dealing with large datasets, multiple data sources, or complex calculations, Power Pivot provides the flexibility and power you need.

To take your skills further, consider diving into our Power Pivot & DAX course—the ultimate guide to mastering this tool and transitioning to Power BI effortlessly.

Curious about how to streamline your data loading process with Power Query? Check out our next tutorial for automating boring data gathering and cleaning tasks with Power Query.