How To Use Pivot Tables To Summarize Data by Time On Google Sheets
Learn how to summarize your data by data, month, year, or any other relevant time period on Google Sheets by using the Pivot Table function. Follow our step-by-step tutorials to complete the tasks in just a couple of clicks!
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.
For this tutorial, we will consider summarizing data by time period, whether it is by days, months, or years. To do so, we need to first make sure we have all the relevant data in our spreadsheet; for our example, it’s the date of entry.
As an example, let’s say we want to see the daily sales and the number of items bought from our store.
Fortunately for us, we have a column containing the date of entry. Google Sheets automatically reads the dates and formats it as such. We will use that functionality to make our work easier.
Summarize by date
Step 1: Right-click on any cell inside the sheet that contains the data, and then click on the Data option in the menu, then click on the Pivot table option in the drop-down list.
On the box with the label Create pivot table, check if the option New sheet is selected:
Step 2: The new sheet containing the Pivot table will be displayed, alongside the Pivot table editor. Look for the Add button beside the Rows. A list of columns in the original sheet shall be displayed, select date.
Step 3: To add the data, scroll down the Pivot table editor and select the Add button beside the Values. A drop-down list will appear. Click on the quantity you want to include. By default, the Pivot table will calculate the sum of all the values.
To replace it, you can click on the box underneath Summarize by and choose other options. Some of them are:
- COUNT - counts the number of entries
- AVERAGE - calculates the average value of the entries
- MAX - the maximum value
- MIN - the minimum value
- STDEV - the standard deviation of the set of values
Step 4: To add another column, click on the Add button beside the Values again and follow Step 3.
Summarize by month, year, or any other relevant time period
Using our existing Pivot Table from the previous section, we can change the date into months, quarters, or years by doing the following steps:
Step 5: Right-click on any cells in the column containing the dates. Click the Create pivot table date group. A drop-down list will appear containing options such as second, minute, hour, day of the week, month, quarter, and year. Click on your preferred group.
If you have a timestamp included in the same cell as the date, you can choose Second, Minute, and Hour. If you do so without the relevant data, Pivot Table will read it as all zeros.
If you select the Month, it will summarize the data by month, displaying it by name.
Separating the date and time to columns before sorting in Pivot Table
If for some reason, you have to separate the date to separate columns (one for the year, another for the month, and yet another one for the day), here are the steps:
Step 1: Separate the date into the year, month, and day by creating separate columns at the end of the sheet for each. Use the following functions for each column to extract the year, month, and day, respectively:
=YEAR(<cell containing="" the="" date="">)</cell>
=MONTH(<cell containing="" the="" date="">)</cell>
=DAY(<cell containing="" the="" date="">)</cell>
If your sheet contains the time of entry (which is important for certain purposes), you can add the following:
=HOUR(<cell containing="" the="" time="">)</cell>
=MINUTE(<cell containing="" the="" time="">)</cell>
=SECOND(<cell containing="" the="" time="">)</cell>
Our sheet will look like this:
Step 2: We click on any cell in the sheet that contains data, and then click on the Data option in the menu, then click on the Pivot table option in the drop-down list.
Make sure the Pivot table is created in a new sheet:
Step 3: The new sheet containing the Pivot table will be displayed, alongside the Pivot table editor. Look for the Add button beside the Rows. A list of columns in the original sheet shall be displayed, which includes the new columns we created for the year, month, and day. Let’s select the day.
Step 4: To add the data, scroll down the Pivot table editor and select the Add button beside the Values. A drop-down list will appear. Click on the quantity you want to include. By default, the Pivot table will calculate the sum of all the values.
To replace it, you can click on the box underneath Summarize by and choose other options. Some of them are:
- COUNT - counts the number of entries
- AVERAGE - calculates the average value of the entries
- MAX - the maximum value
- MIN - the minimum value
- STDEV - the standard deviation of the set of values
Step 5: To add another column, click on the Add button beside the Values again and follow Step 4.
For our example, the sum of total sales and profit are calculated for each day.
This will still work whether you choose a month or year instead. The disadvantage of this method, besides taking longer than the first one, is that months are displayed as numbers rather than names (as the months in the source sheet are displayed in numbers rather than names).
