hello and welcome to our new video in this video we are
going to talk about table but you will say that sir table
is same as pivot table in last video but pivot table is
different from normal table so we are going to talk about
what is pivot table and how it is different from normal
table so first of all i will open excel and i have some
data here it has some names, age, male, female, gender,
city so first of all my question is can i call this table
whatever data i have written so answer is no
can't call this table whatever data i have written because
table has some features but we don't get those features
with this normal data so how to insert table how to see all
these things first of all let's see where we get table so
where we have to go again where we get pivot table remember
in insert tab so where we will get table in insert tab so
let's go to insert tab we click on insert tab you can see
table option and here you have shortcut what you can do
with ctrl plus t you can create
it is written here create a table to organize and analyze
related data so what i have to do either i have to select
all these data in this way whatever data i want where i
have to convert it so i have to select all these data and
click on table so it has told me create table where is the
data for your table and it is telling from here to range
from here to here the data which you have selected what we
are going to do convert it in table when it asks where is
the data for your table it means
this our normal data was normal it was not table you can
see below my table has headers so you can check it if your
table has headers so always make a practice that is a good
practice that you always make table headers make fields
what will that field help you we have seen it was helping
in pivot table what was helping in pivot table that we have
to generate any report so how we can do it if you want to
apply filter you can apply filter that i have mail so what
should be your practice that you
include header you always include field what i do i say ok
my table has headers now see everything has changed table
has become colorful i increase its size see here it is not
visible so i change its color instead of yellow now it is
visible name, age, male, female, city all these things are
visible before it was not visible here you can see drop
down menu you can short by color all these things before it
was normal now i remove it now shortcut ctrl z now it is
normal now it is not table again
it to table select data select data now ctrl plus t same
thing before go to insert select table ok now normal data
is converted to table ok
one more thing here all these tabs are visible apart from
this when we create table table add a tab in ribbon design
name now if you see it is not visible you can see you have
to click any cell of table you can see design option table
what is ribbon it is where ever options are given where
ever groups are given it is in ribbon if we click on design
we can see table name resize table summarize with pivot
table we know pivot table pivot table report remove
duplicates if there is duplicate data we
remove it next is convert to range it is very useful
feature we will cover it in coming videos because some
portion is like this which will be covered in upcoming
videos after that you will understand this feature
I will cover it later after that insert slicer what is
slicer we have to short data slicer is better option than
short
I will show you how to use it export if you want to export
export the data in this table to a share point share point
is a software in that you can share table data refresh
refresh means like in whatsapp you have added contact
number after that you refresh you can see it you have
inserted some data in your table but you can't see it so
what you will do click on refresh option after that header
row banded row table style options let's see one by one
first of all if you go to table name you can
table name suppose you have more than one table then what
you will do you should know name of table here you can name
your table
I will name it cafe style name so you can name your table
after that resize table when you use resize table suppose
here you have 20 rows of data if you want to add one more
data copy paste
so
here you will see one thing here I have now this this is
included in table many times if it is not included in table
then what you will do
Himanshi whole row is included in table if it is not
included then you have to select whole row suppose I select
whole row after that go to insert option here not insert
option go to design option because here you will get resize
table option when I resize table you will see highlight and
data but we want to make table so here you can see 21 I
said we want 25 so I can customize so highlight 25 so rest
of the rows can be included in table after that go back to
design here what other option
with pivot table now summarize with pivot table click and
see table table name is cafe style
I said yes table is selected what we do in pivot table we
do generate report now I go to existing worksheet
I want this worksheet after that select location So, I
selected the location and it showed me that the location is
in sheet 1, row 7 and column G.
I said OK.
So, it showed me the pivot table.
Now, if I want to show the name, it showed me all the names.
Now, if I want to show the age, it showed me the name along
with the age.
Now, if I want to show the male and female, it showed me
that and also the city.
It showed me everything.
But you will say, Sir, in the pivot table, we could see
different things.
We could see some data like this.
So, whatever data you want, you can do it here in the
values.
For example, if I take the name in the values, what will it
do?
It also showed me the count along with the name.
Whatever value you take here, we know that some function
will be applied on it.
Either the count function will be applied or the sum
function will be applied.
So, what I do is that I bring the age and I put the sum on
the age.
I brought the age and what did I do from here?
From here, I got the count of age.
I clicked on the count of age and went to the value field
setting.
And what did I do from here?
I summed it.
So, what is the total?
It showed me 290.
And it also shows the total age of the people who live in
Faridabad.
If we sum it, how much will it come out? 92.
So, the reports that are generated, you can do it according
to you.
What kind of filter do I have to apply?
Or what kind of data do I need?
If my requirement is that how much will be the total age of
the people living in Faridabad?
So, here I found out that it is 290.
You can do it differently according to the requirement.
So, I delete it.
I selected Puri.
And what did I do?
I clicked on the delete button.
What happened to the whole table?
Pivot table.
Moved from here.
So, we found out the pivot table.
The table that we have generated, we can also use the pivot
table feature in it.
That is, what can we do in this current table?
We can generate a report by using the pivot table.
Because we get the option.
Okay.
After this, let's move on to the next thing in the design.
Next is remove duplicates.
Now look here that Himanshi is a duplicate.
Okay.
There is data in this row too.
So, what do you have to do?
Suppose you have to delete.
Okay.
So, what did I do?
I removed it.
Now look at one thing in this.
Here we have selected all the things.
For example, we have selected the name.
We have selected the age.
Male, female, city.
After selecting all the things, we click on OK.
So, what happens?
Or if we select only one thing.
For example, I selected the city.
Then what will happen?
Okay.
Let's see.
For example, I selected all four things.
So, what should happen?
You can see the data of Himanshi twice here.
This is written by Himanshi.
Here is written by Himanshi.
What did I do?
I clicked on OK.
Okay.
So, he said to me.
Four duplicates value found and removed.
Twenty unique values remain.
Okay.
So, I said okay.
Let's see.
I said OK.
So, look here.
The data of Himanshi was deleted.
The one below.
Okay.
Now I bring it back once.
I bring it back.
Now let's see what happens by doing one by one.
Look, when you select all four fields and do it.
When you select all four fields and do it.
The benefit of this is that it looks at all four fields.
The data in all four fields should be exactly the same.
If it is exactly the same, then it will delete it.
Otherwise, it will not do it.
But what do we do?
We select one field.
So, what will it do on its base?
It will remove the whole row.
Let's see this thing.
Okay.
Where am I?
I go here.
Remove duplicates.
Now what do I do?
I don't want all.
So, I remove the name.
I uncheck it.
Okay.
And what do I do?
I just select the city.
I want the data of the duplicate city to be deleted.
Okay.
I said okay.
So, what will happen now?
It is saying that 19 duplicate values have been found.
That is, now this is the rest of the adjacent data.
That the people living in Faridabad.
The mail that was here.
Its age was something.
Its name was.
What will it do?
According to one field.
The rest of the three fields will also delete its data.
Okay.
So, what will happen with this?
Your data can be deleted.
Okay.
You will not be able to recover it.
So, don't use this option.
I bring it back.
Control Z.
But always remember that we have to select all four fields.
And if all four fields are identical.
Exactly the same.
Then we have to delete that data.
Okay.
Now all these options we are getting in the design tab.
They were not available before.
When we were using the normal table.
So, you understand.
What does the table bring with it?
It brings features.
After this, let's see what things are there.
Now one option comes.
Insert slicer.
Okay.
I have told you this option.
I will tell you later.
Okay.
If you go to Insert Slicer.
So, now look at this.
What is this?
This is a very interesting feature.
If I select it on the name.
Okay.
And selected on age.
And.
After that, I did male and female.
And let's do everything.
Okay.
And let's do it.
So, you will see that four things have been made.
I did it here.
I did male and female here.
After this, I did the name here.
And I did the age here.
I think it will be visible to everyone.
Now look at one thing.
I want to short the data from this table.
Okay.
Now suppose that I want the data of the people living in
Faridabad.
Okay.
We used to do short.
We used to do with shorting.
We can also do this thing with slicer.
It happens very well with slicer.
Look, suppose I want to do.
I want here.
I want the data of people living in Faridabad.
So look.
The data of people living in Faridabad came here.
Okay.
Now I want.
What do I want from the people living in Faridabad?
I just want female.
Okay.
So what did I do?
I clicked on female.
So the data of the female came here.
Okay.
After that, I thought that I now want this.
I want the people of the age of 35.
So I did it here.
So these 35 people came.
Okay.
After that, I want something.
I want Radha.
Okay.
So if you click on Radha, you will come to Radha.
Is it clear?
This is a very good feature.
If you have to do this.
Look, you will notice one thing here.
If I am clicking on one, then the other one is moving by
itself.
But suppose.
What do you want?
If you want to select both, then what will you do?
Then here is an option of multi-select.
Or you can also do Alt plus S.
So I do multi-select here.
So what is it now?
What can you do with both things?
You can select.
Or if you want to select more than two data.
You have to select Rahul too.
This thing.
Rahul will not be selected now.
Because we have put a filter.
It is short.
Okay.
So this thing will not happen.
For now.
But here we have seen Deepika and Radha.
That we can select more than one data.
If you click on the option of multi-select.
So I think this is clear to you.
Okay.
After this, let's see how to delete it.
Okay.
We have brought it.
Now to delete it.
It is a simple thing.
Select it completely.
What else do you do?
Click on the option of delete.
Okay.
Or what can you do other than this?
Tell me.
Try removing it.
You can do this too.
Okay.
Remove age.
Remove male, female.
What to do?
Right click on the mouse.
And it will open the whole menu like this.
Remove it.
Okay.
I bring it back.
Whatever my data was before.
Okay.
You can apply control z.
I have got all this data.
First.
After this, what options are left?
Let's see this.
We go back to the design.
Here it is written header row.
If I check the total row like this.
It is written to me.
You have a total of 20 rows in which there is data.
You can check it from here.
You are fine.
Other than this, you can say from here.
What do I have to do with the first column?
Highlight.
So what will happen to the first column?
It will be highlighted.
After this, if you say.
The last column is this one.
City column.
I have to highlight this.
So what will happen from here?
City column.
It will be highlighted.
Okay.
After this, you have to say.
If you want to see the banded column.
What does the banded column mean?
What is this?
It is filling the columns inside it.
Okay.
And after this, there is a filter button.
What happens on the filter button?
These filters are installed.
Like the name.
You have to shorten the name.
You have A2Z or Z2.
We have seen this before.
You can do the age in the same way.
You can filter.
If these options are visible to you.
With name.
With age.
With male, female.
With city.
You can remove it.
What do you have to do?
You have to uncheck it from here.
Now you will see.
There is no option here.
Okay.
What is after this?
After this is the style of the table.
What kind of table do you want?
Okay.
You.
If you click on anyone.
What will happen?
It will change.
Like you said.
What did you do to the whole table?
Selected.
And you said.
I want it like this.
Going down.
Okay.
So you will do it on anyone.
Look at the color.
Change.
It will go.
Okay.
You need in dark.
You need in dark theme.
Your table.
You can do this.
Okay.
You can do this.
Okay.
You can do this.
That's all for today, we will meet in the next video.