Hello and welcome to our new video
In this video we are going to learn how to consolidate data
When you have multiple times data in one workbook then how
to consolidate it
So we are going to see the same thing in this video
So first of all I open excel
And after opening excel I can see that there are some names
and marks written here
And here also there is same data
So what I have done is that I have copied and pasted here,
here and here
What I want is that the marks written here Rahul's marks
are 36 and here also it is written 36 If I want to
consolidate this data with this data
Consolidate means to show the data by collecting
So my result should be 72
If I add these four data 36, 36 and then again
Then I can see Rahul's marks and the total marks on Rahul's
side
So this is called consolidating the data
Now let's see how this is done, what is formalism and what
is it
So first of all you have to select a cell where you can see
the result So where do I have to go, I have to go to the
data tab
In the data tab, I can see consolidate
If you click on consolidate, you will see the functions here
Now it depends on what you want to do
Like I want to sum the marks given in the first data and in
the second data
So I have to consolidate the marks of the first and second
data
So how will you consolidate, you can do it by sum
So here it depends on your use case
You have to find the maximum and minimum
If I want to sum, then I select sum
After that, it will ask you for the reference You will have
to tell the reference of the table
Which table or which data do you want to add
If you want to consolidate, then you have to click here And
you have to tell the reference
So as I have to tell the reference, what I did I want this
data
As I told from here, from A2 to B11
Here it is showing from A2 to B11 Now it has put $ $ means
freeze the range
So we did this and what we did after that
After entering, here it is already written
So I delete it first
So that things are more clear to you
Now let's do it again
Here I selected it again and I entered
Now what you have to do, now you have to add
So when you add, it will show here And it will be unchecked
I did it and saw it, that's why the left column was selected
But you don't have to bother, I am showing it from the
starting
What you have to do again, you have to click on add Okay,
one second, okay
You have to click on add
After that, you have to select this data
By clicking here And I selected this data
Then I entered
And now you will see that I have added it again
Okay, after that what I have to do
Then I have to go here
Okay, and then I have to select this
Then I have to enter
And then I added it once
After that what I have to do This fourth data, I have to do
the same thing
Selected and from here I added
Now you will see that in sheet number 1
These four data were written in different places Okay, they
have come to one place
So I have told all the references
That I have to consolidate the data 1, 2, 3, 4 So I told
this thing
After that you will see here
Use labels in top row left column
If you click on the left column
Then what does it mean
The left column is the one with the name Rahul Imran Jishan
So what will happen
That you will see this name here Once And this data is
written four times
So 36 plus 36 plus 36 plus 36 By doing this, I will see it
on the right hand side
So let's do it
Let's do it
I did it
So now you will see that
Rahul Imran Jishan The name was coming once
But what happened to my data Like 36, 36
What happened to this
If you do it four times
Then 144 will come
Like 90 90 is easy
Add a little 90 and how much is 90 180 is
And then 92, 70
And then 90 again 360 My math is very weak 360 is there
So you can see
You can cross check
What has happened to the data
Consolidated Now let's see one more thing
What if
Like in this data
If I write the same name again
Rahul So will it take its data
This is also a thing to see
That here
There are two names in the same data
So what
Will it include 39 in it
Or not
Or like here
What do we do
Let's do Jishan
And here we did
Jishan Okay So now let's see
Do it again
What does this thing do
I see it here
This thing
So where did I go again
Went to consolidate
And I went to consolidate
What did you do to all of them
Deleted Let's delete it first
Or Or What do I do
And here I see
Rahul And Imran
Rather than
If I have data in reference
So what did I do
Now you will see Jishan 360
But what happened now
Because 23 will also be added here
So I got it here 383 So this thing is clear to us
Can consolidate data
To consolidate data
We have to go to the data tab
We have seen all these things
Apart from this, we have learned one more thing
If in the same data
If you consider it as a table
It is not a table, it is data
If multiple times
The name is written
Then what will happen
It is not like a single name
Different places data
Then he will add it
Rahul should not be here once
If multiple times
Rahul is coming
It means that he will also add it
I hope you will be clear
How to consolidate data
And if there is any doubt
If you have not understood anything
Then play this video again
See you in the next video
Until then keep practicing