Adjusting default layouts and date grouping in PivotTables

- [Instructor] When you create PivotTables, sometimes the way Excel displays dates might not be to your liking. And there was a change started in Excel 2016 which carries into additional versions where you might want to change what we call a default setting. I've got a list of data here about 900 rows, I'm gonna create a PivotTable. There are no empty rows within the list, I've scoped it out. I'll go to the Insert tab, choose PivotTable. I want this on a separate sheet, that's the default setting anyway. I'll simply click OK, and then we are on a separate sheet.

I wanna track items, so I'll click that box first, and immediately that appears on a very tiny PivotTable over in column A. But I wanna track this by region, I'm gonna put that as a column heading. And then the date entries, I want to appear as a rows label in Excel 2016 and later, here's how it appears. Just two rows there, and that might be just fine. You can expand these by either clicking the plus or double-clicking. I'll first zoom in here holding down control and the mouse wheel, make it a little bit larger.

There we go, double-click the 16 or you click the plus, same thing with 2017, same idea. We could expand this completely if we wanted to. We could ungroup, I'm gonna choose Ungroup up here. And that's what happens. This is the way things looked in earlier versions of Excel if you chose date in the rows area. This is what you see date after date after date. Now if you prefer this, you can disable the grouping that we saw earlier. There's another option that you might want to change as well. When you create a PivotTable, unless you make an adjustment, you will see the terms Row Labels and Column Labels here.

If you're about to make a presentation of this, that's not exactly helpful to others. So how do we get rid of them and put in the actual field names? On the Design tab when you're working with PivotTables, under Report Layout, you wouldn't know necessarily, but it is the case, this is in compact form. If you prefer to see field names, you wanna choose outline or tabular. And how do we make this the default setting? In the same way that we're gonna be changing the dates. Now we see the actual field names in place here.

But let's say from now on, when we create PivotTables, we automatically want our dates not to be grouped and we want to see these field names here by way of that design option here on the report tab called tabular. So, File tab on the ribbon, down to Options. The Excel Options dialogue box here down the left-hand side, choose Data, and under Data options, the last entry here, "Disable automatic grouping "of Date/Time columns in PivotTables." And I'm not necessarily saying this is what you want to do, but you might want to do it, so I'm gonna make the change here, if you don't like that grouping that happens automatically.

So we're disabling that. And at the top, "Make changes "to the default layout of PivotTables." Edit Default Layout. We could be choosing under Report Layout here, Tabular Form. From now on when we create PivotTables, that's going to be the default layout. So let's say we click OK, and OK. We don't need this worksheet here anymore, I'm gonna delete it. We're gonna create another PivotTable. Now, sometimes you need to save the workbook, I'm gonna do that now.

Sometimes not, I've seen it go both ways, but let's say we save the workbook. Let's imagine it's lighter. We created a PivotTable, Insert, PivotTable, automatically it'll put it on a new worksheet, click OK. This time after checking the box for Items and dragging Region into Columns, this time when I click Date what happens? There's no grouping of the dates at all. And once again, I'm not necessarily saying that's preferable, but just saying that if you want that, you'd make that change. And notice how we're seeing Date and Region here, the actual field names, instead of the terms Row Labels and Column Labels.

So the changes that we just made, something you might want to consider if you're using PivotTables, once again are found on the File tab under Options, and then Data, and the change here for automatic grouping of date/time, and up above for Edit Default Layout. Two changes that PivotTable users might want to consider making.