Pivot tables tutorial

What is an Excel worksheet?

You might think of a temporary table as a user created Summary table of your original spreadsheet. you create the table by defining which fields to view and how the information shocould be displayed. based on your field selections, Excel organizes the data so
You see a different view of your data.

As example, I 've uploaded a Sample spreadsheet of 4000 fictitious people, which has des the following data fields:

• Voter ID
• Party affiliation
• Their precinct
• Age Group
• When they last voted
• Years they 've been registered
• Ballot status

Looking at the first 20 voter records, you can see the content is boring. in this format, the key question it answers is how many voters exist in all the precincts.

Using Excel into tables, you can organize and group the same data in ways that start to answer questions such:

• What is the party breakdown by precinct?
• Do voters use permanent absentee ballots?
• Which precincts have the most Democrats?
• How many voter pamphlets do I need for precinct 2416?
• Do 18-21 year olds vote?

Excel tables allow you to group the spreadsheet or external data source by any of your data fields. The thumbnail below shows a count of voters by party by precinct.

Using a random table, I can continue to slice the information by selecting additional fields fromPivotTable Field List. For example, I can take the same data and segment by voter age group.

Understanding External table Structures

In the thumbnail above, I 've labeled the main areas of the specified table.

(1) Please tTable Field List-This section in the top right displays the fields in your spreadsheet. You may check a field or drag it to a quadrant in the lower portion.

(2) the lower right quadrants-this area definesWhereAnd
HowThe data shows on your partition table. you can have a field show in either a column or row. you may also indicate if the information shoshould be counted, summed, averaged, filtered and so on.

(3) The Red outlined area to the left is the result of your selections from (1) and (2 ). you'll see that the only difference I made in the last partition table was to drag the age group field underneath the precinct field inRow LabelsQuadrant.

How to create an Excel worksheet

There are several ways to build a temporary table. excel has logic that knows the field type and will try to place it in the correct row or column if you check the box. for example, numeric data such as precinct counts tends to appear to the right in columns.
Textual data, such as party wowould appear in rows.

While you can simply check fields to display and let Excel build your external table, I prefer to use the "Drag and Drop" method. this is partly because I like to visualize my data in columns and rows. I think it may also be easier if you have fields, which
Can appear to be numbers like a precinct value.

1. Open your original spreadsheet and remove any blank rows or columns.

2. Make sure each column has a heading, as it will be carried over to the Field List.

3. Make sure your cells are properly formatted for their data type.

4. Highlight your data range

5. ClickInsertTab.

6. SelectPivotTableButton fromTablesGroup.

7. SelectPivotTableFrom the list.

TheCreate writable tTableDialog appears.

8. Double-check yourTable/range:Value.

9. Select the radio buttonNew Worksheet.

10. ClickOK.

A new worksheet opens with a blank into table. You'll see that the fields from our source spreadsheet were carried over toPivotTable Field List.

11. Drag an item such as precinct fromPivotTable Field ListDown toRow LabelsQuadrant. The left side of your Excel spreadsheet shocould show a row for each precinct value. You shoshould also see a checkmark appear next
To precinct.

12. The next step is to ask what you wowould like to know about each precinct. I'll drag the party field fromPivotTable Field ListTo
Column labelsQuadrant. This will provide an additional column for each Party. Note that you won't see any numerical data.

13. To see the count for each Party, I need to drag the same field to
ValuesQuadrant. In this case, Excel determines I want a count of party. I cocould double-click the entry and choose anotherField setting. Excel has also added grand totals.

Additional groupings and options

As you build your Excel into table, you'll probably think of additional ways to group the information. for example, you might want to know the age range of voters by precinct by party. in this case, I wocould dragAge GroupColumn from
ThePivotTable Field ListDown belowPrecinctValue in
Row Labels.

Each age group is broken out and indented by precinct. at this stage, you might also be thinking of usability. as with a regular spreadsheet, you may manipulate the fields. for example, you might want to rename "Grand total" to "Total" or even collapse
Age values for one or more precincts. You can also hide or show rows and columns. These features work the same way as a regular spreadsheet.

One area that is different is the specified table has its own options. You can access these options by right-clicking a cell within and selectingPivotTable OPTIONS...For example, you might only want grand totals for columns and not rows.

There are also ways to filter the data using the controls next to Row labels or column labels on the specified table. You may also drag fields toReport FilterQuadrant.