This post describes drilling down a pivot table date column (field) in Google Sheets to get additional levels of detail.

For example, consider your credit card or bank statement for the last two financial years. You want a yearly, quarterly, and monthly summary view of it.

You don’t require to add a year, quarter, or month column to your data or create three pivot table reports for date field data hierarchies.

The existing date column in your credit card statement is sufficient.

The Pivot table can do the grouping with additional levels without adding a year, quarter, or month column in your source data.

Let’s see how to drill-down and drill-up a date column in the Pivot table in Google Sheets.

Thanks to Shay for sharing this tip with me.

Drill Down a Date Column in Pivot Table: Year > Quarter > Month

I am using the following mockup sales data as a sample to create our report.

You don’t require to use this sales data. You can use your available data (copy) for the purpose. But the data must contain the date and amount columns.

That will be enough to drill-down date field in the Pivot table in Google Sheets.

There are a total of three main steps involved in this.

1. Creating a Pivot table.
2. Defining Dimension Hierarchy.
3. Drill-down date column as per the data hierarchy in the Pivot table.

1. Creating a Pivot Table

Select your data range, i.e., A3:F13, and go to the Insert menu and select Pivot table.

Check “New Sheet” and click the “Create” button on the pop-up that appears.

Add “Date” to the Rows field, “Description” to the Columns field (optional), and “Qty. (MT)” to the Values field.

The above settings will create a Pivot table with dates row-wise and descriptions in columns. It sums the quantities as below.

There is no option to drill-down the date column in the above Pivot table in Google Sheets.

It’s not necessary, though. Because the dates in this report are already in the lower level of the hierarchy.

Grouping the date column is essential to enable drill-down detail in Pivot Table in Google Sheets. How to do that?

2. Defining Dimension Hierarchy

First, decide the order of the levels of the hierarchy. In the example, I will follow Year > Quarter > Month. We will define that while grouping dates.

Other proposed levels are Year > Month, Quarter > Month, and Month > Day of the week.

We are creating a three-level drill-down. So we will have a year column, a quarter column, and a month column in our report.

So we should first group the dates by year, then by quarter, and finally by month.

3. Drill Down Date Column as per Dimension Hierarchy in the Pivot Table

The first thing we should do on the Pivot table is group the date column by year.

I’ve already written two detailed tutorials on this.

• Month Wise Pivot Table Report in Google Sheets Using Date Column.
• Group Dates in Pivot Table in Google Sheets (Month, Quarter, and Year).

You can skip reading them right now.

Right-click on any date and select Create pivot date group > Year. Please see the image below.

Then open the Pivot table editor. How?

You can open it by hovering your mouse pointer over the Pivot table and clicking on the Edit button that appears at the bottom of the table.

Under the Rows field, add Date.

It will instantly add a new date column in your Pivot table report.

Right-click any date on that column, and choose Create pivot date group > Quarter.

Repeat the last step once again. I mean to say, add Date to the Rows field within the Pivot table editor.

Right-click on any date and select Create pivot date group > Month.

The output will be as follows.

You can now drill-down or drill-up the date column in your Google Sheets Pivot Table.

If you want, you can add one more level. That’s to show the Day of the week. But I’ve omitted that in my example.

To test the drill-down detail in Pivot Table, please do as follows;

Click the minus to collapse the group and the plus (that subsequently appears) to expand the group. That’s all.