ExcelInsider Team
Pivot tables are handy for data analysis, but using a data model significantly enhances their usefulness. For working with multiple tables and databases, using the data model is mandatory. In this article, we will learn the techniques of pivot table add to data model.
➤ Go to the Insert
➤ Locate the Tables section on the left and click on PivotTable.
➤ Check the “Add this data to the Data Model” box, and click OK.
While adding a new pivot table to a data model is easy, you might want to add an existing pivot table to the data model as well. In this article, we will learn how to add a pivot table to the data model, whether the pivot table is new or not. Let’s begin.
What is a Data Model in Excel?
The data model is a tool that allows you to use some powerful features to analyze your data. In a data model, you can organize multiple data sources that connect with each other within a single workbook. Moreover, you can use Power Query, manage relationships, integrate pivot tables, etc.
Adding a New Pivot Table to the Data Model
Today, we have a dataset of budget information. There are categories, budgets for those categories per month, the actual amounts spent, and whether the actual spending was compliant with the budget or not. While we can create a pivot table to analyze the data more effectively, it would also be beneficial to use a data model, as additional tables may be added later. This table only includes information for a quarter of the year.
➤ Click on the table, and go to the Table Design
➤ From the Tools section, select Summarize with PivotTable.
Note:
You can also go to the Insert tab and select PivotTable from the Tables section, it’s the same.
➤ A small tool window will open. Check the box that says, “Add this data to the Data Model”, and click OK afterwards.
➤ The pivot table will be created while being connected to the data model.
Adding an Existing Pivot Table to the Data Model
Let’s say you have already created a pivot table and forgot to add it to a data model. It is possible to add data from the existing pivot table to a data model as well. Here is how you do it:
➤ From the PivotTable Fields section, select More Tables from the area where you choose fields.
➤ A small question will pop up, asking you to create a new pivot table. Click Yes to proceed.
➤ A new pivot table will now be created in a new sheet, connected to the data through a data model.
Managing the Data Model
Now that you have added the pivot table to the data model, you might want to check the model to see it in action. Here is how you do it:
➤ From the Data tab, locate the Data Tools section and click “Manage Data Model”.
➤ If the Power Pivot is not enabled already, Excel will prompt you to enable the data analysis features.
➤ The data model window will open for you to check.
Frequently Asked Questions
How do I add a data table to a PivotTable?
From the PivotTable Analyze tab, click on Change Data Source from the Data section. From there, you can select a new table or add some other data range to the pivot table as well.
How to get Power Pivot?
From the File menu, go to Options. A new window will open. In the left panel, select Add-ins. Below, there will be a combo box labeled “Manage”. Select COM Add-Ins and press Go. Now check the boxes called “Microsoft Power Map for Excel” and “Microsoft Power Pivot for Excel”. Hit OK to confirm.
How do I automatically add data to a PivotTable?
While making the pivot table, make sure your source data is not a regular data range, but an actual table. That way, when you add columns or rows to your table, the new data will be added to the pivot table as well. Make sure to refresh the pivot table afterwards.
Is Power Pivot free?
If you have Microsoft Excel, you have access to Power Pivot for free. It is already bundled with the Microsoft 365 package.
What is Power Pivot vs PivotTable?
Power Pivot is used for a large dataset with a lot of relations between datasets and circumstances where complex analysis is required. A pivot table is for some straightforward analysis with a single table and a small dataset.
Wrapping Up
In this article, we have learned how to add a pivot table to a data model. We hope that you have got a clear understanding about working with data models, pivot tables, and pivot table add to data model. Leave your questions and concerns below, we love audience feedback. Download the practice sheet and try applying the methods we mentioned to learn practically.
