Teach Users To Manipulate Data With Excel Pivot Tables
December 18, 2000
Mary Ann Richardson
as found on URL: http://techrepublic.com.com/5102-6270-1034640.html
If your users are looking for a great way to manipulate data in Excel, pivot tables may be the answer. Here's the information you need to teach your users how to start using this valuable Excel feature.
I recently read
an article that
ranked Excel pivot tables among a list of functions that users were
forced to buy but never used. Yet, the most popular demos I have
witnessed at trade shows were those that involved dragging and dropping
data items onto a designated area of a screen to obtain answers to
pressing business-critical questions (e.g., which products are making
money and which aren’t or which stores were not meeting their
sales goals). What seemed to attract the show attendees to such
software was the ability to find answers with just a few clicks of the
mouse. Here’s a quick way to teach your users how Excel pivot
tables can be just as functional and easy to use.
Learning pivot tables without mastering databases
materials in Excel
don’t let students near pivot tables until they have mastered
creating a database in a spreadsheet. Users also must learn to use a
form to enter data into the database, sort that data, filter the data,
add validation checks, and so on. The majority of users learning Excel
probably won’t build or enter data into the databases that
contain their needed information. Most of the databases they will use
pivot tables for aren’t even created in Excel but will be
downloaded off of a server over a company network or the Internet.
These users simply need an answer to a question, and they need it fast.
Start with something simple
Rather than introducing pivot tables to students with a complex lesson on database creation, begin by showing them a typical database in Excel from which they can begin asking questions. Keep the database small—all the records should be visible on the screen without scrolling. Likewise, use an absolute minimum of fields, i.e., only the ones you will actually need in your pivot table demo. The database shown in Figure A fits these requirements. Also, it is a good idea to presort the database to make it easier for the student to check the pivot table results against the actual data in the table.
This database has been sorted by division.
After pointing out the various fields of data contained in this database, suggest some questions that the information in the table can answer. For example, how does the average salary of female employees compare with that of the male employees? Then walk users through the PivotTable Wizard. First, have them select the entire database. Next, select Data in the menu bar, followed by PivotTable And PivotChart Report… in the drop-down menu (see Figure B).
|Select PivotTable And PivotChart Report… from the drop-down menu.|
Selecting the PivotTable And PivotChart Report… brings up the PivotTable And PivotChart Wizard as shown in Figure C.
The PivotTable And PivotChart Wizard
At this point, select the defaults by clicking the Next button. Since you have already chosen Microsoft Excel List Or Database to analyze and have already selected that list, Step 2 of the Wizard is already completed for you, as shown in Figure D.
|Choosing the defaults takes care of Step 2 of the Wizard.|
Clicking the Next button in Step 2 Of 3 of the Wizard brings up the next dialog box. For this first demo, we will place the pivot table on a new worksheet by clicking on the Finish button (see Figure E).
This pivot table will be on a new worksheet.
After the Finish button is clicked, the Wizard sets up the pivot table on a new worksheet. Note that there is a new toolbar that lists the five data fields from the database. Since our objective is to compare the salaries of the male employees with the female employees, we will drag and drop the Gender field to the box that says Drop Row Fields Here. Next, we will drag and drop the Salary field to the Drop Data Items Here box, since this is the data we will be averaging (see Figure F).
|Each of these boxes will hold specific data for your pivot table.|
After dragging and dropping both fields as indicated, the following pivot table shown in Figure G is displayed.
Here‘s the finished product.
Now that the table is built, we can begin asking questions of the data. Once a table is built, it can be saved to the workbook and used again to reflect changes in the data. By default, the PivotTable Wizard calculates the Sum Of Salary field. But we need to compare the average. To change the calculation, we first click on A3 to select the data field and then click on the Field Settings button in the PivotTable toolbar (see Figure H).
Select the Field Settings button in the PivotTable toolbar.
After clicking on the Field Settings button, a dialog box appears. Select Average under the Summarize by: scroll box (see Figure I).
|Select Average from the options listed in the Summarize by: scroll box.|
After clicking OK, the pivot table will now provide the average salaries for both groups, as well as the average for all employees (see Figure J).
|The average salaries for male, female, and all employees are now provided.|
In a similar manner, we can choose Max from the PivotTable Field dialog box to get the maximum salaries for both groups, as shown in Figure K.
|The maximum salaries for each group of employees are now presented.|
Likewise, if we had selected Min, we would get the results shown in Figure L.
|Now the minimum salaries are displayed.|
Now that you’ve shown your students how to create and analyze data with pivot tables, they need an eye-catching way to display their results. This is where pivot charts come in. Pivot charts are the best way to present your data analysis.
|Title:||Teach users to manipulate data with Excel pivot tables|
|Author:||Mary Ann Richardson|
|Certain materials herein are included under the fair use exemption of the U.S. Copyright law and have been prepared according to the educational multimedia fair use guidelines and are restricted from further use. This work may be protected by further copyright, reproduction and distribution in violation of United States Copyright Law is prohibited.|