The Consolidate command is versatile. In Episode 832, see how to use Consolidate to summarize similar data that occurs on multiple worksheets.
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'm Bill Jelen.
Well, yesterday, I showed one way to use data consolidate basically to kind of replace a pivot table summary, just to do one line per customer, but there's the completely different thing.
We have a spreadsheet here with 4 different worksheets, one for each quarter – so, we have Q1, Q2, Q3, Q4 -- and I'd like to summarize all four of those worksheets.
The consolidate command can create a great summary worksheet here.
I'll insert a blank worksheet, call it SUMMARY, and I'll go to cell A3.
That’s where I want the consolidation to start.
We'll go to DATA, and then COSOLIDATE, and, for the first sheet, it's a little bit tougher.
I'm going to press the refers to box, go to Q1, and I'm going to specify the range that includes my data, and I'll click ADD, but then, for the other sheets, it's fairly easy.
Let me rearrange this here.
When I click on Q2, you see that it keeps the exact same range on Q2, so I can just click ADD, click on Q3, click ADD, click on Q4, click ADD, and then, in the lower section, we want to USE LABELS IN TOP ROW LEFT COLUMN, and CREATE LINKS TO THE SOURCE DATA.
That way, if anything changes on the back sheets, the summary sheet will change as well.
When we click OK, we get a pretty cool-looking summary here with SALES, COST OF GOODS SOLD, EXPENSES, and INCOME, but it's even a little bit better than that.
You'll see that there's some hidden rows.
If we use the group and outline button, so I press the number 2 button, it actually shows me the numbers from each spreadsheet, build a great little summary there that's expandable using data consolidate.
Great way to set that up.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, yesterday, I showed one way to use data consolidate basically to kind of replace a pivot table summary, just to do one line per customer, but there's the completely different thing.
We have a spreadsheet here with 4 different worksheets, one for each quarter – so, we have Q1, Q2, Q3, Q4 -- and I'd like to summarize all four of those worksheets.
The consolidate command can create a great summary worksheet here.
I'll insert a blank worksheet, call it SUMMARY, and I'll go to cell A3.
That’s where I want the consolidation to start.
We'll go to DATA, and then COSOLIDATE, and, for the first sheet, it's a little bit tougher.
I'm going to press the refers to box, go to Q1, and I'm going to specify the range that includes my data, and I'll click ADD, but then, for the other sheets, it's fairly easy.
Let me rearrange this here.
When I click on Q2, you see that it keeps the exact same range on Q2, so I can just click ADD, click on Q3, click ADD, click on Q4, click ADD, and then, in the lower section, we want to USE LABELS IN TOP ROW LEFT COLUMN, and CREATE LINKS TO THE SOURCE DATA.
That way, if anything changes on the back sheets, the summary sheet will change as well.
When we click OK, we get a pretty cool-looking summary here with SALES, COST OF GOODS SOLD, EXPENSES, and INCOME, but it's even a little bit better than that.
You'll see that there's some hidden rows.
If we use the group and outline button, so I press the number 2 button, it actually shows me the numbers from each spreadsheet, build a great little summary there that's expandable using data consolidate.
Great way to set that up.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
