The Complete Guide to Use Pivot Table

1
The Complete Guide to Use Pivot Table
2
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
  theres a feature called Pivot Tables that can
  help. PivotTables let you summarize and
  manipulate your data, without actually changing
  anything in the worksheet. Lets 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?

3
Example of Pivot Table

• Because each salesperson has data on more than
  one row, Id 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 its
  formatted as a table otherwise you need to
  select all the cells yourself (and dont forget
  your column headers). When youre ready, go to
  the Insert tab then click the PivotTable
  command. Youll 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.

4
Complete Solution

• Over on the left is our blank PivotTable (its ok
  that theres 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 isnt displayed in the PivotTable. But
  before we continue, lets 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 itll 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 well put this one under Values.

5
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.
  Lets say, for example, that you wanted to know
  each persons 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.

6
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
  were 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 Rowsyou can see that
  it doesnt really give us any useful data. Since
  Order Amount contains numerical values, it really
  does work best in the Values area.

7
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 thats 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.