Simply Tech

Demystifying pivot tables

A step-by-step primer on creating an Excel PivotTable

By Karen Lancaster

As an insurance pro, you know the drill: You're trying to build a report. You're searching agency records, hunting and sorting for specifics line by line and trying to keep track of the pertinent information. Soon the data starts to meld into a huge blob of letters and figures. You might eventually get what you're looking for, but you might also be wasting a lot of time and effort.

Many agents and brokers use Microsoft Excel^® to summarize and analyze data, but many probably do not use the tool to its full potential. Enter the Microsoft PivotTable^®, a feature in Excel that aggregates your information and showcases it with a new, useful look. This powerful tool works its wonders in only a few clicks, sparing report-builders the time and aggravation of more traditional methods of visualizing data.

Pivot tables provide relief from the tedium of endless scrolling and the uncertainty of hoping you can remember everything as you hunt for important data. Once you become familiar with pivot tables, you can quickly organize data in a format most useful for making important business decisions.

Pivot tables allow you to position—or pivot—data in a way that gives it meaning. Drag and drop specific fields and records into a pivot table, and the information tells users what they want to know. For example, a pivot table can be created to look at policies by type, producer, company, CSR, or a combination of categories.

You can compare data by year. A pivot table can show how much business you placed with individual carriers each year and how much commission your business earned. You can pull the transactions, split the month from the year in the spreadsheet, and run a pivot table, putting the years across the top and the carriers on the left. You can also add other criteria such as producer, so you know how much business a specific producer writes and with whom. This is a helpful tool for making sure you meet minimums with specific carriers. For numerical figures, you can find the sum, average, count or any combination.

When you put records into a pivot table, they will automatically be organized in a logical order, or they can be "pivoted" the way you want them to look.

Pivot tables can look complex, which is often a reason so many shy away from them. Yet, if you spend a little time to learn the basics, pivot tables become less intimidating. The biggest advantage to working with pivot tables is being able to get information from a large data source in a short time and with only a few steps. Pivot tables seem complicated because they can be built to be rather precise. Once you learn them, however, you'll wish you'd learned them sooner.

A closer look

Here's a simple example: You want to know, by producer, how much and for what your customers were billed by month. First, you'll have to pull data from your agency or broker management system and put it into an Excel spreadsheet.

Once you get the data into the spreadsheet, you'll do a little formatting to ensure that the pivot table you create aligns correctly. Next, you'll extract some data parts to get the billing information you need for this example.

Here are a few tips:

• Be sure you have no empty columns or rows to distort your data. Before you do any other formatting, delete any empty columns or rows. To make your headers distinct, make them bold or italicized.

• To split a date (i.e., Effective Date) into separate columns for month and year so you can run pivot reports by either field or both:

—Insert two blank columns where you want the effective month and year to be in your spreadsheet. To insert blank columns, highlight the columns where you want to insert them, right click and choose Insert.

—Name the columns EffMo and EffYr by clicking in the first cell of the column and typing the name.

—In the first empty cell under EffMo, type =Month (I2). The "I" is for the column and the "2" for the row. "I2" is the location of the effective date starting with the first line of data, not the title.

—Copy and paste the data down to the end by the method you use. Use the double-click at the bottom right corner of the cell at the cross arrows. This copies the formula down to the last row of data.

—In the cell named EffYr, type =Year (I2). "I2" is the location of the effective date starting with the first line of data, not the title.

—Select each column the EffMo and EffYr is in and give the cells a general format.

—EffMo and EffYr are now in a format you can work with.

Now you're in the home stretch. In Excel, click on the "Insert" tab at the top left of the page, then click on "PivotTable" below and to the left. "PivotTable Field List" appears on the right. Here you can choose fields you want to add to the report. Click on the box next to the field you want.

For the purpose of our monthly-billing-by-line-of-business example, choose type, month and sum of amount. Under the "PivotTable Field List" are the column and row labels. The fields will automatically appear in those areas as you choose them. To make the table look different, you can drag and drop the labels in different boxes to position them differently in your table.

To add by producer, click on the producer (Pr) field. Excel automatically adds that field to the pivot table and subtotals when you choose a field. The report shows the month, then the producer-by-line-of-business subtotals.

To change the report to show the producer, then each month subtotaled by the producer, click on the Pr, hold it, and drag it to the "Row Labels" box under the "PivotTable Field List" box. Be sure you place Pr above the "Month" field. Then the report will automatically change to show that each producer has a heading and each month is subtotaled by line of business.

You can add a filter for a particular month(s) by clicking on the month in the "Row Labels" box on the right side of the screen and dragging it up to the report filter. Then click on the drop-down arrow to the right of the Month Filter and select the months you want included in your report. Click on boxes 1, 2 and 3, and you can review first-quarter billings or look at each month individually.

To save what you've created, right-click on the tab at the bottom of the sheet and type in the new name for your report.

Increasing numbers of insurance professionals are realizing the usefulness of pivot tables, but many people have yet to appreciate how much time and frustration this tool can alleviate. 

For additional information and a little practice with pivot tables, check out http://office.microsoft.com/en-us/excel-help/results.aspx?filter=1&ck=1&av=zxl140&qu=PivotTable.

The author

Karen Lancaster is the chief information officer for Western Marine Insurance Services, Inc., in Stockton, California. She has worked in IT since 1984, automating Western Marine from typewriters and teletypes to PCs, e-mail, Citrix, Exchange, MS Office and virtualization. She is also chair of the Education Committee with Applied Systems Client Network (ASCnet).