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.
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.
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.
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.
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.
Step 5: Create PivotTable
The next step is to create a pivot table with the dataset. To do so,
- Select PivotTable.
- Create PivotTable box will appear. Select New Worksheet.
- Then, press OK.
- Excel will create a PivotTable.
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.
- 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.
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.
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.