All right, hey welcome back its where’s that Wednesday. Thanks to George Wood for that thing. How much where I have to thanks George Wood because George Wood writes the theme for every day now, but still thanks to George Wood.
We’ve been talking this week about how we match up data from two different worksheets that have one key income, customer and forecast. I also have customer order I added some new columns here cost and gross profit. I like to marinate this all of these up on Monday we talked about the double look up method, yesterday we talked about using a pivot table, and today I want to talk about a different way to use the pivot table, but this feature at least apparently has been removed from Excel 2007. Let me show you what I'm talking about back in your Excel 2003 when we go to data, pivot table.
Now most people would just click finish in the wizard, but there were other choices here included something called multiple consolidations I just love. This is obscure I probably get to use about twice a year, but it really helps to solve this problem. And the problem we have is it seems that it's gone in Excel 2007. I mean, take a look go to insert pivot table. There's nothing there for multiple consolidation art. What the heck? I mean it was there before. Let’s see if it’s in the drop down pivot table, pivot chart now of course not.
So how do we get to any command that used to be in the old Excel well think about it? The old accelerator key start works here in Excel 2003 that D was underlined. D would open the data and you and then about the P will do a pivot table and here is chart report. Pretty wild, if we come here to Excel 2007 and you Alt D, P it brings back the old wizard. Yes, it's look like the wizard that had been there it just not really banished which just removed from the ribbon so alt D, P gets it back to your old wizard. And check some artists that Microsoft actually redesign the dialogue box, got to do some new artwork there, and then it was removed the ribbon. I hate to be that person, but more of the consolidation, which is okay.
So let me show you how we can use multiple consolidations I just to solve this problem which is to use multiple consolidation we’re in just step one of three. Click next and now we’re in step two a —hey, it’s your like they're saying, “We don't know how many steps that are going to be.” Say, I'm going to create the page fields, click next and now we’re going to specify our ranges one at a time.
So the first range, here on orders worksheet. A1 to D21 press add and I want to add a second range, so click on the forecast sheet and specify my data there and click add. I don’t want any page fields at all so I click actually at this point I can click finish. And I get a brand new worksheet. All of the customers that are on either sheet either sheet one or sheet two shows up on the left hand side. All of the columns that are on either sheet show over cross the top.
Now they put these in alphabetical order so cost shows first that's not how I want this at all. This is a pretty cool feature in pivot tables I can take that forecast heading, and go to the edge of it and drag it over at column B, and I can rearrange it. I want orders to show up next again I can take that field and drag it over the column C, costs, gross profit that's okay. Just like I did yesterday we will go on to pivot table options. Fill the empty cells to zero, and get rid of the grand total for rows, and we solved this problem.
The advantage here is we didn't have to copy the data on to the same worksheet. Remember yesterday I had to take the orders and copy them below the forecast and add that source field not necessary to do it all here, using the multiple consolidation arranges featured at the pivot table, allowed us to solve this problem.
Unfortunately, it's a cool feature that we just can't find anymore. It's under Alt D and then P alternatively. There is by the way, an icon that you can add if you’re going to customized your quick access toolbar it will bring back the pivot table wizard at all easy enough just to do alt D for data P for pivot table. And it get back to that nicely, redesign dialogue box that they removed from the ribbon go for here.
I want to thank for stopping by we’ll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services