Excel pivot tables can help IT admins track support, costs

One of the most difficult things an IT administrator has to do is to provide data to enterprise management to justify labor costs, equipment expenditures and the like. Admins also need to analyze service call reports to make sure an organization is getting its money's worth in support. Excel pivot tables are a powerful, yet easy-to-use tool that can help you turn mountains of data into tables that are easy to read and put into PowerPoint presentations.

Analyzing expenses, producing detailed reports and reviewing service contracts may not be part of your job description, but it's always good to be able to communicate clearly and cut or at least contain costs. Demonstrating such abilities can help you move up the career ladder.

A good example of this is determining if a particular support contract is cost-effective. It is not terribly difficult to find data. Any hardware or software vendor will have reporting mechanisms to track support calls, though the data they collect may vary. The challenge is filtering and manipulating that data to answer the following questions:

• How many calls were logged in a month, quarter or year?
• Of those calls, how many were Priority 1, Priority 2, etc.?
• When were those calls logged -- during business hours or non-business hours?
• Which sites logged the most security calls (i.e., which sites have more events and thus require more effort and expense)?
• What was the time required to resolve each call?

Figure 1

Desktop admins and others in IT need to study the answers to determine if they are getting the most from each technology supplier. Is the vendor meeting its service-level agreement, and is the SLA the most efficient one possible? Are there specific locations, user types or devices that require more support?

Figure 2

The best way to show how admins can use Excel pivot tables is with a practical example. Suppose you receive a report with support call data such as that shown in Figure 1. Obviously, this is a small, simple table, but the principle is the same.

Creating a pivot table will let us select columns in the table and sum, count or average the date (or other operations) and present the result in an easy-to read format. To do so, follow these steps:

Figure 3

1. Select all rows and all columns of the spreadsheet that you want included. Hint: Click in cell A1 (in Figure 1) then hit Ctrl-Alt-Right Arrow, then Ctrl-Alt-Down Arrow. The selected cells will be shaded. Make sure to include column headers in the selection.

2. In the tool bar, go to Insert -- Pivot Table (Figure 2), and the Create Pivot Tool will appear (Figure 3). Note that the Table range is shown. You can edit it here if needed. The New Worksheet option is selected by default. This option will create a new worksheet for the pivot table and is recommended. Click OK.

If you get an error complaining about columns having to have headers and the pivot table fails, delete any blank columns.

Figure 4

3. Figure 4 shows the new worksheet and the pivot table. In the upper right corner is the Field List. This is a list of column headers. By selecting any set of headers in that list, it will add the data to the Report Area at the left.

Figure 5

For example, I want to see how many P1, P2, P3 and P4 priority incidents are in the table. They are stored in the Priority column, so select the Priority field (Figure 5). 

In the Report Area, you will see the values Excel found in the Priority column -- P1, P2, P3, P4. These are what you would see in a filter in the worksheet.

Figure 6

4. Underneath the Field List are four areas: Report Filter, Column Labels, Row Labels, and Values. Note that the Priority field is already in the Row Labels area as a result of selecting it in the Field List.

In order to find out the quantity of P1, P2, P3, and P4 incidents, click on the Priority field in the Field List, and drag it to the Values Area (Figure 6). This produces, by default, a "count" of the values in the priority column. So, we can see we have nine P1, six P2 and five each of P3 and P4 incidents.

In my next tip, we'll add some complexity and see how adding criteria to sort data can help with IT reporting.