Using date and time as metrics in a PivotTable

- One of the PivotTable's greatest strengths is its ability to analyze information by date and time. In this worksheet, here, we're showing sales data, transaction data, over a two year period and it's identified by the date in which it occured and the time of day. We might want to analyze the information based on either or both of those criteria there. Let's create a PivotTable on this worksheet. We can go to the Insert tab on the ribbon. Select PivotTable, choose Existing Worksheet, click on the Location panel below it and I'll simply click on cell H1 in the background, click OK.

The PivotTable Fields list, I'm going to shrink it a bit and by the way, you can make this be docked by simply double-clicking, if it's already docked, and you'd like to move it out from the docking position, simply grab its title bar and move it out. I usually prefer have it undocked or floating. Now, let's imagine this PivotTable we'd like to see, at least initially, is a, say the date in which a particular sale occurs, so I'll choose Date first. This automatically goes into the Row Labels area down the left hand side. And I think almost immediately, most people would say, "Wow, that's probably not what I'm aiming for here." Let's drag in, also, the Amount here, the dollar amount.

Or possibly Units, either of these. I'll drag in Units as a values entry. Could've checked the box there but automatically appear here anyway. So, as we see this, not too promising. Maybe we'll bring in Region, also, to make it a little more interesting for Column Labels. But, the big thrust of this is that this is not very compact, this is not what we would be hoping for in analyzing this data. But I'm going to right-click one of the dates. And choose Group. There's months, quarters, years, now you don't need to control key here but you can use two of these, or three of these, or any two, or all three, or any one, I'm simply going to use all three.

Let's group this data by months, quarters, and years. Click OK. Not bad. If we don't want to see Column Labels and Row Labels, I think that would help, by the way, this isn't what we call a compact form. It takes up so much horizontal space, but if we make a change here and don't use compact form, the advantage will be we'll see real field names here. It will, however, make this report slightly wider. On the Design tab, Report Layout is where we make this choice, instead of Compact which is the default, I'm going to choose Tabular.

Now it's a bit wider but I think it pulls out the data, displays the data, the layout is easier to comprehend now. And we see what's happened here. We've got the data by groups, here's the 2013 data, here's the 2014 data below it, and within that, we see it grouped by quarters and months. Recognize within the PivotTable Fields list, these are all Row Label entries. And now, bit of analysis could be acheived simply by moving some of these fields back and forth, in and out of the way by putting them into the filters area of the PivotTable Field list here.

So for example, maybe I want to make this list more compact. I'm thinking the information about the months is, in fact, critical. Now recognize that we've got years and quarters and this says date, and yet, what's below it are months. Date is the name of the field in the source data off to the left. We see that in Column D. So I'm simply going to type over this and call it Months. That's not in conflict with our source data, although it's certainly different, but it's more useful within the PivotTable to see this term.

And what do we see in the PivotTable Fields list? Months! So it's gonna make some sense here. I'm going to drag Months temporarily out of the PivotTable and put it into the filters area. And now we see a more compact list. Compact isn't always better but let's say that, maybe for certain kinds of presentations, it's what you want it here. Another small adjustment here, right-click any of the values within here and choose Number Format, let's make sure we have commas usually under the category Number, that would be a good choice. Use the thousand separator and no decimals.

A bit easier to read now. Now, some of the layouts might seem a little unorthodox but, yet, how about this idea, you'd like to see the two Quarter 1 entries together. So, within the Row Labels area, with the PivotTable Fields list, I'm going to drag Years and put it below Quarters. And that changes the order here. Now, we are seeing are two Quarter 1 entries right next to one another. And you can imagine doing the same kind of thing with Years and Months, so I'm going to move Quarters out of the Row Labels area into filters and then bring Months back in and put that above Years.

And now we're seeing, for example, are two January entries together. We might also, along the way, see subtitles we can easily get rid of those. Sometimes you want them, sometimes you don't. Design tab, Subtitles, Do Not Show Subtitles. Now we're seeing on month-by-month basis the two different years together. So there's the February 2013 and 2014 data contrasted. Same thing with May down here and so on. So, moving these fields back and forth from the Row Labels area into filters to get different kinds of layouts can be very worthwhile.

Not every single layout's gonna be valuable but certainly some of these are gonna be useful as we move these back and forth. Now, we could also analyze data by time. Instead of seeing years, quarters, months, let's simply move these out of the way. We might want to use them again soon but let's leave them out, put them out of the way by dragging them into the filters area. And let's bring in Time. I'm simply gonna check the box for Time. Watch the data to the left. And that's probably not what you were thinking and hoping and that's way too much detail, also, like, just like what we saw with dates.

But I'm going to right-click one of these and Group. And group these by hours, not by months. And that makes some sense there. And we can see at a glance which hour of the day, there it is, 3 PM, bigger than the others. All through this, too, and even now we can do it, we can press alt F1 to get a chart. See the data this way. With PivotCharts, I strongly suggest go to the Design tab in the ribbon and switch this into, by way the choice called Change Chart Type, switch it to a Stacked Column.

Nearly always these work best with PivotTables because they make them simpler and easier to read. They're not always the best chart for all occasions but if you're working with PivotTables, I think these are gonna be your best choice. So we can see clearly, here, and in the visual really helps a lot here. The 3 PM hour really jumps out. It's quite a bit higher than the others. 6 PM looks like it's second highest, that could be a surprise as we analyze this data here. And if we click within the data here and I'll make a quick change back to Dates again, by simply dragging Time off the Rows grid and bringing back Years and quarters and months.

As we do this you see what's happening in the chart as well. At some point you'll decide maybe that chart's a bit too busy. But we can see how beautifully this works both with date and time entries. And working within PivotTables, we can analyze the data based on date or time.