Adding records

Before reading this article, continue working on the file from the previous activity or download this activity file and open it in preparation.

Whilst refreshing the data cache from a static data source is a simple button click, adding new records to the data cache can be a little more complicated.

As the source for the data cache (hence the pivot table), spans a fixed range of cells (A1 through to O180 in the example), all cells outside of this range are not included in the data cache.

This may be ok if the analysis being performed is a once-off or ad-hoc summary, but as this example is a working file, new records are regularly being added directly to the source data worksheet when they occur.

Let’s add a record.

Open the example file, activate the ‘Incident Register’ worksheet, and populate the first blank row (181) with the following incident details:

Report number= CPM2288; date= 01/04/2023; Employee Name= Alby Roberts; Project= Orange Energy Corp; Brief detail= Struck thumb with hammer; Notifiable = No; Investigation = No, Cause of Injury = Hit by moving object(s); Incident Type= Medically Treated Injury; Immediate Corrective Action= Driven to hospital ER for treatment; Injury Type= Crush; Body Location (General)= Arm; Body Location (Specific)= Thumb; Days Unfit for Duties= 0; Days Restricted Duties= 1

Return to the pivot table and refresh the cache using one of the methods learnt in the previous activity.

The update we’re hoping to see is an increase (of 1) in the total number of medically treated injuries that occurred whilst working on the Orange Energy Corp project, but our pivot table shows no change.

Let’s understand why this is.

When this pivot table was created, it created the cache based on the range of cells A1 through to O180 in the Incident Register worksheet.

The new record was added to row 181 on the Incident Register, which is outside of the pivot cache source. As the source data of the Pivot cache (cells A1:O180) are unchanged, so is the pivot cache unchanged and hence the pivot table remains unchanged.

To include the new row in the pivot cache, the source data range will need to be updated to include this new row.

From the pivot table tab, click the Change Data Source button. When the ‘Change pivot table Data Source’ dialog box is displayed, clear the Table/Range and (with your mouse) select the correct range (A1:O181 on the Incident Register worksheet) and click OK.

The table/range in the dialog box should read ‘Incident Register’!$A$1:$O$181

Notice the pivot table update this time to show 1 Medically Treated Injury occurred at Orange Energy Corp.

Although we can easily change the data source to include new rows (like this), this method is a little cumbersome when basing a pivot table on a working file that’s regularly growing (like the example), and it may be a little misleading if another user assumes that the pivot table report is summarising all of the records on the source data worksheet.

When the analysis is based on a data extract, or it’s an ad hoc analysis or for some other reason the data range isn’t likely to grow; creating a pivot table based on a fixed range of cells in Excel will work perfectly, but when the source data is a working file, we’ll have to regularly change the data source for our pivot table in order to keep it current, unless we base the pivot table on a dynamic range, like a table.