Create Date Hierarchy in Excel Pivot Table (with Easy Steps)

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Excel is useful to create a date hierarchy. We can use PivotTable to do that. In this article, I will show you how to create date hierarchy in Excel Pivot Table with easy steps.


Download Practice Workbook

Download this dataset and practice while going through this article.


7 Quick Steps to Create Date Hierarchy in Excel Pivot Table

This is the dataset for today’s article. We have some dates and amount of sales on each day. I will use this dataset to illustrate how to create date hierarchy in Excel Pivot Table.

how to create date hierarchy in excel pivot table


Step 1: Add Dataset to Data Model

The first task is to add dataset to the Data Model. To do so,

  • Select the entire dataset B4:E12.
  • Then, go to the Power Pivot
  • After that, select Add to Data Model.

  • Excel will add the dataset to the data model.

how to create date hierarchy in excel pivot table


Step 2: Activate Diagram View

The next step is to activate Diagram View. To do so,

  • Select Diagram View from the Data Model

  • Excel will activate the Diagram View.

how to create date hierarchy in excel pivot table


Step 3: Select Column to Create Hierarchy

Now, I will illustrate how to select a column and create a hierarchy. To do so,

  • Select the column Year.
  • Then, right-click your mouse.
  • After that, select Create Hierarchy.

  • Excel will create a hierarchy.
  • Rename as you wish. I am going to call it Date Hierarchy.

how to create date hierarchy in excel pivot table


Step 4: Create Child Hierarchy Level

Now, we need to create a child hierarchy level. The Month and Date column will be the child levels in this case.

  • Drag them one by one within the parent hierarchy level.

Read More: How to Create Multi Level Hierarchy in Excel (2 Easy Ways)


Step 5: Create PivotTable

The next step is to create a pivot table with the dataset. To do so,

  • Select PivotTable.

how to create date hierarchy in excel pivot table

  • Create PivotTable box will appear. Select New Worksheet.
  • Then, press OK.

  • Excel will create a PivotTable.

how to create date hierarchy in excel pivot table


Step 6: Edit PivotTable Fields

Our final step is to edit the PivotTable Fields. I will show you how to do so.

  • Drag Date Hierarchy to Rows level and Sales to Values. You will find the Sales column in the More Fields

  • This will be the output. To see the hierarchy, click the + icon.

how to create date hierarchy in excel pivot table

  • Excel will show you the hierarchy.


Step 7: Format PivotTable

Finally, format the PivotTable the way you want. I am going to format it this way.

how to create date hierarchy in excel pivot table

Read More: How to Create Hierarchy in Excel Pivot Table (with Easy Steps)


Things to Remember

  • You must activate the Power Pivot add-in for this task.
  • You can change the positions of the columns in the hierarchy.

Conclusion

In this article, I have explained how to create date hierarchy in Excel Pivot Table. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit ExcelDemy for more useful articles like this.


Related Articles

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo