The Pivot Table is Google Sheets’ answer to Microsoft Excel’s PivotTable feature. This feature allows the user to quickly summarize a large amount of structured data through few clicks, giving the user a powerful tool for free. Pivot Table can be a bit more challenging to tame, but this tutorial will help you master it by considering the common demands of data analysis.
The Pivot Table contains a lot of predefined functions you can use in summarizing your data into meaningful information. However, you have to sometimes apply a certain formula to it that is not predefined in the Pivot Table. How can you do so? There are two ways, one involves adding extra columns in the data sheets while the other does not. We will tackle both ways here.
Adding custom formulas in the source sheet first
What is the advantage of adding the custom formula in the source sheet first? It lets you keep the data for individual entries intact and accessible. There are times where you have to keep the individual data for later auditing. You won’t be able to do that if you go directly to the Pivot Table.
For our example, we need to calculate the profit from each sale. To calculate the profit from each sale, we use the following formula:
Profit = Sales - Cost
We need to calculate this to make accurate budgeting. Here, we have the sheet we're working with:
To do so, follow the following steps:
Step 1: Go to the individual sheet, and add a column that will contain the formula.
Step 2: Apply the formula. For our example, the formula for cell H2 will look like this:
Google Sheets will automatically calculate it. To apply it to all the cells, one quick technique is to click on the small square box on the lower-right corner of the highlight on the cell where you entered your formula and drag it down across your range.
Voila! You have now applied the formula to all rows below the header.
Step 3: Go to the sheet containing the Pivot Table and adjust the range to include the new column. Access the Pivot table editor by clicking on any cell of the Pivot Table and then look for the range box, just below the label Pivot table editor.
Step 4: For our example, the change is simply from week28!A1:G450 to week28!A1:H450. Refresh the Pivot Table by clicking on any cell outside the Pivot Table and then clicking on any cell of Pivot Table again. The new column will be listed when you click Add beside the Values.
Adding custom formulas in the Pivot Table directly
If you do not need to keep the values for individual entries and just want the summary or you are not allowed to modify the original sheets, then you can go straight to the sheet containing the Pivot Table. Here are the steps:
Step 1: Open the sheet containing the Pivot Table. Open the Pivot table editor by clicking on any cell in the Pivot Table.
Step 2: Go to the Values section of the Pivot table editor and click the Add button beside it. A drop-down list of columns from the source sheet of the Pivot Table will appear. In the end, there is an option called Calculated Field. Click the Calculated Field.
Step 3: A new entry for the Calculated Field will appear listed below the Values as label. The custom formula should be placed in the box below the Formula box, using the labels of the columns in the Pivot Table as the variables.
For our example, the custom formula is
='total price' - 'total cost',
Notice we are referencing the name of the columns we included in the Pivot Table. To customize the name of the field, simply double-click on the cell containing the header of the column and type the name.
For instance, we can change the name "Calculated Field 1" to "Profit." Our example now looks like this:
Suscribe to get more data and analytics tips!
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.