Add lookup tables to your data sets with this resourceful add-on
Youve got data and lots of it.
Instructions in this article apply to Excel 2019, 2016, 2013, and Excel for Microsoft 365.
You dont need specialized training to developdata modelsand performcalculations.
You just need to enable it before it’s possible for you to use it.
Select theManagedropdown menu, then selectCOM Add-ins.
SelectMicrosoft Power Pivot for Excel.
The Power Pivot tab is added to Excel.
When you want to get up and running with Power Pivot quickly, learn by example.
These are great learning tools that provide insight into how professionals analyze big data.
This tutorial uses theMicrosoft Student Data Model sample workbook.
Explore these learning resources:
Before using any dataset, clean it up.
Now its 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.
SelectData, then selectGet Data>From File>From Workbookto open theImport Datadialog box.
In Excel 2013, selectPower Query>Get External Dataand choose your data source.
Navigate to the folder containing the Excel file, snag the file, then selectImportto open theNavigator.
go for the check box forSelect multiple items.
go for the tables you want to import.
When youimport two or more tables, Excel automatically creates the Data Model.
SelectLoadto import the data tables into a data model.
The tabs at the bottom of the Power Pivot Window correspond to each of the tables that were imported.
kill the Power Pivot Window.
The data appears as a new tab in the Power Pivot Window.
SelectPower Pivot, then selectManage Data Modelto initiate the Power Pivot Window.
SelectHome, then selectDiagram View.
The imported tables appear as separate boxes inDiagram 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.
SelectHome, then selectData View.
In the Power Pivot Window, selectHome, then selectPivotTable.
In theCreate PivotTabledialog box, selectNew Worksheet, then selectOK.
In thePivotTable Fieldspane, go for 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 theFiltersarea.
In this example, Sum of Grade was changed to Average of Grade.
Experiment with the filters and sort data using the column header dropdown arrows.
Create PivotCharts
If youd rather analyze your data in a visual format, create a PivotChart.
In the Power Pivot window, selectHome, then select thePivotTable dropdown arrow.
A list of options appears.
A PivotChart placeholder appears on a new worksheet.
Go toPivotChart Tools>Analyzeand selectField Listto display the PivotChartFieldspane.
Drag fields to add to the PivotChart.
In this example, a PivotChart is created showing the average grade for classes filtered by semester.
Experiment with theFiltersand sort data with the column header dropdown arrows.
