hello and welcome to our new video in this video we are
going to talk about two things one is pivot table and
second is pivot chart now where do you get to see these
options or why we are using these options how these options
benefit us so we are going to learn in today's video let me
open excel and let me tell you where do you get to see
these options so for this you have to go to insert tab and
in insert tab you will get to see pivot table this is pivot
table option and if we talk about pivot
then you will get to see this option here now what does
pivot table do suppose you want to generate a report by
generating report what do I mean for example there is a
shop in which many people work 4,5,10,20 people work and I
want to get the data of that shop from 2012 to 2018 how
much total sale so how will I get that will I get that
total sale or I said particular person how much sale he has
done or how much profit he has generated so how much profit
he has generated so what is this we can
a report using pivot table
I will show you this so how do you do it so let me tell you
that here is the data as it show the shop and in that shop
from 2012 to 2018 in this we can see some names and also
month
In January how much profit and how much sale in February
So this means that in 2012 Jishan generated this much
profit in March and sold it to the shop
In the same way you can say that Hari Om generated this
much profit in July 2012 and sold it to the shop
In the same way you can say that in April 2014 Neeraj
generated this much profit and sold it to the shop
Now you are looking at these things and telling me
Now I asked that Neeraj sold here also in 2014 and you will
see that Neeraj sold in 2015 also
Now I want you to tell me how much Neeraj sold from 2012 to
2018 and how much profit he generated in total
So the first option is that I will see one by one that in
2015 Neeraj sold this much
Then I went to 2016 and saw that Neeraj sold in August also
And how much Neeraj sold in August, he sold this much
Then I will see that Neeraj sold in 2017 also
See here he sold in June also
He sold this much and generated this much profit
So the first option is that I take a white paper and write
all the data on it
And after writing all the data I will add it one by one
But this is time consuming
And what we are seeing in Excel from the starting is that
we have to see smart ways so that we get the answer
immediately
So the same smart things are going to be done in this
So first of all what you have to do
One more thing you have to remember in the case of pivot
table
That you can see this header here that there is year, name,
month, sale, profit
You have to write this always
This is called field
I will show you what is the work of this in the pivot table
Why is it important
If I say that tell me the thing of particular month
Then there should be the option of month
I should also have the header of the month
Then only I will be able to tell that how much sale has
been done in all these months
So let's see this
I will tell you again that where is the use of these fields
So first of all what you have to do
You have to select the whole table
Like a lot of rows have been given in it
We have data in almost 85 rows
What did I do to this whole
Selected first
After that I have to go to the insert tab
When I will go to the insert tab
I will see the pivot table option
Now what I have to do
I have to click on the pivot table option
When I clicked on the pivot table option
It is showing me here
Select a table or range
We have already selected the whole table
Now what is written in it
I will tell you
Here it is written sheet 1
We know that whatever work we are doing
We are doing it in sheet 1
After that it told me
You selected $A and $1 $A means
From this column
And from this first row
To $E
Means To this column and $85
Means the last row number is 85
So this means
After this
It is asking
Choose where you want
The pivot table report to be placed
Where do you want to generate the pivot table
You want to do it in this sheet
You want to do it in the second sheet
You want to do it in the third sheet
It is asking
I want it here
What option do I have to select
I have to select the option
Existing worksheet
After that
It is asking
Destination references not valid
What does it mean
You have told that you want it in the existing worksheet
But the table that will be generated
It will be somewhere
So what do we have to do
We have to select a cell
When you select a cell
Its address is here
The name of the cell is here
Inside sheet 1
See what is written here $J means
In this column
In row number 9
In the cell
The whole report should be generated
So I clicked ok
When you click on ok
You see here
On its side
The fields of the table are written
Year is written, name is written
Month is there, sale is there, profit is there
After this
If you see below, things are written here
Drag filter between areas below
Column rows
What does it mean
It means
You have to show the data horizontally
Or you have to show it in columns
In columns
J, K, L, M
What does filter mean
Filter means
I want this particular month
I have 12 months
I said
Give me the data of February
How much has been sold in February
Filter means
What does values mean
It means
All the data we have
Like the data of sale
We can sum it
I will show you
First of all, I selected year
Show me the year
Now Sum of years is coming
We do not need the sum of these years 2012 We do not need
this
What will you do
Sum is here
Count is here
I will show you
Sum of year
If you do not need the sum
You need the count
From 2012 to 2018
How many years
Where do you have to go
You have to go to the value field setting
You have to count
You can see the same functions 2-4 functions
In home tab
You can see
Average, Maximum, Minimum
Count, Sum
These are the same functions
If I want to count
What will I do
I said count
He counted the year
He said 84
We do not have to count
We have to sum
Go to the value field setting
Sum
Sum We do not have to sum
We have to show the data 2012, 2013
I am trying to tell the same thing
What happens in values
You can sum
You have some data
You can count
Minimum, maximum
What do we need
We do not need this
We will put this thing
In rows
Click with mouse
And drag
When you drag
It will show the data 2012, 13, 14, 15, 16, 17, 18
After this
I said
How much did I sell in 2012
How much did I sell in 2013
How much did I sell in 2014
And how much profit
Tell that data
I told you
This field should be yours
If it is not added, then it does not show in this option,
so we understood why we wrote these fields here, so it is
very important to mention this field, after that I clicked
on the sale data and what I did, now see when I clicked on
the sale data, then it came in values and we saw that
whatever comes in values, what can we do in it, we can do
sum, we can do maximum found or we can do minimum found,
what can we do other than this, we can do count, so what
happened here, this is the sum of
in 2012, see here is the data of 2012, in 2012, whatever
sale has been done, what you have done, you have done the
total, so you understood that this is the sale of 49,222,
after that what is here, in 2013, you came to know that
there was a sale of 49,000 something, in 2013, what
happened, there was a sale of 50,000 something, after that
you saw what happened in 2014, in 2014, the sale was
dipped, so where is he getting the data from, he is getting
the data from here, the data of 2014, he added
whole data, and showed in the last, by doing the grand
total, that brother from 2012 to 2018, how much total sale
has been done, how much amount of sale has been done,
so I have to find that, you tell, the total amount of sale
you have done, how much profit, in 2012, how much profit
you get, or overall till today, how much total profit we
get, so what I have to do, I have to go here, click on
profit, and here you see, sum of profit has come here, now
sum of profit, when it comes, so you know here, why it is
in values because you are doing the sum, so here we came to
know that in 2012 you have sold this much, in columns how
much sales happened in 2012 2013
I will bring it back here so that it looks a little better
after that what is it names or month what i want do one
thing let's name it what did he say in 2012
Rahul did this much and generated this much profit
Imran did this much generated this much profit shivam did
this much generated this much profit so in this way you can
generate a report in 2013 what happened, Rahul did this
much profited this much Imran did this much so in this way
you can show things
You can also apply filters
For example, I want to apply filter on my name
I want to know when Rahul sold in 2012 and how much he sold
in 2013
Consolidate and show Rahul's data
When I take my name here Filter means I have 10-12 names
Select a name
When you take your name in the filter, it shows you the old
option
Now you have to show Rahul's data
Ok It shows Rahul's data
Rahul sold this much in 2013, 14, 15 16 and profited this
much
Click on month
It shows Rahul sold this much in 2012, 14, 15 16 and
profited this much
If I want to filter my name and month
I take my name in the filter and click on month all
Now tell me
Select multiple filter
Then rains
So, in 2012, Rahul must have sold in easter and same way in
2016 in january, february, march
It shows my numbers in 2012, from January, February, March,
Rahul has done profit and sale in January only.
Here you can see, you can also filter things here.
When do you need the data?
Apart from this, what can we do in this?
We have understood that year, name, month, sale or profit.
In this way, we can generate a pivot table.
After this, let's see how to generate a pivot table or how
to see the data in it.
Now let's talk about whether we can delete the pivot table
or not.
I say you can delete it.
How to delete?
You have two options to delete.
The first option is that you select the entire table and
click the delete button on the keyboard.
The entire data will be deleted.
The second option is that you select the entire table and
click the delete button on the keyboard.
I selected the entire table.
I went to the insert option.
I selected the entire table.
If you do not want to select the entire table, you can
select the delete option.
If you do not want to select the entire table, you can
click on any cell.
When you click on any cell, it will show the fields and the
pivot table tool.
You have to click on the pivot table tool.
When you click on it, you will see the select option.
You have to select the entire pivot table.
After selecting it, you have to click the delete button on
the keyboard.
The most important thing is that you do not have to go to
the pivot table tool.
After that, you have to select the entire pivot table.
It is better to select the entire pivot table and click the
delete button on the keyboard.
It is better to select the entire pivot table and click the
delete button on the keyboard.
We have seen how to use the pivot table.
We prepare reports in the pivot table.
We prepare reports from 2012 to 2019.
We see the pivot chart.
We go to sheet no. 2.
We see the pivot chart.
If I want to know that how many people are there who are
interested in computers, then count it and show me on the
chart.
So how will you show this? If you want to show things in
the form of a chart, then what we have to use for that?
Pivot chart option So I select it and select as many fields
as I want.
I have selected from here to here.
And then I went to the insert option again.
Now I will not go to the pivot table.
If I want to show things in the form of a chart, then where
will I go? I will go to the pivot chart.
Now the pivot chart option which is given here.
If you are using old versions, then you will not get to see
it individually.
Where will you get it?
There will be an arrow below the pivot table.
When you click on that arrow, you will see it next to the
pivot table.
If you are using the old version.
But if you are using the new version, like I am using 2013.
or after that all the versions 2016, 2019 you will get to
see it separately so what i did, i selected whole table of
this query and i did pivot chart in this also you will get
to see two options pivot chart and second option is pivot
chart and pivot table if you don't want to use this
combined then this is for it, so now we want to do on pivot
chart, so we clicked now you will see i selected all the
data from here to here and it showed me in range that you
are using sheet no.2 you saw what was
earlier sheet no.2 was not there what was written there,
sheet no.1 this one, so in sheet no.2 it said that you are
selecting things from dollar B, from column no.B and from
row no.1 we said ok, this is column no.B and what is this
row no.1, means it is correct and what should be the column
here it should be this one and row should be 21, so see it
is here so we said now this is the same thing as we saw in
table what was shown in pivot table in the same way here
also it is giving option that
want it in new worksheet or existing worksheet so i said i
want it in existing worksheet now in existing worksheet it
will ask that where do you want i want it here so what i
did i selected it and i said ok when i said ok, it will
show you chart now see, in side it is showing fields again
that how you want data and it is showing the same thing
filters, values and you can drag in these areas so what i
did first show me name i did name and after that i did
interest it is showing me in this way
will say table is not created i said see this chart is
created age now if i do this gender because here it is
overlapping so i will remove gender, so what is first it is
showing me data
Rahul in painting
Deepak in music now what i have to do see there is a lot of
data i want data of painting people i want painting people
in chart now in all these things you have to filter so what
i have to do from here i have to take interest in filters
when i will take interest in filters what i have to do here
it is showing when i took interest in filters now here you
will see it is showing interest option click on interest
option and it is showing all those things some kids are in
computer some kids are in
some kids are in painting so i said show me kids in
painting so it is ok and here it is written
Rahul is in painting
Ajay is in painting, Manish is in painting
Srishti is in painting is it ok now i have to find gender
like here Srishti is female and these three people are male
now i have to find from painting male so i applied gender
and after applying gender i put it in filter and from here
i have to find female in painting so i said ok so you will
see data of Srishti
Srishti's chart or i have to find male in painting so i
will click on male and click ok so here it is written Rahul
is in painting
Ajay is in painting, Manish is in painting apart from this
i have to show kids in music so i will click on music and
click ok so you will see kids in music
Deepak, Anudeep, Hemant, Mukesh, Yugal all of them are in
music all of them are in music if i click on all so what
will happen it will show all of them
Rahul, Deepak, Ajay, Anudeep, Hemant, Manish all of them
are in music what is this tell me it is showing data of
male it is telling us
Rahul, Deepak, Ajay, Anudeep, Hemant, Mukesh, Yugal all of
them are in music if i click on all it will show all of
them now i have to find so it is the same chart it will
show you according to interest and according to gender
apart from this if i what else i can do in this what else i
can do in this in name if you don't want to select all so
you have to
Rahul Rahul Rahul Rahul Rahul Rahul Rahul Rahul Rahul Rahul
Rahul Rahul Rahul Nash Nash Nash Nash Nash Nash Nash Nash
Nash Nash I will use chart form for this
I think this will be clear for you
So let's move it from here
And it shows things in the background
You can also edit things from here
You can do what you want
You can do ok
So it will change things here
Because we have not selected everything
So how will it show
That's why it is showing blank
So this is enough for today's video
If you practice once or twice
Then you will understand how pivot table or chart works
If you don't understand this
Then play this video again