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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Create Date Hierarchy in Excel Pivot Table: 7 Quick Steps

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.

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


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.

Read More: How to Add Row Hierarchy in Excel


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


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.

Download Practice Workbook

Download this dataset and practice while going through this article.


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.


Related Articles


<< Go Back to Hierarchy in Excel | SmartArt in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo