Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Basically, you start out with massive amounts of data and then how we’re going to analyze this. Well, let’s fire up the pivot table and see if we can solve this problem.
Hey welcome back to the Mr. Excel netcast. I’m Bill Jelen. Today’s question comes in from our Facebooker. So we got a Facebook and search for MrExcel.com. You’ll find our company page there. There’s a few discussion post. Hey, look at this we have 935 fans. Isn’t that cool?
So we come here and there’s some discussion threads and Cheryl posted a great question that I described in detail on the Facebook page but I want to show you how to do some Mac and Excel 2003 because Cheryl is in excel 2003. We’re going to create a pivot table from this data. Pivot table and click finish and we’ll apply the region, product and then let’s do our quantity, revenue, cost of good sale and profit.
Okay now, my suggestion to Cheryl was that she was going to record a macro so it’s going to make life easier. She talked about formatting these region totals and you might have a different number of intervening rows 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 the macros. So we’re going to go to pivot table, table options, and I’m going to call it “BillPT” for pivot table and click ‘OK’. All right, now when we record the macro, the recorded the macros always going to work on BillPT.
Now, here we go. Tools, macro, recorded macro, I’ll call it “FormatMyPivot”. Sure if you could ‘Ctrl’, ‘Shift’, ‘P’ stored in this workbook. Great. All right. The mac recorder is now running.
Now, here’s my tip for Cheryl. I said we start it out above the Pivot table. we see that light 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 a light plus to a black horizontal arrow? So light plus, to a black horizontal arrow. When it’s a black arrow, click and you’re going to select all of the region totals wherever they maybe. Now I got to play any formattings. So I want those region totals in green, bold, maybe 11 point font.
I also suggest that Cheryl should right click, table options, and uncheck ‘AutoFormat table’. She talked about how frustrating it is that the columns would change every time that she adds a new field. So, click ‘OK’ and now we will stop recording.
Okay. so, on another data, click delete. And we come in, we create our pivot table and it’ll be slightly different pivot this time. Data, pivot table, finish’s still going to put region on the left hand side but now I’m going to put ‘Customer’ in the region field and some different fields here, revenue, cost of good sold and profit. Drag the data to go across.
Okay. Here’s the big test. First thing we have to do, pivot table, table options, remember that name BillPT, and click ‘OK’. Now we should be able to run the macro which is going to operate on the pivot table called BillPT. It should go through to the region totals and format those. So let’s run all of it and let’s call it FormatMyPivot. Click run.
Check it out. Even thought there are four more rows here with customer instead of product, it was smart enough to go through and find all the region totals and format those. A 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 Mr. Excel.
Well, thanks for stopping by. We’ll see you next time for another netcast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services