Pivot Table Quick Start Guide Wichita Public Schools Instructional Technology Department Introduction: 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 seconds. 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 Settings. 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.
Pages to are hidden for
"Pivot Table Quick Start Guide"Please download to view full document