Creating A Company Report Card - Continued

Step 5This screen gives you an option for placement of your pivot table. I usually select Existing worksheet, then click on the tab at the bottom labeled Sheet 2, and select a cell that I want the pivot table to start in. This gives me a worksheet that is separate from the database.

Step 6While in that same box, click the Layout button. With the screen that pops up, you can determine what information the pivot table will display. Simply drag the boxes from the right and place them where you want them. In this example, I have chosen to view the gross profit margin (GPM) of our jobs by Type. I also want to know how many jobs of each type we did.

Step 7Notice that my boxes for Actual GPM are labeled "Count of" and "Average of." This gives me the total count of jobs that fit the criteria and the average GPM of all jobs in each type. I set this up previously by simply dragging Actual GPM into the data area twice, then double-clicking on one of them. This brought up a format box for that item, and I selected Average. Also, after clicking the Number button, I selected to display Percentage with no decimal places.

Step 8Clicking OK brings you back to the Layout Wizard; click OK again, then Finish, and you can see the pivot table, which shows average GPM by job type.

Step 9For a more detailed look, I can add the name of each job under the type by simply dragging the Name box from the field list at the right and putting it under the word Type. Now I can see how we did on each specific job.

Step 10Let's change it again, to compare lead carpenters with each other and by job type. Right-click anywhere within the pivot table and select Pivot Table Wizard, then Layout. You are now back where you started, in the original Layout box. Move Name out from under Type and drag Lead over, putting it in the column spot. Click OK, then Finish. Now I can compare leads by job type and with one another.

Step 11Because our database contains several years' worth of information, there are some lead carpenters in it who no longer work for us. If I want to compare only our current leads, I simply click on the arrow next to the word Lead at the top. This pops up a list of all the leads; I can unclick the initials I don't want, click OK, and the pivot table refreshes itself with only our current leads.

Step 12Let's look at one more way to sort data. Right-click in the table, select Wizard, then Layout. Move Name back under Type, then move Type to the Page box in the upper-left corner. Click OK and Finish. In the Type pull-down menu, check only Kitchens. Now you are comparing kitchen jobs by lead. At the bottom is the total number of kitchens each lead has completed and his or her average GPM.