6 hours 3 minutes
this lesson is going to be about using the consulted data function off the XL.
And dysfunction is extremely useful if you have to put together data from different files that have the same identical or similar for mint, and they are strong
on this. They're showing data about the same things, but from different people, different locations, different
time periods. And you want to put them together in one consolidated table, which will show
So, um, the typical example for something like this
would be, for example, if you want to create sales forecasts
uh, for cars that have some sales people send for sales. That will happen next month.
So I'm a manager, and I want to put together whatever they have sent me as their sales forecast for their own sales in their regions or for the companies that they're working with.
So it looks like this. It's a very simple thing. I have a product name, then I have products
on for each product. I have, ah, the forecasted number of units that will be sold and the total value now this total value
is there because these people can choose what kind of discounter they're going to give to certain customers.
And according to the discounted, the unit price will vary from sales person to sell person for each type of product.
we just want to put them together, and we'll go back to the
sheet in which we are creating the combined consolidated sales forecast
and we'll just taken consolidate button.
So what we get is this
window in which we already have some, um,
references, which I'm going to delete now because I was working on this file before. So let's say we're starting from scratch
s. So what we want is to put all the sales data together and to some, all the units and all the values
Ah ah, off the for each product so that we will get the total consolidated sales forecast for what? What is forecasted number off units to be sold in next month
for each product and also the value off all these units together.
So in this case, we're using the function some. Now, if we wanted just to put together some some other kind of data, we would maybe use account function or if you wanted to see the average cells. Ah
Price. For example, if we had that column, then you would use average. We could use Max men, whatever.
In this case, we need some. And this is the most common type of function used in consolidation.
And then we want to put the list of our references. So we're just taking the reference field.
We go to the first sheet in which we have sales forecast for Mike
and we select this table
and then we
Then we go to the next sheet
and Excel has anticipated that it's going to have the same format and include the same cells like the previous one.
So it has already selected them. We don't need to do that.
And if they're all the same, no, no need for that. And we're going to click, add,
And then if you go to the last sheet, you see here that Alex has added
one more row saying Alex focused,
which has changed the structure so it doesn't this stable we need doesn't start in cell a one but in Sally too.
So we're going to just re select this and do the adding,
So now we have
this selected. We have all the references we need for our consolidated sales forecast.
And then we have to look a TTE the other options here.
So we have you use labels in top row and left column,
and we have this one create links to swords data.
So what? Use labels in top row and left? Call me
that in our consolidated table. If they're on, we're going to have this role. So sells a 22
C two and this is going to be top together or top label off this stable,
and then the product names
are going to be left column.
So where is this important? Because
you see here, we're doing the function. Some
If we unchecked this left cone,
we're going tow. Have empty cells here in cells. Ah, a to a 10
in our, uh,
consolidated sales forecast.
So we needed
create links to source Datum
making sure that
it's not just copying Well, use its actually creating links to
these three sheets.
So if we will change something in the sheets. So somebody that last moment changes their focus to say, OK, I'm not going to sell 200 is going to be 180 units and the weather is not going to be 70. 600 is going to be
And we changed it in this sheet for Alex in his or hers forecast.
It's automatically going to change the consolidated forecast in our cells for CAS sheet.
The problem is,
if you are not linking this from different sheets, but you're linking this from different files.
In that case, you don't want this checked because then you have to make sure that as long as you use this table these files tail in absolutely the same location in which they were
when you created this consolidated sheet.
If you move them, if you delete them,
so move them in another folder, you are going to lose the link, and then you'll get the garbage in your consolidated.
You're going to get the air message and you don't want that.
So let's just click, okay?
And here is our consolidated forecast,
and you see here that it looks pretty much like sub totals or,
uh, grouping with these added pluses
left to the roll numbers and what I what are those let's click here.
And when you look at here,
when you're five months or five. Model five and then
basically 323 25 3 35 which is 3 25 from Mike for product, one
and 3 35 from Alex.
So you get all these data in there?
Plus, essentially, what is
some kind of sub Toto on Lee doesn't look like sub total. So you got all these
and you get this Sorry nine line products and you get the consolidated
number off units and value. So if we would goto the first sheet and simply changed this to from 3 25 to 3 20
and presenter, we go back here, it's no longer 918. Now it's 9 75 I haven't changed the value just the
off, But, uh,
how these links work,
So if you go back on, do is back to 980.
So this is how
consolidation works. And what is most important is to know that consolidation doesn't need to work on identical formative data. So let's just copy some of these
for example of these 1st 5
and copied them to sheep. 13. So let's copy them here.
And then let's do the Judy Sir sales forecast and what's copy everything.
And then, uh,
let's go Pete here
and then delete some. So let's say the Judy didn't forecast numbers. Products 23 and four
on Dhe didn't focus the eight.
While Alex didn't forecast anything from product six to the end,
they they were thinking, I'm not going to sell any of these, so let's not put them there. The focus is anyway zero.
And then let's do conciliation
here in she 12. Let's delete this
and let's go here and do the consolidate
and let's delete This is well
and let's do this
referee. Let's go click on reference. Go to sheet
13 and put these on
and then go to sheet 14 and selectees as well.
And, uh, let's ah,
check on everything and just like Okay,
so what did we get here?
We get product. 12345
67 and nine.
Those two sheets
have forecast for product eight, so it's not included there.
But what is overlapping is, for example, product five. You have 110 units here,
and we have 120 units here
and in total. We have 230 units here. So Excel did find
the matching product names
and consolidated data for all the products that exist in
all of the sheets,
regardless, if they exist in one or two
or if we had more 345
So every product that was mentioned at least once in any of these forecasts is there.
And the units are
some off whatever we have for that product. For some product, we had just one, and for some products we had more. So this is how it works,
and this is all about consolidate function.