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 columns and rows
- If a dataset is formatted as an Excel table:
--- Adjacent columns are automatically added to the selected area
--- Formulas are autofilled for the column after one cell is complete
- Nested IF functions are a great way to group data manually
- Be sure to refresh your Pivot Table after adding new columns
Adding new row entries
- Excel Tables also automatically add adjacent columns to the selected area
- New entries can cause problems with grouped dates
- Always make sure the Ending At date is after the dates of your new entries
CTRL + Page Up/Page Down: Move to next sheet
CTRL + ALT +F5: Refresh all Pivot Tables
RMBK, G: Open Grouping dialog box
ALT + A, J: Expand entire field
ALT + A, H: Collapse entire field
CTRL + →: Move to end of data region
Your Pivot tables will often need to be updated with new columns and rows of data.
This process can prove a little tricky, so let’s spend a short lesson learning how to do it correctly.
I’ll start by adding a new column to our data set.
Because I converted the data set to an Excel table in the first lesson of this course, any data I add to the adjacent column will automatically be included in the data set.
Let’s say I want to categorize company subscriptions by size, with Small for under 100 users, Medium for under 500 users, and Large for above 500 users.
To do this, we’ll use a nested IF statement.
I’ll start by writing the title, Subscription Type, and you can see how this column has been added to the Excel table.
And now "=if", my first logical test will check if the number of users is less than 100.
And if it is, I’ll return Small in inverted commas.
And if it isn’t, I’ll create another IF statement that will check if the number of users is less than 500.
And if it is, I’ll return Medium, and if it isn’t, I’ll return Large.
Then I’ll close both brackets and press Enter.
And the data table auto fills the remaining cells in this column.
If we now go to our Pivot table, you still can’t see Subscription Type in our field list, and that’s because we haven’t refreshed our Pivot table.
To do this we can use the shortcut Ctrl + Alt + F5, and you can now see the Subscription Type has appeared.
Let’s enter this underneath Sales Person in the row labels.
Putting the Subscription Type underneath the Sales Person 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 the shortcut Alt + A H, and if we want to expand them again we can press Alt + A J.
To add additional data to our Pivot table we would simply go back to our data set and enter New Data in the adjacent column, making sure that we keep this panel of text separate from the data set.
Now let’s update our Pivot table with a new data entry.
So I’ll skip to the bottom and off camera I’ll add a new transaction for the 27th of December.
Returning to the Pivot table, I’ll expect our new transaction, Costa Airways, to appear as a large transaction for Baines in quarter four.
But when I refresh the Pivot table with Ctrl + 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 our grouping to get it into the Q4 column.
I’ll press the right mouse button G to enter the Grouping dialogue box.
And we can quickly see where the error resides.
Our current Ending at date is the 25th of the 12th, 2012, and 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.
I’ll say the 31st of the 12th, 2012.
And now when I press OK, the new transaction for Costa Airways appears in December, under Baines.
If I collapse the quarters with Alt + A H, it appears in Q4 also.
Not updating the Grouping dialogue 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.
If instead you’re using data ranges, I recommend simply changing the data source in the Options tab of the ribbon to include the new transactions.
Obviously, you’ll have to refresh the Pivot table after making this change.