Welcome to our guide on how to drill down a date column in a pivot table in Google Sheets. If you want to get additional levels of detail without the need to add extra columns to your data or create multiple pivot table reports, you’re in the right place!
Drill Down a Date Column in Pivot Table: Year > Quarter > Month
To illustrate the process, let’s use a sample sales data set. However, feel free to use your own data as long as it includes date and amount columns. That’s all you need to drill down the date field in a pivot table in Google Sheets.
There are three main steps involved:
1. Creating a Pivot Table
Start by selecting your data range (e.g., A3:F13). Then, go to the Insert menu and choose Pivot table. Check the “New Sheet” option and click “Create” on the pop-up that appears.
In the Rows field, add “Date.” You can also add “Description” to the Columns field (optional) and “Qty. (MT)” to the Values field. This will create a pivot table with dates row-wise and descriptions in columns, summing up the quantities.
At this stage, you won’t find an option to drill down the date column in the pivot table. Don’t worry, though, because the dates are already in the lower level of the hierarchy.
2. Defining Dimension Hierarchy
Next, decide the order of the hierarchy levels. For example, let’s follow Year > Quarter > Month, but you can choose other combinations like Year > Month, Quarter > Month, or Month > Day of the week.
Since we’re creating a three-level drill-down, we’ll have a year column, a quarter column, and a month column in our report. The grouping of dates will be done accordingly.
3. Drill Down Date Column as per Dimension Hierarchy in the Pivot Table
To start, right-click on any date in the pivot table and select “Create pivot date group” > “Year.” This will group the date column by year.
Open the pivot table editor by hovering over the pivot table and clicking on the Edit button at the bottom. Under the Rows field, add Date, which will add a new date column to your pivot table report.
Now, right-click any date in the new column and choose “Create pivot date group” > “Quarter.” Repeat these steps to add another level of granularity by right-clicking any date and selecting “Create pivot date group” > “Month.”
You can now drill down or drill up the date column in your Google Sheets Pivot Table. To test the drill-down detail, simply click the minus sign to collapse the group and the plus sign to expand it.
And there you have it! With these simple steps, you can easily drill down a date column in a pivot table in Google Sheets, revealing additional levels of detail without the need for extra columns or separate reports.
For more useful tips and tricks, visit Crawlan.com. And a big thank you to Shay for sharing this insightful tip with us!
