Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

Pivot Table Quick Start Guide by jzc15495


									                          Pivot Table Quick Start Guide
                             Wichita Public Schools
                      Instructional Technology Department
        Pivot tables are like a “Rubik’s cube of information.” Once you enter the data, you can
        twist it around until you get it to look the way you want. Without pivot tables, if you
        enter the data and then change your mind about the layout, you have to copy and paste
        your columns or retype everything. But with a pivot table, you can change the layout in

Things to do before creating a pivot table.
        Make sure your data is in the correct format – Categories should be listed in columns.
           A record is a single row.
        Make sure you have removed all filters.
        Make sure there are no blank columns or rows in your data.

Steps for Using a Pivot Table

   1.   Click on a cell that contains data anywhere in your spreadsheet.
   2.   Click on the Insert Tab
   3.   Select the Pivot Table button from the Tables group.
   4.   Select PivotTable from the list.
   5.   The Create Pivot Table dialog box will appear.
            a. Excel automatically chooses a data range. If you want to change the data range,
                simply click on the range button and select a new range.
            b. Select where you want the Pivot Table to appear (In a new worksheet or on an
                existing worksheet).
            c. Click OK.
6. A new worksheet opens with a blank pivot table. You’ll see that the fields (columns)
   from the original spreadsheet were carried over to the Pivot Table Field List.

7. Use the following guidelines as you drag the fields (headings) for the data you want to
   analyze into the row, column, and data/value areas. Note that you can drag the headings
   right onto the chart on the spreadsheet or into the rectangles below the field list. At the
   very least, you must drag one field into the row or column area and one button into the
   data/values area.

   Row and Column Areas – Drag category or non-numerical data to these areas. Examples
   may include teacher name, student ethnicity, male/female, etc. You may need to “play
   around with it” to figure out the best data presentation.

   Data/Value Area – Drag numerical data to this area. Usually, this will be student scores
   on a particular standard or assessment

   In this example, we’d like to see the average total score by teacher. So, drag the teacher
   field to the column area and the Total score field to the column area.
8. You may notice that by default, it calculates a SUM
   or Count of the total score for each teacher (which
   can be affected by the number of students taking
   the assessment) rather than the average total score
   by teacher. To correct, this, click on the dropdown
   menu in the Values box and choose Value Field
9. The following dialog box will appear. Choose how you would like Excel to calculate the
   data and click OK.

10. Your pivot table will now show the average score by teacher. To change the categories
    or data being analyzed, simply drag the old headings back to the list and the new
    headings to rectangles in the bottom right portion of your screen. In this example, we can
    easily change the pivot table to analyze the total score by race rather than by teacher.
11. When you are working with pivot tables, two new tabs appear along the right side of the
   ribbon: the Pivot Table Options Tab and the Pivot Table Design Tab.
       a. Use the Options Tab to move the pivot table to a new or existing sheet or create a
          Pivot Chart from the data (see directions below). You can also use this tab to
          show or hide the field list, buttons, or field headers.

       b. Use the Design tab to change the layout and design of the pivot table. You can
          choose to include banded rows or columns or add or remove totals from the pivot
          table on this tab.

12. To create a chart from the pivot table data, click anywhere on the pivot table so the two
    new tabs appear. Then, click on the Pivot Table Options tab and choose the Pivot Chart
    icon. A dialog box will appear asking you to choose a chart type. Make a selection and
    click OK.
13. The chart will be automatically created for you on the page. A filter pane will also
    appear, allowing you to manipulate the data in the chart without creating a new one.
    Simply select different information from the dropdown menus provided.

14. Four new tabs will appear allowing you to change and design your Pivot Table Chart: the
    Pivot Table Design, Layout, Format, and Analyze tabs. These tabs are similar to those
    that appear when creating a general chart in Excel.

To top