From the course: Excel: PivotTables in Depth

Manage dates in a PivotTable

From the course: Excel: PivotTables in Depth

Start my 1-month free trial Buy for my team

Manage dates in a PivotTable

“

- [Instructor] One of the most important facts that a business records is when something happened. In this movie, I will show you how Excel manages dates in your pivot tables. You'll find that you can break dates down by year, quarter, and month using just the dates from your original data source. My sample file is 02_04_dates. And you can find it in the Chapter 2 folder of the exercise files collection. I've actually started on the sales data sheet. And here one of my columns is OrderDate. So I have dates, and these are in the US system. So a sale on 1/2/2022 would be January 2nd, 2022. Now, I'm going to switch over to sheet one, which has my pivot table. And we'll see how adding a date field to the rows area changes its organization. So I'll go over to the PivotTable Fields task pane, find the OrderDate field, and drag it down to the rows area. A couple of things happened. I'll go to the pivot table first. Here I have row labels for the year 2022 and 2023 and summaries of the data for those two years. In the PivotTable Fields task pain, you can see that I have subfields for years, quarters, and months of OrderDate, and then OrderDate itself. So those will be the individual dates. We can use the show and hide detail controls in the pivot table to go to a lower level of organization. So if I go to 2023 and click the show detail button next to it, it looks like a plus sign, then I see each of the quarters worth of data. And if I click the show detail button next quarter one, I get the individual months. And if I click next to a month, then I get the individual days. So I can drill down to my lowest level of organization, which is the day. And I'll click the hide detail control, it looks like a minus sign to go back up, until I just see quarters for 2023. If I want to see individual dates as opposed to these larger levels of organization, then I can go to the PivotTable Analyze contextual tab, and then in the group area click Ungroup. And I get each of my datasets broken down by day. So even if there are multiple orders per day, and you can see that there are, I just see a single row label for January 1st, January 2nd, January 3rd, and so on throughout the rest of the data. And if I press Ctrl + Z, then the grouping returns. Excel pivot tables handle dates well. The built-in date hierarchy lets you analyze by year, quarter, and month without having to do any extra work.

Contents