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.
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:
=E2-G2
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.
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:
