Consolidate Data

Video Activity
Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
or

Already have an account? Sign In »

Time
6 hours 3 minutes
Difficulty
Beginner
CEU/CPE
6
Video Transcription
00:00
this lesson is going to be about using the consulted data function off the XL.
00:07
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
00:22
on this. They're showing data about the same things, but from different people, different locations, different
00:29
time periods. And you want to put them together in one consolidated table, which will show
00:35
everything together.
00:37
So, um, the typical example for something like this
00:42
would be, for example, if you want to create sales forecasts
00:47
from the,
00:49
uh, for cars that have some sales people send for sales. That will happen next month.
00:56
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.
01:08
So it looks like this. It's a very simple thing. I have a product name, then I have products
01:17
on for each product. I have, ah, the forecasted number of units that will be sold and the total value now this total value
01:25
is there because these people can choose what kind of discounter they're going to give to certain customers.
01:33
And according to the discounted, the unit price will vary from sales person to sell person for each type of product.
01:42
So, um,
01:44
we just want to put them together, and we'll go back to the
01:49
sheet in which we are creating the combined consolidated sales forecast
01:55
and we'll just taken consolidate button.
01:57
So what we get is this
02:00
window in which we already have some, um,
02:07
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
02:15
s. So what we want is to put all the sales data together and to some, all the units and all the values
02:24
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
02:38
for each product and also the value off all these units together.
02:45
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
03:00
Price. For example, if we had that column, then you would use average. We could use Max men, whatever.
03:07
In this case, we need some. And this is the most common type of function used in consolidation.
03:15
And then we want to put the list of our references. So we're just taking the reference field.
03:21
We go to the first sheet in which we have sales forecast for Mike
03:25
and we select this table
03:28
and then we
03:30
say ad.
03:30
Then we go to the next sheet
03:34
and Excel has anticipated that it's going to have the same format and include the same cells like the previous one.
03:42
So it has already selected them. We don't need to do that.
03:45
And if they're all the same, no, no need for that. And we're going to click, add,
03:50
And then if you go to the last sheet, you see here that Alex has added
03:55
one more row saying Alex focused,
03:59
which has changed the structure so it doesn't this stable we need doesn't start in cell a one but in Sally too.
04:08
So we're going to just re select this and do the adding,
04:12
So now we have
04:14
this selected. We have all the references we need for our consolidated sales forecast.
04:21
And then we have to look a TTE the other options here.
04:25
So we have you use labels in top row and left column,
04:29
and we have this one create links to swords data.
04:32
So what? Use labels in top row and left? Call me
04:36
that in our consolidated table. If they're on, we're going to have this role. So sells a 22
04:44
C two and this is going to be top together or top label off this stable,
04:49
and then the product names
04:51
him
04:53
are going to be left column.
04:56
So where is this important? Because
04:58
you see here, we're doing the function. Some
05:00
If we unchecked this left cone,
05:03
we're going tow. Have empty cells here in cells. Ah, a to a 10
05:12
in our, uh,
05:13
consolidated sales forecast.
05:15
So we needed
05:16
left
05:18
checked,
05:19
create links to source Datum
05:21
is
05:23
making sure that
05:26
it's not just copying Well, use its actually creating links to
05:30
these three sheets.
05:32
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
05:46
but
05:46
7200.
05:48
And we changed it in this sheet for Alex in his or hers forecast.
05:56
It's automatically going to change the consolidated forecast in our cells for CAS sheet.
06:03
The problem is,
06:04
if you are not linking this from different sheets, but you're linking this from different files.
06:12
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
06:25
when you created this consolidated sheet.
06:29
If you move them, if you delete them,
06:31
so move them in another folder, you are going to lose the link, and then you'll get the garbage in your consolidated.
06:42
You're going to get the air message and you don't want that.
06:46
So let's just click, okay?
06:48
And here is our consolidated forecast,
06:51
and you see here that it looks pretty much like sub totals or,
06:59
uh, grouping with these added pluses
07:03
left to the roll numbers and what I what are those let's click here.
07:11
And when you look at here,
07:13
you get
07:15
when you're five months or five. Model five and then
07:17
basically 323 25 3 35 which is 3 25 from Mike for product, one
07:30
3 20
07:31
from Judy
07:33
and 3 35 from Alex.
07:36
So you get all these data in there?
07:41
Plus, essentially, what is
07:44
some kind of sub Toto on Lee doesn't look like sub total. So you got all these
07:51
five numbers
07:53
and you get this Sorry nine line products and you get the consolidated
08:01
number off units and value. So if we would goto the first sheet and simply changed this to from 3 25 to 3 20
08:11
and presenter, we go back here, it's no longer 918. Now it's 9 75 I haven't changed the value just the
08:20
example
08:20
off, But, uh,
08:24
how these links work,
08:24
So if you go back on, do is back to 980.
08:31
So this is how
08:33
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
08:46
for example of these 1st 5
08:50
and copied them to sheep. 13. So let's copy them here.
08:56
And then let's do the Judy Sir sales forecast and what's copy everything.
09:03
And then, uh,
09:05
let's go Pete here
09:09
and then delete some. So let's say the Judy didn't forecast numbers. Products 23 and four
09:18
on Dhe didn't focus the eight.
09:22
While Alex didn't forecast anything from product six to the end,
09:30
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.
09:37
And then let's do conciliation
09:41
here in she 12. Let's delete this
09:45
and let's go here and do the consolidate
09:48
and let's delete This is well
09:52
and let's do this
09:56
referee. Let's go click on reference. Go to sheet
09:58
13 and put these on
10:03
and then go to sheet 14 and selectees as well.
10:07
And, uh, let's ah,
10:11
check on everything and just like Okay,
10:16
so what did we get here?
10:18
We get product. 12345
10:22
67 and nine.
10:24
So
10:26
none off.
10:26
Those two sheets
10:28
have forecast for product eight, so it's not included there.
10:33
But what is overlapping is, for example, product five. You have 110 units here,
10:41
and we have 120 units here
10:46
and in total. We have 230 units here. So Excel did find
10:52
the matching product names
10:56
and consolidated data for all the products that exist in
11:03
all of the sheets,
11:05
regardless, if they exist in one or two
11:09
or if we had more 345
11:13
So every product that was mentioned at least once in any of these forecasts is there.
11:20
And the units are
11:22
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,
11:33
and this is all about consolidate function.
Up Next