Join Dennis Taylor for an in-depth discussion in this video Adjusting default layouts and date grouping in PivotTables, part of Excel Tips Weekly.
- [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.
Author
Updated
3/2/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 32m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Adjusting default layouts and date grouping in PivotTables