Participation Project #08 - Excel Pivot Tables

Pivot Tables

Pivot Tables allow users to summarize a large amount of information in an interactive way. Pivot Tables allow you to rearrange the data without having to create a new table. You can move columns around, change columns order (alphabetical, etc.). 

Pivot Tables can be found on the Insert ribbon but use the Pivot Table on the left as Pivot Charts are also on this insert, but it's on the right. Pivot Charts also give you an option to insert Both Pivot Chart & Pivot Table. 

Step 3a - Inserting Pivot Table

Select your cells using the name box in the upper left hand corner, then insert the PivotTable (not a PivotChart and PivotTable combo!). It will ask you what you want to insert, so you’re going to want to pick the Table/Range option. Since you’re on the CoalMined sheet, and it’s a named table, the table/range in the pop up may say CoalMined or it might say $A3:$J58 in your formula bar. Either one of these is okay. You’ll then drag the variables into the appropriate boxes. It specifically tells you in the instructions what variables you need to put where.  

 Your variables are always summarized when they get put into the Values field, and that summary will always default to Sum. When it asks you to summarize your data in a different way, you can change the summary by clicking on the drop down menu for each variable in the Values field. You will have to do this for each variable individually.  Once you click the Value Field Settings, you will get a dialog box that will give you other options for summarizing your data. 

Note that there’s a Number Format option on this pop up menu. If the directions tell you to format your data as currency, or as a number with two decimal places, or in any other way, you can click on this button and it will bring up your normal formatting menu like you would use if you were formatting any other cell.  

Step 4a - Creating Groups

To create groups, you'll use the PivotTable Analyze ribbon. It will most likely be grayed out when you click on the ribbon – just click on one of the cells in the first column and that option should become active. You can than select the “Group Selection” option to group your data. The information that needs to go in each of the boxes will be listed on the PDF directions.  

PivotCharts

A word of caution for the PivotChart – your titles of your fields are different on the chart than they are on the tables. If the directions say to put something in the Rows field, if you’ve got the chart active, you’ll see “Axis (Categories)” instead of “Rows.” The easy way to fix this is to click on the table so that it’s active instead of the chart. Once you’ve done that your field labels will change so they match what’s in the directions. To change your chart type and to move it to a new sheet, click on the Design ribbon instead of the PivotChart Analyze ribbon. You’ll format it just like you did with the charts last week.