Sign in or start a free trial to avail of this feature.
6. Updating Pivot Tables with New Data
Adding columns and rows to PivotTables is made easier by using Tables but still requires some changes to your PivotTable settings, notably in the Group settings.
Adding New Columns (00:03)
In the first lesson of this course, we created an Excel table for our data. This means that any data added in an adjacent column automatically becomes part of the data set. When we write a formula for a new column, the Excel table autofills the formula for the entire column.
After we create a new column, it won’t automatically appear in the Pivot Table’s field list. To see it, we need to refresh the Pivot Table. We do this by selecting any cell in the table and pressing Alt + F5.
Adding New Rows (02:25)
When we add a new row to the data set, it will automatically become part of the Excel table. When we refresh the Pivot Table, the new row appears in the Pivot Table, however it may not appear in the way you want.
In our case, the new transaction does not fit into the existing quarters, and is given its own column. This issue is caused by grouping. When we grouped dates, we selected start and end dates based on the existing data. If the new transaction falls outside the previously defined date range, we need to adjust the start or end dates to accommodate the new data. Once we do this, the Pivot Table works as expected. This is a common issue when adding new data.
When working with Pivot Tables, you'll find that they often need to be updated with new columns and rows of data.
In this lesson, we'll learn how to update a Pivot Table when new columns and rows are added to the data set. We'll start by adding a new column to our data set. Because we converted the data set to an Excel table in the first lesson of this course, any data we add to the adjacent column will automatically be included in the data set.
Let's say we want to categorize companies subscriptions by size with under 100 users as small, under 500 users as medium and above 500 users as large.
To do this, we'll use a nested IF statement, we'll start by writing the title subscription type, and we can see how this column has been added to the Excel table. We'll now enter equals IF, our first logical test will check if the number of users is less than 100, if it is, we'll return "small" in quotes, if it isn't, we'll create another IF statement that will check if the number of users is less than 500, if it is, we'll return medium, if it isn't, we'll return large, we'll then close our parentheses and press Enter.
The data table auto fills the remaining cells in this column. If we now go to our Pivot Table, we still can't see subscription type in our field list, this is because we haven't refreshed our Pivot Table. To do this, we can use a shortcut Control + Alt + F5. We can now see that subscription type has appeared.
Let's add this underneath sales person in the row labels.
Putting the subscription type underneath the salesperson allows us to see what proportion of revenue comes from large, medium and small clients. If we want to collapse these menus, we can use a shortcut Alt+ A + H, if we want to expand them again, we can press Alt+ A + J.
To add additional data, we simply go back to our data set and enter new data in our adjacent column, making sure we keep this panel of text separate from the data set. Let's update our Pivot Table with a new data entry. We'll skip to the bottom and off-camera, I'll add a new transaction for the 27th of December. Returning to the Pivot Table, we expect our new transaction Costa Airways to appear as a large transaction for Baines in the fourth quarter. But when we refresh the Pivot Table with Control + Alt + F5, it actually appears in a new column entitled greater than the 25th of December, 2012. So clearly, we're going to have to change grouping to get it into the Q4 column. We'll press the right mouse button and G to enter the grouping dialog box.
We can quickly see where the error resides. Our current ending at date is the 25th of December, 2012.
This date is the day after the last transaction in our original data set. So we must extend this date to include our new transaction. We'll say the 31st of December, 2012.
Now when we press okay, the new transaction for Costa Airways appears in December under Baines. If we collapse the quarters with Alt + A + H, it appears in Q4 as well. Not updating the grouping dialog box is a common error when adding new entries to a Pivot Table. Anytime you include new entries, just make sure that the ending at date is set correctly to include them.