Excel Project

ExcelProject-12February2011

Today I am tying up the loose ends of this week’s Excel project in ISM 3004.  I am going through a step by step process to provide you, my blog follower, with a simple set of instructions of how to create a basic pivot table.  We will take a peek at the “Raw Data” used to make the pivot table first.  Next we will discuss the inputs required to make the table work.  Finally we will see the end product, the “AvgIncrease” pivot table.

The “Raw Data” is a listing of 500 individuals heart rates based on age and gender.  We take this data seen below and insert it into the pivot table by using the “Insert Tab” and select Pivot Table.  This data has been manipulated and formatted to fit the desires of a fictitious gym owner.

The next page that will open is a new tab on Excel.  I then manipulate the table to fit my data needs. The owner wants to see the percent of increase grouped by age and gender.  Here is a peak at how I will set up the columns, rows, and formatting to make it work.  Look at the right side of the screen shot to see the exact details on how to make the project work.

Now that you see the inputs for row labels are the subject age and the column labels are gender.  The values used are the average increase in heart rate in which I formatted as an average.  This will allow me to have a nice clean average when I format age in groups of ten, 20-29 etc.  I will do the formatting in those groups by right clicking on 20-20 in the row column, click on grouping, and then set the “By:” to “10”

Next we will meet the owner’s requirement of showing the results in percentages and blocking the Grand Total Labels.  Highlight the data in B:5/C:5-B:7-C:7, right click and then select format.  Next select percentage and ensure there is only one decimal place. The last step is to right click on both “Grand Total” headings and then click remove “Grand Total” to finish the pivot table. Here is the view of the pivot table and I hope my instructions will help others in their attempts at completing a pivot table