Software

Teach users to manipulate data with Excel pivot tables

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
Most course 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.

Figure A
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).

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.

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.

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).

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).

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.

Figure G
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).

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).

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).

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.

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.

Figure L
Now the minimum salaries are displayed.


Stay tuned!
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. In my next article, I will describe how to make your Excel data stand out by using pivot charts.
Do you find Mary Ann Richardson’s description of Excel pivot tables interesting and helpful? Will you be able to use this information to make your job easier? We want to know what you think. Your feedback is important to us. Post a comment or write to Mary Ann Richardson.

Editor's Picks