Laura from Nashville is looking for a way to make sure that all future pivot tables default to having No Subtotals. This *was* a feature in the PowerPivot Add-in back in Excel 2010, but then was removed. However, thanks to Pivot Table Defaults, you can force all future pivot tables into a flat pivot table that is ready for re-use.
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2504. Is our way to default to a Flattened Pivot Table with no subtotals by default.
Today's question from Laura in my Nashville webinar.
"How can I make my pivot tables default to have no subtotals"?
Now, look, I'm going to blame this on Rob Collie.
Back when Rob was on the PowerPivot team, they in the PowerPivot window gave us this PivotTable drop-down where we could create a single PivotTable, single pivot chart, several other options.
And then right there, the last one was a Flattened Pivot Table.
Then Rob left Microsoft, and soon those eight choices were just replaced with PivotTable.
It had to be when Rob's influence left. I know how much he loves Flattened Pivot Tables.
It just disappeared. So we had a feature.
It was there, disappeared, but now I realize it's back, thanks to PivotTable Default.
So, let's take a look at Laura's question. We're going to insert a PivotTable.
Okay.
We're going to put Sector, Customer, Product along the left hand side, Revenue, Cost Of Goods Sold, and Profit in the Values area.
And you see that we're getting an AT&T Subtotal. We're getting Communications Subtotal.
All of those totals are happening automatically.
So if you're a person who doesn't want those subtotals and just wants a nice, flat PivotTable, we can now have control of that.
Do you have the feature?
Well, it wasn't there in Excel 2016, but certainly by Excel 2019 or Microsoft 365. Go to file options.
If you have this data category, then you definitely have the button.
Make changes to the default layout of PivotTables, edit default layout.
And what we're going to do is we're going to change from the Compact form to Tabular form.
Under subtotals, we're going to say no subtotals.
Do not show subtotals. And also, Repeat All Item Labels.
Grand totals, we can even turn those off. So off for rows and columns, and click okay.
Now, all future PivotTables should start out in a nice, flat PivotTable.
Insert PivotTable. Okay.
And again, we'll choose Sector, Customer, Product, Revenue, Cost Of Goods Sold and Profit.
And look at that. What a beautiful data set, ready for reuse.
Summarize the data down to one line for each combination of sector, customer, product.
Summed everything up for us.
There's no subtotals to get in the way, none of this crazy Product, Customer, Sector, all in column A.
Who would ever want something like that. And so that is a great way to go.
I loved when Laura asked that question because I've certainly never included this in any of my books.
But since 2010, when we talked about a Flattened Pivot Table in PowerPivot, but I realized with the PivotTable Defaults now, we basically can make all future pivot tables start in this awesome Flattened PivotTable.
Well, hey, I want to thank Laura for asking that question.
I want to thank the Nashville IMA for sponsoring our webcast and I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
Today's question from Laura in my Nashville webinar.
"How can I make my pivot tables default to have no subtotals"?
Now, look, I'm going to blame this on Rob Collie.
Back when Rob was on the PowerPivot team, they in the PowerPivot window gave us this PivotTable drop-down where we could create a single PivotTable, single pivot chart, several other options.
And then right there, the last one was a Flattened Pivot Table.
Then Rob left Microsoft, and soon those eight choices were just replaced with PivotTable.
It had to be when Rob's influence left. I know how much he loves Flattened Pivot Tables.
It just disappeared. So we had a feature.
It was there, disappeared, but now I realize it's back, thanks to PivotTable Default.
So, let's take a look at Laura's question. We're going to insert a PivotTable.
Okay.
We're going to put Sector, Customer, Product along the left hand side, Revenue, Cost Of Goods Sold, and Profit in the Values area.
And you see that we're getting an AT&T Subtotal. We're getting Communications Subtotal.
All of those totals are happening automatically.
So if you're a person who doesn't want those subtotals and just wants a nice, flat PivotTable, we can now have control of that.
Do you have the feature?
Well, it wasn't there in Excel 2016, but certainly by Excel 2019 or Microsoft 365. Go to file options.
If you have this data category, then you definitely have the button.
Make changes to the default layout of PivotTables, edit default layout.
And what we're going to do is we're going to change from the Compact form to Tabular form.
Under subtotals, we're going to say no subtotals.
Do not show subtotals. And also, Repeat All Item Labels.
Grand totals, we can even turn those off. So off for rows and columns, and click okay.
Now, all future PivotTables should start out in a nice, flat PivotTable.
Insert PivotTable. Okay.
And again, we'll choose Sector, Customer, Product, Revenue, Cost Of Goods Sold and Profit.
And look at that. What a beautiful data set, ready for reuse.
Summarize the data down to one line for each combination of sector, customer, product.
Summed everything up for us.
There's no subtotals to get in the way, none of this crazy Product, Customer, Sector, all in column A.
Who would ever want something like that. And so that is a great way to go.
I loved when Laura asked that question because I've certainly never included this in any of my books.
But since 2010, when we talked about a Flattened Pivot Table in PowerPivot, but I realized with the PivotTable Defaults now, we basically can make all future pivot tables start in this awesome Flattened PivotTable.
Well, hey, I want to thank Laura for asking that question.
I want to thank the Nashville IMA for sponsoring our webcast and I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
