Software & Apps MS Office 43 43 people found this article helpful Power Pivot For Excel: What It Is and How to Use It Add lookup tables to your data sets with this resourceful add-on by Coletta Teske Writer Coletta Teske is a Lifewire writer focusing on consumer electronics. She has 30 years' experience writing for Adobe, Boeing, Microsoft, and many others. our editorial process Twitter LinkedIn Coletta Teske Updated on May 07, 2020 Martin Barraud / Getty Images MS Office Excel Word Powerpoint Outlook Tweet Share Email You’ve got data and lots of it. When you want to analyze all that data, learn how to use the Power Pivot add-in with Excel to import data sets, identify relationships, build PivotTables, and create PivotCharts. Instructions in this article apply to Excel 2019, 2016, 2013, and Excel for Microsoft 365. How to Get the Excel Power Pivot Add-in Power Pivot gives you the power of business insights and analytics app. You don’t need specialized training to develop data models and perform calculations. You just need to enable it before you can use it. Open Excel. Select File > Options. Select Add-Ins. Select the Manage dropdown menu, then select COM Add-ins. Select Go. Select Microsoft Power Pivot for Excel. Select OK. The Power Pivot tab is added to Excel. Follow Along with the Tutorial When you want to get up and running with Power Pivot quickly, learn by example. Microsoft has several example datasets available as a free download, which contain the raw data, the Data Model, and examples of data analysis. These are great learning tools that provide insight into how professionals analyze big data. This tutorial uses the Microsoft Student Data Model sample workbook. You’ll find a download link to the sample workbook and to a completed data model in the first note on the page. The data in this sample Excel workbook has the following: The workbook contains four worksheets.Each worksheet contains related data, meaning there's at least one column heading on a worksheet matching a column heading in another worksheet.The data in each worksheet is formatted as a table.Every cell in the table contains data. There are no blank cells, rows, or columns in the tables. There are other example 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 how to use Power Pivot to import data, create relationships, build PivotTables, and design PivotCharts. Before using any dataset, clean it up. Use Excel’s CLEAN function to get rid of non-printable characters, run a spell check, remove duplicate rows of data, convert numbers and dates to the proper format, and rearrange the data. How to Add Data to Your Excel File and Build a Data Model You’ve collected the data you’ll need. Now it’s time to import your data sets into Excel and automatically create a Data Model. A Data Model is similar to a relational database and provides the tabular data used in PivotTables and PivotCharts. If you need data for a school assignment, work project, or to follow along with this tutorial, you’ll find awesome public datasets at GitHub. To import Excel data into a Power Pivot Data Model: Open a blank worksheet and save the file with a unique name. Select Data, then select Get Data > From File > From Workbook to open the Import Data dialog box. In Excel 2013, select Power Query > Get External Data and choose your data source. Navigate to the folder containing the Excel file, select the file, then select Import to open the Navigator. Select the check box for Select multiple items. Select the tables you want to import. When you import two or more tables, Excel automatically creates the Data Model. Select Load to import the data tables into a data model. To make sure the import was successful and the Data Model was created, go to Data and, in the Data Tools group, select Go to the Power Pivot Window. The Power Pivot Window displays your data in a worksheet format and consists of three main areas: Data Table, Calculation Area, and Data Table Tabs. The tabs at the bottom of the Power Pivot Window correspond to each of the tables that were imported. Close the Power Pivot Window. When you want to add new data to the data model, in the Excel window, go to Power Pivot and select 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 a Data Model, it’s time to create relationships between each of the data tables. Select Power Pivot, then select Manage Data Model to open the Power Pivot Window. Select Home, then select Diagram View. The imported tables appear as separate boxes in Diagram View. Drag to move the tables to a different location. Drag a corner of a box to resize it. Drag the column heading from one table to the other table or tables that contain the same column heading. Continue to match column headings. Select Home, then select Data View. How to Create PivotTables When you use Power Pivot to create a Data Model, most of the hard work involving PivotTables and PivotCharts has been done for you. The relationships you created between the tables in your dataset are used to add the fields you’ll use to create PivotTables and PivotCharts. In the Power Pivot Window, select Home, then select PivotTable. In the Create PivotTable dialog box, select New Worksheet, then select OK. In the PivotTable Fields pane, select the fields to add to the PivotTable. In this example, a PivotTable is created that contains a student name and their average grade. 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 the list can be filtered to show student’s average grade for a class. To change the calculation method used by a field in the Values area, select the dropdown box next to the field name and select Value Field Settings. In this example, Sum of Grade was changed to Average of Grade. Analyze your data. Experiment with the filters and sort data using the column header dropdown arrows. Convert a PivotTable into a PivotChart If you want to visualize your PivotTable data, turn a PivotTable into a PivotChart. Select the PivotTable, then go to PivotTable Tools > Analyze.Select Pivot Chart to open the Insert Chart dialog box.Choose a chart, then select OK. Create PivotCharts If you’d rather analyze your data in a visual format, create a PivotChart. In the Power Pivot window, select Home, then select the PivotTable dropdown arrow. A list of options appears. Select PivotChart. Choose New Worksheet and select OK. A PivotChart placeholder appears on a new worksheet. Go to PivotChart Tools > Analyze and select Field List to display the PivotChart Fields pane. Drag fields to add to the PivotChart. In this example, a PivotChart is created showing the average grade for classes filtered by semester. Analyze your data. Experiment with the Filters and sort data with the column header dropdown arrows.