We have already shown how to create and copy a Pivot Table. This can seem pretty straightforward and soon can become second nature to you.
However, changing the look of our sheet with the Pivot Table in it can be a little trickier.
What if you wanted to add a column or a row to an existing Pivot Table? We will show you how to do it in the text below.
Adding the Data to the Data Model
For this example, we will use the table with basketball players from the NBA league, some of their stats, and their salaries.
We will select the whole table and then go to the Insert tab and then Pivot Table. A familiar pop-up window will appear:
Since we created a table for our range and named it Table1, it is logical that our source data will be Table1.
To make things easier to explain, we will use New Worksheet for the location of our Pivot Table.
Finally, and most importantly, we have to click the little box: Add this data to the Data Model.
A Data Model allows you to integrate data from multiple tables, effectively building a relational data source inside an Excel workbook.
In our case, it is useful because it will allow us to add columns into our Pivot Table without affecting the numbers in it.
You will notice that it will take more time for Excel to create Pivot Table with this option than without it. It is a matter of seconds, but still worth noting.
Add a Column to a Pivot Table
Now that we have our data into the Pivot Table, we will put players into the row field and averages of points into the value fields:
If you, for whatever reason, wanted a different value (for example, a total sum of points) all you have to do is click the field in values (in this case Average of Points) and select Value Field Settings.
You will be presented with the window as shown in the picture below:
There are various options for you to choose from.
Once you finish that up, your final table shows like this:
Now, let’s say that we got info that we have to add the team name for every player on the list.
We could add the team name column into our original table, but we would have to do it for some players more than once since their names are reappearing in our table.
The quickest way is to add a column to our table. However, if we select the B column, right-click on it and select insert, we will get an error message:
This message tells us that we cannot change these cells because this will affect our Pivot Table. Excel suggests that we move our Pivot Table and then insert a column. But we do not want that.
We would get the same message even if we did not choose to add this table to a Data Model.
However, the difference is that with Data Model, we can click on the table, then go to PivotTable Tools in the ribbon, select Analyse, and then select OLAP Tools.
OLAP is short for Online Analytical Processing and represents technology that is used to organize large business databases and support business intelligence.
Finally, we have to choose to Convert to Formulas from OLAP Tools dropdown menu to be able to manipulate our table.
We can notice that our table looks differently and that our table cells now have different values, for example:
Also, we do not have any PivotTable Tools at our disposal.
On the upside, now we can add rows and columns as we like. We click on the B column and add a column. Then, we can add all the matching teams for our players.
Our table now looks like this:
This table is still interactive, meaning that, if we change the data for some of the players in our original table, the data in this table will change as well.
All we have to do is refresh it. Since the table is now a part of the Data Model, for us to implement the changes, we have to click on the table, then go to the Data tab and select Refresh all .
The numbers in our table will change accordingly.