Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams (2023 Update)
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11 (2023 Update)
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Data Analytics
        • Introduction to Analytics and Artificial Intelligence
        • Data Analytics in Excel
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > Getting Started with Power Pivot: Advanced Excel

Getting Started with Power Pivot: Advanced Excel

Power Pivot is a tool in Excel that enables us to create data models and perform more complex operations than the standard Pivot Tables allow.

You may have seen references to the data model in Excel before when creating PivotTables – this is Power Pivot. It is also known as the data model.

This article will provide an overview of the advantages and how to get started with Power Pivot.

What are the Advantages of using Power Pivot?

Power Pivot is a data modeling tool. It prepares data for analysis in a way that has many advantages over the classic use of Excel PivotTables.

These advantages include;

Create PivotTables from multiple different tables

In Excel, lookup functions such as VLOOKUP are often used to combine data from multiple tables into one for a PivotTable.

In the example below, a VLOOKUP is used to return the product category from another range into a single table for a PivotTable.

With Power Pivot, you can connect to multiple data sources (Excel tables, CSV, text files, web data) and create relationships between them.

We then have a single source (the data model) to create our Pivot Tables.

In the example below, a relationship is established between the sales and products tables rather than combining the data into one.

Power Pivot can handle vast volumes of data

By connecting to external sources such as CSV files and SQL databases instead of loading the data into an Excel workbook, we can use millions of rows of data.

We are not limited by the number of rows in an Excel worksheet or constrained by the time it would take for formulas such as VLOOKUP to perform calculations.

With Power Pivot, our workbooks remain lean and fast.

More Powerful Calculations

PivotTables only provide a few calculations, such as sum, count, and average.

These are great, but if you need something different, you need to create calculated fields or write more formulas in your source data.

In Power Pivot, a formula language named DAX can be used to write formulas beyond what a standard PivotTable provides.

The aggregated formulas created using DAX are known as Measures. These Measures can be formatted in advance and are reusable in PivotTables and other Measures.

This provides a significant advantage in saving time and having faster calculations.

Getting Started with Power Pivot

Let’s walk through a simple example of using Power Pivot.

For this example, we have an Excel file with two worksheets. One with a range of sales data and the other with a range of product data.

We will load them to the data model, create a relationship between them, write some DAX formulas, and then create a PivotTable from the Power Pivot model.

Download the Excel file to follow along.

Where is Power Pivot in Excel?

There are two main ways to access Power Pivot in Excel.

You can open the Power Pivot window from the Data tab by clicking Data > Manage Data Model.

There is also a Relationships button to manage the relationships without opening the Power Pivot window.

Or from the Power Pivot tab. There is a Manage Data Model button there and also more functionality, such as being able to create Measures.

Install the Power Pivot Add-In

The Power Pivot tab is not visible by default. It needs to be installed before you can start using it.

  1. Click File > Options > Add-ins.
  2. Select COM Add-ins from the Manage list, and click Go.

3. Check the box for Microsoft Power Pivot for Excel and click Ok.

The Power Pivot tab is now available on the Ribbon. We will use this tab later in the article to create Measures.

Loading Data into the Power Pivot

In this example, we are loading data from the worksheets of this Excel file.

Let’s begin with the sales range.

  1. Click anywhere in the range of cells and click Data > From Table/Range.

The data needs to be formatted as a table. As this was a range, the Create Table window appears to convert the range to a table before loading it to Power Query.

The Power Query window opens. This is the tool to connect to your data sources and load them to the Power Pivot model.

2. In the Query Settings pane on the right, name the query ‘sales.’

3. On the Home tab, click the Close & Load list arrow and click Close & Load To.

4. Click Only Create Connection and Add this data to the Data Model.

This query is now loaded to the Power Pivot data model.

These steps can now be repeated for the products range. Every step is the same, except the query is named ‘products.’

The two queries are loaded to the data model and are shown in the Queries & Connections pane.

Power Query is extremely powerful and is used to get data from external sources such as Excel files, databases, CSVs, etc.

Transformation steps are performed to clean and format the data ready for the model. And these steps can be refreshed to update your reports at the click of a button.

This is a simple example to demonstrate the use of Power pivot. No Power Query steps have been used.

Take our Power Query, DAX, and Power Pivot course to see how Power Query and  Power Pivot work together.

Create a Relationship between the Tables

We now need to create a relationship between the ‘sales’ and the ‘products’ tables. We can then use fields from both tables in our PivotTable reports.

Click Data > Manage data Model to open the Power Pivot window.

You are taken to the Data view. The two tables look like Excel worksheets, but they are not, and you cannot edit the cells here.

Click the Diagram View button on the Home tab.

This is the best view to create and manage the relationship between the tables of your model.

We only have two tables in this simple example. We need to create a relationship by using the two common fields, just like when using VLOOKUP to find a related match in another table.

In this example, the common fields are the ‘Products’ field in ‘sales’ and the ‘Product ID’ field in ‘products.’

Click and drag from one field to the other. As you do so, a line is drawn, and the two fields highlight green.

A one-to-many relationship has been created. A ‘1’ is shown at the end of the line by the ‘Products’ table, and an infinity symbol is shown at the end of the line by the ‘sales’ table.

Close the Power Pivot window.

Create Measures with DAX

DAX stands for Data Analysis Expressions and is the formula language of Power Pivot.

It is extremely powerful and requires much more time and demonstration than what this article can offer. Learn DAX with our Power Query, DAX, and Power Pivot online course.

As a quick example, let’s create a measure to sum the sales totals.

Click Power Pivot > Measures > New Measure

A window appears to simplify the creation of Measure and provide the options we need.

  1. Select ‘sales’ as the table to store the Measure in.
  2. Type Total Revenue for the Measure name.
  3. Type the following formula into the box provided. We will use a simple SUM function.

=SUM(sales[Total])

As you type a tooltip and list of tables and column names, appear to help you write the measure.

4. Select Currency from the formatting options provided. Choose which currency symbol to use and how many decimal places you want to use.

Creating PivotTables from the Data Model

We can now create a PivotTable from the data model.

The PivotTable will not look much different from any other PivotTable you have created in the past.

The name Power Pivot may be misleading in this aspect. People expect extra tools sometimes.

Power Pivot is a tool to prepare and model data ready for the PivotTable. Using the model, a PivotTable can now handle more data from multiple tables and use powerful DAX calculations.

  1. Click Insert > PivotTable.
  2. Select to Use this workbook’s Data Model.
  3. Choose to place the PivotTable report on a New Worksheet.

The PivotTable is inserted, and the field list shows the two tables and also the two worksheet ranges.

The two tables have a cylinder icon indicating that they are from the data model.

If we had connected data external to the workbook, we would not have seen the ranges in the worksheet.

Let’s create a quick PivotTable to see the data model and our Measure in action.

Expand the ‘products’ table and drag the ‘Category’ field to the Rows area.

Then expand the ‘sales’ table and drag the ‘Total Revenue’ measure to the Values area. The fx symbol identifies Measures.

The following PivotTable is created. It demonstrates two fields from different related tables being used, and the Measure is already formatted.

This is a simple example to show the model in operation. Go ahead and create more PivotTables and change the fields.

This article is an introduction to what is Power Pivot and a demonstration of how to use it.

To learn more enroll in our Power Pivot course

Check out a live recording of a webinar we held on Power Pivot and Power Query.

Post written by Alan Murray, founder of Computergaga

Deborah Ashby

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Convert XML to Excel? – 2 Easy Methods 

How to Create an Excel Gauge Chart the Easy Way? 

Microsoft Project 2019: Linking Tasks and Dependencies

Using Budget Resources in Microsoft Project 2013

The Most Used Access Shortcuts – Download

How to Use the Excel DATEDIF Function? 3 Easy Examples

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)