During a recent seminar, someone asked me to show the audience how to use the Consolidate command. I launched into the technique shown in Episode 831 - using consolidate to collapse a single range down to one line per customer. In tomorrow's podcast, we will look at alternate uses for consolidate.
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.
Before we get started this week, I want to talk about Facebook.
Facebook is a great social network application.
We added a new page there for mrexcel.com.
So, if you have a Facebook account or want to a set one up, just search for mrexcel.com.
You’ll find our page there.
Man, what I'd like you to do is sign up as a fan.
That allows you to keep track of our upcoming events.
I'm going to have some fun content here.
For example, everyone who signs up as a fan by this Friday, on Saturday we'll have a draw in for a free book.
So, it’s easy.
Just click on BECOME A FAN and, every once in a while, I'll be able to let you know about new events, and, speaking of events, we have something coming up this Wednesday.
If you are in Atlanta and you're going to the SouthEastern Accounting Show, I'll be doing 2 seminars there on Wednesday.
So, be sure to stop by and say hello if you're already going to that.
Alright, well, the last seminar I was at, someone said, hey, we need you to show us how to do consolidate, and I've always used consolidate in this manner.
It was completely unlike what they wanted to do though, so let me show you the consolidate that I always use, and then, tomorrow, we'll take a look at a different way to use consolidate.
Now, for me, I've been using consolidate even since there were pivot tables.
Consolidate is a great way to take a data set that has several hundred rows and group it down to 1 row per customer.
So, here I have data, REGION, PRODUCT, DATE, CUSTOMER, QUANTITY, REVENUE, COST OF GOODS SOLD, and PROFIT, and if I wanted to produce a summary by CUSTOMER, I would basically want to consolidate everything from D1 down to, well, H585.
So, what I'm going to do is I'm going to go to a blank section of the spreadsheet first, and go to the DATA menu, choose CONSOLIDATE, and, in the REFERENCE box, I’m going to specify that range.
So, I want to have the important field, the CUSTOMER field, as the leftmost field, choose all the data including the headings, and say that we want to use the labels in the TOP ROW and the LEFT COLUMN, click OK, and what we're going to get is we're going to get 1 line per customer -- very quick way to summarize this data.
Now, tomorrow, we'll take a look at how to use consolidate to add up similar worksheets -- completely different use for the exact same function.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Before we get started this week, I want to talk about Facebook.
Facebook is a great social network application.
We added a new page there for mrexcel.com.
So, if you have a Facebook account or want to a set one up, just search for mrexcel.com.
You’ll find our page there.
Man, what I'd like you to do is sign up as a fan.
That allows you to keep track of our upcoming events.
I'm going to have some fun content here.
For example, everyone who signs up as a fan by this Friday, on Saturday we'll have a draw in for a free book.
So, it’s easy.
Just click on BECOME A FAN and, every once in a while, I'll be able to let you know about new events, and, speaking of events, we have something coming up this Wednesday.
If you are in Atlanta and you're going to the SouthEastern Accounting Show, I'll be doing 2 seminars there on Wednesday.
So, be sure to stop by and say hello if you're already going to that.
Alright, well, the last seminar I was at, someone said, hey, we need you to show us how to do consolidate, and I've always used consolidate in this manner.
It was completely unlike what they wanted to do though, so let me show you the consolidate that I always use, and then, tomorrow, we'll take a look at a different way to use consolidate.
Now, for me, I've been using consolidate even since there were pivot tables.
Consolidate is a great way to take a data set that has several hundred rows and group it down to 1 row per customer.
So, here I have data, REGION, PRODUCT, DATE, CUSTOMER, QUANTITY, REVENUE, COST OF GOODS SOLD, and PROFIT, and if I wanted to produce a summary by CUSTOMER, I would basically want to consolidate everything from D1 down to, well, H585.
So, what I'm going to do is I'm going to go to a blank section of the spreadsheet first, and go to the DATA menu, choose CONSOLIDATE, and, in the REFERENCE box, I’m going to specify that range.
So, I want to have the important field, the CUSTOMER field, as the leftmost field, choose all the data including the headings, and say that we want to use the labels in the TOP ROW and the LEFT COLUMN, click OK, and what we're going to get is we're going to get 1 line per customer -- very quick way to summarize this data.
Now, tomorrow, we'll take a look at how to use consolidate to add up similar worksheets -- completely different use for the exact same function.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
