Introducing Microsoft Excel's Pivot Table Feature

Introducing Microsoft Excel’s

Pivot Table Feature

Nate Moore, CPA, MBA, CMPE

President

Moore Solutions, Inc.

If you’ve ever entered a claim in a medical billing software package, you know that it takes lots

of information from a variety of sources to file a clean claim. Most practice management

systems store all of that information but of necessity only offer a limited number of canned

reports. If you can either download those reports to Microsoft Excel

or link Excel directly to

the billing software, Excel offers a powerful feature called Pivot Tables that allow users to sort,

filter, and manipulate the data in a variety of ways. This article will use sample data to

demonstrate how to create a basic Pivot Table in Excel 2007.

Here is a link to a sample data set that could have been downloaded from a medical billing

software package. Download the spreadsheet with the data and click on the Sheet 1 tab to start

the Pivot Table on a blank sheet.

Figure 1

Figure 2

Figure 3

Create a Pivot Table

Put your cursor in cell A10 and click Pivot Table

from the Insert Tab

In the menu that appears, make sure the radio

button Select a table or range is

selected and then either select

the data range by clicking on the

box with the red arrow or

entering the data range as shown

in Figure 2.

From this box you can also Choose

where you want the Pivot Table

report to be placed, either in a

New Worksheet or an Existing

Worksheet. Your screen should

look similar to Figure 3.

Figure 4

Figure 5

The left circle shows where the Pivot Table will be placed. The right circle shows the Pivot

Table Field List with areas for Report Filter, Column Labels, Row

Labels, and Values. All of the columns in the original data are listed as

fields to be added to one of these four areas.

At this point you have created a Pivot Table with eight fields. Since the

sample data is for new consults, start by counting the number of new

consults. Drag the patient field from the Field List to the Values area,

as shown in Figure 4. Since Patient is a text field, Excel assumes that

you want to count the number of patients, which is correct. Select the

drop down arrow next to Count of Patients and choose Value Field

Settings. The Value Field Settings box allows you to create a Custom

Name for the field, choose how to summarize the data (Sum, Count,

Average, etc.) and the Number Format box allows you to format the result (General, Number,

Currency, etc.). Format Count of Patients as a Number with 0 decimal places and check the box

to use a comma to separate thousands. Click OK twice to proceed. Count of Patients should be

2,541 patients.

The next step is to use the Pivot Table to analyze the 2,541

new consults. Drag the Insurance field from the Field List to

the Row Labels area. Your Pivot Table should look like

Figure 5.

Excel has quickly and easily told you that 1,360 of your 2,541

patients have Commercial Insurance. You can further analyze

Figure 8

Figure 6

the new consults by dragging the Location field from the

Field List to the Column Labels area.

Now it’s easy to see the insurance companies broken

out by the five regions the sample clinic operates in.

The Central region clearly has the most patients and the

Central region also has nearly as many Medicare

patients as Commercial patients. The East region has

less than 10% as many new consults as the Central

region has. Your Pivot Table should like Figure 6. Note that if you can’t see the Pivot Table

Field List or the Pivot Table Tools on the toolbar, click on a cell inside the Pivot Table to bring

it back. You can show or hide the Field List by clicking the Field List button shown in Figure

With the basic Pivot Table set up, it’s

easy to move different fields between

rows and columns. With the Pivot

Table still set up as in Figure 6, drag

Location from Column Labels back

up to the Pivot Table Field List, drag

Insurance from Row Labels to the

Figure 7

Figure 9

Column Labels area, and drag Doctor from the Pivot Table Field List to the Row Labels area.

Your Pivot Table should look like Figure 8.

Which of the five clinic physicians saw more Medicare than Commercial insurance consults?

What do the five physicians have in common?

Grouping Data

The ability to move data fields from rows to columns and

back is very helpful when analyzing medical practice data.

Most fields in the example work well this way, but the New

Consult Date field is different. Drag the Doctor field from

the Row Labels area back to the Pivot Table Field List and

put the New Consult Date field in its place. Your Pivot

Table should look like Figure 9. Excel provides a Group

Selection feature under the Pivot Table Tools Options Menu. Remember that the Pivot Table

Tools Options Menu is only visible if a cell in the Pivot Table is selected. Choose any of the

dates in Column A and click Group Selection as shown in Figure 10.

Figure 10

Figure 11

Figure 12

Figure 13

Excel will allow you to group date fields by seconds,

minutes, hours, days, months, quarters, and/or years.

The selection box that appears allows multiple selections.

An example of the selection box is shown in Figure 11.

Select both months and years and click OK. The top of

your Pivot Table should look similar

to Figure 12. Note how quickly and

easily Excel grouped the dates by month and year.

Notice how Excel changed the Row Labels area in Figure 13. Now

instead of only having a New Consult Date field, Excel has added a

Years field as well. The New Consult Date field holds the months in

this example and the Years field tracks the years. To continue the

example, drag both the New Consult Date field and the Years field to

the Report Filter area directly above the Row Labels area.

Filtering Data

Figure 14

Figure 15

Once the data is organized in a Pivot Table, the next step is often to filter the data to focus on

specific questions. Excel puts fields in the Report Filter area directly above the Pivot Table to

give easy access for filtering the data. To continue the previous example, Click the drop-down

arrow next to Years

and select 2009

from the drop-down

menu that appears.

Also note the box at

the bottom of the drop-down menu titled Select Multiple Items. Rather than just selecting 2009,

as we’ll do in this example, Excel allows users to select multiple items from a list by checking

this box. Select 2009 from the drop-down menu and click OK. Note how the drop-down arrow

has changed in Figure 14. The drop-down arrow next to Years has a filter to indicate that the

field is filtered, while New Consult Date still has a drop-down arrow to indicate that none of the

months have been filtered in this example. Also note that Excel shows 2009 next to Years and

(All) next to New Consult Date to indicate what filter has been applied. To continue the filtering

example, drag Consult Level to the Row Labels area and replace Insurance with Doctor in the

Column Labels area, again by dragging and dropping the respective fields. Your Pivot Table

should look like Figure 15.

Figure 16

Assume that you want to compare the Consult Level billed by two similar physicians, Dr.

George and Dr. Wolfgang. An easy way to only show those two physicians is to click the drop-

down arrow next to Column Labels (circled in Figure 16) to filter the physicians shown in the

column. First uncheck the (Select All) box to clear all of the physicians, then check the boxes

next to Dr. George and Dr. Wolfgang and click OK. The result should look like Figure 16.

Again notice that the drop-down arrow next to Column Labels has changed to a filter to show

that the field is being filtered. The

analysis in Figure 16 clearly shows

that compared to Dr. Wolfgang, in

2009 Dr. George billed many more

level 4 and level 5 consults. Change

the years filter to look at 2007 and 2008 to quickly and easily determine if this pattern has been

consistent over time. Finally, notice that Excel allows multiple filters at once. In Figure 16 both

Years and Doctor have been filtered.

Sorting Data

Pivot Tables are easy to sort, either by the text in the rows or columns or by the values displayed.

To continue the example, first remove the filters from both Years and Doctor. Drag Consult

Level and Doctor back to the Field List. Drag Referring Physician to the Row Labels area and

drag Years from the Report Filter area to the Column Labels area. Your Pivot Table should

look like Figure 17.

Figure 17

Figure 18

Excel is currently sorting the data by the name of

the referring physician. It would be helpful to

quickly find the referring physician with the most

referrals. To do that, click anywhere in the Grand

Total column in the Pivot Table and click the

Descending Sort button circled in Figure 18.

You’ll see that Dr. Nickel referred the most patients during the three year period from 2007 to

2009. Excel can also sort by

individual columns. Click on

any cell in the 2009 column

and click the Descending Sort

button again. You’ll see a

different physician referred

the most patients in both 2009

and in 2007. What do the 19

referring physicians have in

common?

Conclusion

Excel’s Pivot Tables are a very powerful tool to analyze a large amount of data. The sample data

used in the examples has 8 columns and 2,541 rows, but Pivot Tables can easily group, filter and

sort the data into meaningful reports. Most practice management software has functionality to at

least download reports to Excel, if not link directly to Excel. Practices and practice

administrators would be well served by becoming familiar with Pivot Tables.