The Complete Guide to Use Pivot Table
                     The Complete 
Guide to Use 
Pivot Table
Introduction to Pivot Table
 One of the best things about 
Excel is the fact that a single 
worksheet can hold so much 
information. In fact, you could fill 
billions of cells if you needed that 
much room. A file with that much 
data could get pretty 
overwhelming, but there’s a 
feature called Pivot Tables that 
can help. PivotTables let you 
summarize and manipulate your 
data, without actually changing 
anything in the worksheet. Let’s 
take a look at how they work. In 
this example, I have some 
company sales data, with 
columns for the salesperson, 
region, account, order amount, 
and month. And I need to find the 
answer to a very specific 
question: What is the amount sold 
by each salesperson? 
Example of Pivot Table
 Because each salesperson has 
data on more than one row, I’d 
normally have to add these up in 
order to find the answer. But a 
PivotTable can do this for us 
automatically, which will make 
the task much, much easier. First 
you need to select your data. You 
can just click any cell if it’s 
formatted as a table; otherwise 
you need to select all the cells 
yourself (and don’t forget your 
column headers). When you’re 
ready, go to the Insert tab… then 
click the PivotTable command. 
You’ll usually want to place your 
PivotTable on a new worksheet, so 
make sure this option is selected. 
Then click OK… and a new sheet 
will appear.
Complete Solution
 Over on the left is our blank 
PivotTable (it’s ok that there’s 
nothing there yet). To the right is 
a list of fields that have actually 
been pulled from our column 
headers. This is where we control 
what is or isn’t displayed in the 
PivotTable. But before we 
continue, let’s take another look 
at that question: What is the 
amount sold by each 
salesperson? This question tells 
us exactly which fields to use to 
find out the answer: Salesperson 
and Order Amount. All you have 
to do is check the box next to the 
field… and it’ll be added to one of 
the areas below — in this case, 
the Rows category. Another 
method is to drag and drop the 
field into place; we’ll put this one 
under Values.
Complete Solution
 If we go back to the PivotTable… 
now we can see the information 
we were looking for; in other 
words, the amount sold by each 
salesperson. So our question has 
been answered just by choosing 
what fields to include. One of the 
best things about PivotTables is 
how easy they are to modify, so 
you can view and analyze 
different things. Let’s say, for 
example, that you wanted to 
know each person’s monthly 
sales, not just their total sales. 
Just drag the Month field… down 
here to Columns… and this will 
add a column for each month in 
the worksheet. If we wanted to 
know what each region sold, we 
could take out the Month and 
Salesperson fields… and then 
drag Region… down to Rows.
Pivot Table Best Practices
 So each time we change the 
fields, the PivotTable is just 
combining the source data in a 
different way, so we can view the 
information we need and we’re 
doing all this without adding or 
changing anything in the original 
worksheet.
 You can even experiment by 
dragging the fields into different 
areas, but some combinations 
work better than others. For 
instance, if I move Order Amount 
over to Columns or Rows…you 
can see that it doesn’t really give 
us any useful data. Since Order 
Amount contains numerical 
values, it really does work best in 
the Values area.
Advanced Excel Course by IPA
 Finally, if we click the arrow next 
to Order Amount… and go to 
Value Field Settings… we can 
change the function to something 
other than SUM. Instead we could 
count the number of cells; 
calculate the average; or use any 
of the other options here. Some of 
these functions will be more 
useful than others depending on 
your data, so that’s just 
something to keep in mind.
 If you to learn advance excel then 
join institute of professional 
accountants’ 
advanced excel course and 
learn complete advanced excel 
structured reference formulae, 
PowerQuery, PowerBI and more. 
In case of any query call us at 
9213855555.