Power Pivot for Excel: what it is and how to use it

Content
  1. Add lookup tables to your datasets with this ingenious add-on
  2. How to Get the Excel Power Pivot Add-in
  3. Follow along with the tutorial
  4. Add data to an Excel file and build a data model
  5. Create relationships between tables with Power Pivot Excel
  6. How to create pivot tables
  7. Convert PivotTable to PivotChart
  8. Create PivotCharts

Add lookup tables to your datasets with this ingenious add-on

You have data and a lot. To analyze all of this data, learn how to use the Power Pivot add-in with Excel to import datasets, define relationships, create PivotTables, and create PivotCharts.

The instructions in this article apply to Excel 2019, 2016, 2013, and Excel for Office 365.

How to Get the Excel Power Pivot Add-in

Power Pivot provides you with a powerful application for analyzing and analyzing business data. You don’t need specialized training to develop data models and perform calculations. You just need to enable it before you can use it.

  1. Open excel .

  2. Select File > parameters: .

  3. Select add-ons .

  4. Open drop-down menu Check and choose COM Add-ins .

  5. Select To go .

  6. Select Microsoft Power Pivot for Excel .

  7. Select Okay . Added Power Pivot tab to Excel.

Follow along with the tutorial

If you want to quickly get started with Power Pivot, learn by example. Microsoft has several sample datasets available for free download that contain raw data, data models, and data analysis samples. These are great learning tools to help you understand how professionals analyze big data.

This tutorial uses the Microsoft Student Data Model sample workbook. In the first note on the page you will find a link to download the tutorial and the completed data model.

The data in this sample Excel workbook has the following:

  • The workbook contains four worksheets.

  • Each sheet contains related data, that is, at least one column header on one sheet corresponds to a column header on another sheet.

  • The data in each worksheet is formatted as a table.

  • Each cell in the table contains data. Tables have no empty cells, rows, or columns.

There are other examples of datasets on the Microsoft website. Explore these learning resources:

  • Download data from a Microsoft Access database that describes Olympic medals.

  • Download three Business Intelligence samples that show you how to use Power Pivot to import data, create relationships, create PivotTables, and design PivotCharts.

Clear these before using a dataset. Use the CLEAN function in Excel to remove non-printing characters, run a spell check, remove duplicate rows of data, convert numbers and dates to the correct format, and regroup data.

Add data to an Excel file and build a data model

You have collected the data you need. Now it’s time to import your datasets into Excel and automatically create a data model. The data model is similar to a relational database and provides tabular data used in PivotTables and PivotCharts.

Whether you need data for a school assignment, a work project, or to complete this tutorial, you’ll find great public datasets on GitHub.

To import Excel data into a Power Pivot data model:

  1. Open a blank sheet and save the file with a unique name.

  2. Select Facts and then select Retrieve data: > From file > From workbook to open Import data dialog box.

    Select in Excel 2013 power query > Get external data and select a data source.

  3. Browse to the folder containing the Excel file, select the file, then select Import to open the navigator.

  4. Check box Select multiple items .

  5. Select the tables you want to import.

    When you import two or more tables, Excel automatically creates a data model.

  6. Select To download to import data tables into the data model.

  7. To verify that the import was successful and the data model was created, go to the section Facts and in the group Data Tools select Go to the Power Pivot window .

  8. The Power Pivot window displays your data in a worksheet format and consists of three main areas: the data table, the calculation area, and the data table tabs.

  9. The tabs at the bottom of the Power Pivot window correspond to each of the imported tables.

  10. Close the Power Pivot window.

To add new data to the data model, in the Excel window go to Power Pivot and choose Add to data model . The data appears as a new tab in the Power Pivot window.

Create relationships between tables with Power Pivot Excel

Now that you have your data model, it’s time to create relationships between each of the data tables.

  1. Select Power Pivot and then select Check to open the Power Pivot window.

  2. Select House and then select Map view .

  3. Imported tables appear as separate blocks in the schematic view. Drag to move tables to another location. Drag the corner of the window to resize.

  4. Drag a column header from one table to another or to tables that contain the same column header.

  5. Continue matching column headings.

  6. Select House and then select View data .

How to create pivot tables

When you use Power Pivot to create a data model, most of the hard work with pivot tables and pivot charts is done for you. The relationships you’ve created between the tables in your dataset are used to add the fields you’ll use to create PivotTables and PivotCharts.

  1. In the Power Pivot window, select House and then select pivot table .

  2. In the dialog box Create pivot table select new leaf and then select Okay .

  3. In the PivotTable Fields pane, select the fields you want to add to the PivotTable. This example creates a pivot table that contains the student’s name and the average of the grade point.

  4. To sort the PivotTable data, drag a field to the Filters area. In this example, the “Class Name” field is added to the “Filters” area so that the list can be filtered to reflect the student’s average grade for the class.

    To change the calculation method used by a field in the Values ‚Äč‚Äčarea, select the drop-down list next to the field name and select Value field settings . In this example, the score sum has been changed to the average score.

  5. Analyze your data. Experiment with filters and sort your data using the column header drop-down arrows.

Convert PivotTable to PivotChart

To visualize your PivotTable data, turn your PivotTable into a PivotTable.

  1. Select PivotTable and then go to PivotTable Analysis Tools .

  2. Select PivotChart to open the dialog Insert chart .

  3. Select a chart and then click Okay .

Create PivotCharts

If you prefer to analyze your data visually, create a pivot chart.

  1. In the Power Pivot window, select House and then select the drop-down arrow pivot table . A list of options appears.

  2. Select PivotChart .

  3. Select new leaf and choose Okay . The pivot chart placeholder appears on the new sheet.

  4. Go to page Analyzing PivotChart Tools and choose List of fields to display the panel PivotChart fields.

  5. Drag fields to add to the pivot chart. This example creates a pivot chart with the average grade for grades filtered by semester.

  6. Analyze your data. Experiment with filters and sort your data using the column header drop-down arrows.

Leave a Reply

Your email address will not be published.