Now for another top ten skill that are the signs of an experienced user- pivot tables.
Pivot tables allow you to summarize the results of data and look at it in a number of different ways (i.e. pivot).
Do the exercise in the Pivot Table spreadsheet. The result is shown in the Pivot Table Result spreadsheet.
In this spreadsheet, we have data which we have set up in a named range called data. Naming ranges will make it easier to set up pivot tables.
To start off a pivot table, go to the menu bar, select Data, then Pivot Table (2000 or XP users will also see Pivot Table and Pivot Chart on the menu as shown below)
You get the first dialog box. Since we are creating a Pivot Table and using an Excel list, we leave this as is and click Next.
The second dialog box asks where is the data. We input data in the box since that is the named range for our data.
The third dialog box asks where do you want to put the data? We usually suggest a new sheet.
Now there are two ways to go. You could click Finish, which we will show here. This takes you to a new sheet with the pivot table. What you have is the shell of the pivot table, ready to drop items in.
In our first report, we want to show sales by product. Click on product and drag it to the Row Field section. You will see the product names appear and product highlighted in the field list.
We would like to see the sales by month. Let's show that going across, so click on Month and drag it to the column field section. Now we see the months appear going across and the Month field highlighted also in the field list since it is now an active field.
Our next piece is the data we want in the report. Click on Invoice Amount and drag it into the data items section.
Now we have a report. Let's do one more step. What if we also wanted to see results by rep. No problem. Click Rep and drag it into the page field area.
At this point, the report is the same, just that it shows rep at the top. No numbers have changed since the default is all reps.
Now though, we can easily switch to review individual rep results. Click the down arrow and select rep 1 and the results will change to just show the first rep.
Over the next few days, we will show you more features and ways to work with pivot tables. You are on your way to better analysis.