Welcome to Excel Basics video number 22.
In this video, we've got to see how
to set the default pivot table layout and options in Excel
2016, Office 365, or later.
Now, edit default layout in a pivot table--
that is amazing.
We've created 20 pivot tables in the class so far,
and every time, we had to change the report layout
to tabular form manually.
So this is going to be an amazing time saver.
Now, why didn't we see this earlier in the class?
Because we don't have this option in our computer labs.
And right now, it's only if you bought the Office 365
version of Excel 2016.
Now, the great news is that this feature
will be available in all future versions.
So just in case you have Office 365 or just in case
you're going to be using Excel for years to come,
let's see how to use this amazing feature.
Here's a proper data set.
I'm going to create a quick pivot table using our keyboard,
Alt-N-V. I'm going to put this on the existing sheet location,
G6, click OK.
From the field list, I drag Region
to Rows, Sales to Values.
And there it is--
the entire class.
We have to change row labels and then later column labels
by going to Design over to Layout, Report Layout,
Show in Tabular.
Now, when I click that, now I get the field names.
Now, when we go and look at this option in just a moment,
all of these settings are available.
We can set whether or not we want blank rows.
We could actually insert a blank line after each item.
We can do any of these, including repeat all item
labels.
Now, two videos ago, we saw a Country Product Category pivot
table where it listed country only one time.
But if we always wanted to repeat them,
we could select that.
We have options over grand totals and subtotals.
All of these are available.
Now, another thing that's available in this default
setting--
right click the Pivot table.
And Pivot table has a bunch of options you can set.
In layout and format, you can change things
like auto fit column width on update.
So as you look through these, if there's some of them
that you want to set as a default, you can do that, too.
I'm going to click Escape.
Now notice I have my cursor in the pivot table
and I've changed one of the settings.
Now, here's what we do.
We go up to File, down to Options.
That opens the Excel Options Dialog box.
We go down on the left to Data.
Over here, Default Layout for Pivot Tables,
we click Edit Default Layout.
And there it is.
We've been waiting for this dialog box for years.
And finally, Microsoft gives it to us.
Now, what's also amazing is the first text
boxes lay out import.
So whatever settings for options and layout
I had in this pivot table, I could simply click Import,
and it would change them.
Notice the default is compact.
When I click Import, it changes that one.
Now, you can go through each one of these for Subtotals, Grand
Total, Report Layout, Repeat All Items, Insert Blank
Rows, and even Pivot Table Options and change those.
But once you click OK, those are now the defaults.
Click OK.
I come back to the data set.
Alt-N-V. I'm going to put it on this existing sheet.
I'm going to select G13.
Click OK.
Now when I drag Region down to row sales, down to values,
that is absolutely beautiful.
Now, that was just a quick Excel basics.
So we know that you either have this feature with Office 365
or we know we'll be getting it soon.
All right.
We'll see you next Excel Basics video.