Cheryl asks how to format all new pivot tables that she creates. The macro recorder can handle this, if you change the name of the pivot table before recording the macro. Episode 1027 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Basically, we start out with massive amount of data.
How we're gonna analyze as well plus file up a pivot table.
Let's see if we can solve this problem.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
And today's question comes in from our Facebook group.
So, if you got a Facebook and search for MrExcel.com.
You'll find our Company page there.
There's like few discussion post.
Hey, look at this we have 935 fans.
Isn't that cool?
So, we can come here.
And there's some discussion threads and Sheryl posted a great question that I described in detail on the Facebook page.
But I want to show you how to do this.
I'm back in Excel 2003 because Sheryl is in Excel 2003.
We are going to create a pivot table from this data Pivot Table and Click Finish.
and we'll apply a region and product, and then let's do quantity revenue cost of goods sold in profit.
Okay! Now, my suggestion to show was that she was going to record a macro.
It's going to make life easier.
She talked about formatting these region totals.
And you might have a different number of interviewing rows here.
So, you can't just copy the formatting from the old pivot table.
Now, before you record a macro that's going to operate on a pivot table.
You need to change the name of the pivot table that cannot be in the macro.
So, we're going to go to Pivot Table >Table Options.
And I'm gonna call this BillPT for pivot table Click OK.
Alright! Now, when we record the macro.
The record macro is always going to work on BillPT.
Now, here we go Tools>Macro> Record New Macro.
I'll call it FormatMyPivot.
Shortcut key CTRL+SHIFT+P stored in this workbook.
Great!
Alright! The macro recorder is now running.
Now, here's my tip for Sheryl.
I said we started, out above the pivot table.
We see that white plus sign there.
But, I want to go to the very first Region Total and then to the left edge of that Region Total.
So, right there bam!
See a change from white plus to a black horizontal arrow.
So, white plus black horizontal.
What is a black arrow?
Click and you're going to select all of the Region Totals wherever they may be.
Now, I can apply any formatting.
So, I want those region totals and green, bold maybe 11 point Font I also suggested Sheryl should right-click Table Options and uncheck Auto format table.
She talked about how frustrating is that the columns that's changed every time that she adds a New field.
So, Click OK.
And now, we will stop recording.
Okay! So, on another day Click Delete.
And we come in we create a pivot table.
It'll be a slightly different pivot table this time.
Data>Pivot Table>Finish.
Still going to put regional and left-hand side, but now, I'm going to put Customer.
Customer in the Region Field add some different fields here revenue cost of goods sold.
And Profit, drag the data to go across.
Ok!
Here's the big test.
First thing we have to do, Pivot table>table options.
Remember that name BillPT.
Click OK.
Now we should go run the macro which is going to operate on the Pivot table called BillPT.
It should go through to the region tables and format those.
So, let's run all of 8.
And it's called Format my pivot.
Click Run.
Check it out even though there are far more rows here with customer into that a product.
It was smart enough to go through and find all the region totals and format those Very very cool way to go.
Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Basically, we start out with massive amount of data.
How we're gonna analyze as well plus file up a pivot table.
Let's see if we can solve this problem.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
And today's question comes in from our Facebook group.
So, if you got a Facebook and search for MrExcel.com.
You'll find our Company page there.
There's like few discussion post.
Hey, look at this we have 935 fans.
Isn't that cool?
So, we can come here.
And there's some discussion threads and Sheryl posted a great question that I described in detail on the Facebook page.
But I want to show you how to do this.
I'm back in Excel 2003 because Sheryl is in Excel 2003.
We are going to create a pivot table from this data Pivot Table and Click Finish.
and we'll apply a region and product, and then let's do quantity revenue cost of goods sold in profit.
Okay! Now, my suggestion to show was that she was going to record a macro.
It's going to make life easier.
She talked about formatting these region totals.
And you might have a different number of interviewing rows here.
So, you can't just copy the formatting from the old pivot table.
Now, before you record a macro that's going to operate on a pivot table.
You need to change the name of the pivot table that cannot be in the macro.
So, we're going to go to Pivot Table >Table Options.
And I'm gonna call this BillPT for pivot table Click OK.
Alright! Now, when we record the macro.
The record macro is always going to work on BillPT.
Now, here we go Tools>Macro> Record New Macro.
I'll call it FormatMyPivot.
Shortcut key CTRL+SHIFT+P stored in this workbook.
Great!
Alright! The macro recorder is now running.
Now, here's my tip for Sheryl.
I said we started, out above the pivot table.
We see that white plus sign there.
But, I want to go to the very first Region Total and then to the left edge of that Region Total.
So, right there bam!
See a change from white plus to a black horizontal arrow.
So, white plus black horizontal.
What is a black arrow?
Click and you're going to select all of the Region Totals wherever they may be.
Now, I can apply any formatting.
So, I want those region totals and green, bold maybe 11 point Font I also suggested Sheryl should right-click Table Options and uncheck Auto format table.
She talked about how frustrating is that the columns that's changed every time that she adds a New field.
So, Click OK.
And now, we will stop recording.
Okay! So, on another day Click Delete.
And we come in we create a pivot table.
It'll be a slightly different pivot table this time.
Data>Pivot Table>Finish.
Still going to put regional and left-hand side, but now, I'm going to put Customer.
Customer in the Region Field add some different fields here revenue cost of goods sold.
And Profit, drag the data to go across.
Ok!
Here's the big test.
First thing we have to do, Pivot table>table options.
Remember that name BillPT.
Click OK.
Now we should go run the macro which is going to operate on the Pivot table called BillPT.
It should go through to the region tables and format those.
So, let's run all of 8.
And it's called Format my pivot.
Click Run.
Check it out even though there are far more rows here with customer into that a product.
It was smart enough to go through and find all the region totals and format those Very very cool way to go.
Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
