How to Use the Pivot Table and Chart Option in Excel

Pivot Tables are simultaneously the most useful and most misunderstood of all the features that Excel has to offer. Pivot tables are great for analyzing and aggregating thousands of rows of data in a few clicks. I'll show how easy Pivot tables are to make in Excel, and how useful they are.

Instructions

Difficulty: Moderate

Things You’ll Need:

• Excel 2007
• A spreadsheet to practice on (If you don't have one already you can download a practice sheet from http://www.timeatlas.com/tutorials/pivot_table_example.xls)

1. Create a Pivot table Open the spreadsheet you wish to create a Pivot table for. Click the "Insert" tab on the menu ribbon and select "Pivot Table" A dialog box titled "Create Pivot Table" will appear on the screen. Select the range of cells you wish to compare by dragging the cursor through the columns. Select the "New Worksheet" radio button on the bottom and click "OK".
2. The next step involves what data you want to compare and how you want to view it. A pivot table will allow you to group data by rows, columns, values, and filters. My example spreadsheet contains a list of voters with data on what party they belong to, precinct, age group, the date they last voted on, the number of years they have been registered, and their ballot status. How many voters are each precinct? How many Democrats are in the age group of 31-40 years old? How many Democrats in Precinct 2424 voted in August of 2006? A Pivot Table will help us to analyze the data to answer these questions.
3. Create a row Let's answer the question, "How many party members are in each precinct?" We'll start by creating a Precinct Row Label. Go to the Pivot Table Field List box located on the right hand side of your new Pivot Table worksheet. Click on the "Precinct" field from the top of the box, hold the mouse button down, and drag it down to the "Row Labels" box on the bottom.
4. Create columns Now create a Column Label for our table. Select "Party" from the fields located on the top of the field list and drag it down to the "Column Labels" box. Now you'll see all the political parties that you have information on appear in the columns. Note that there is not yet any information on how many parties per precinct there are.
5. Tell the Pivot Table what we are looking for We have to tell Excel what values we are looking for in order to see any real data. Click on "Party" again and drag it down to the "Values" box.
6. Add a filter Now we have a useful table! We can tell how many party members are in each precinct at a glance. We can analyze our data even further. How many party members of each age group are in each precinct? One way to do this is to use a filter. Drag the "Age Group" field to the "Report Filter" box.
7. Change the filter to see different values Find the "Age Group" text box on the top left of the Pivot Table, which is set to "All". Select another Age range and watch the table change.
8. How many party members are in each precinct arranged by age group You can also see all of the possible age ranges on one sheet without using a filter. Uncheck the "Age Group" field filter on the top of the Pivot Table field list. Click and drag the "Age Group" field onto the "Row Labels" box under "Precinct".
9. You can see that the Pivot Table is a powerful but deceptively simple tool for analyzing data in Excel. The trick is to use it to answer questions you have about your data. A common question that people have is "What fields do I put where in the Pivot Table?" Write your question down in simple terms. Use the column that appears last in your sentence as the basis for your Row Label, and use anything else you wish to know about that column as your Column Label. For example, "How many party members do I have in each age group?" Use "Age Group" as your Row Label, and put "Party" into your Column Label.
10. Now you know the basics on how to create a pivot table. The best way to master them is to sit down with your spreadsheet and play with different options to see what works best for you.