Set IT support and reporting priorities with an Excel pivot table

An Excel pivot table can help IT report on support, especially if admins use its ability to prioritize data by location, date and other criteria.

Figure 1: A sample Excel pivot table

Figure 1

Most desktop administrators already know Excel as part of the Microsoft Office suite, but many in IT may not be making full use of its capabilities for analysis and reporting. As my previous article noted, this can be helpful for monitoring vendor support, as well as demonstrating your own worth to the enterprise.

Figure 2: A priority count

Figure 2

After creating an example report (Figure 1), we looked at how to create an Excel pivot table to show incidents by priority (Figure 2). You can also create sums; sort table data by model, date or other criteria; and clean up empty fields.

Figure 3: Adding sites to the list

Figure 3

That was easy enough, so let's add some complexity. Let's say I want to find out how many items of each priority level are recorded at each site.

Simply click the "Site" field in the field list. It will be added to the "Row Labels" area, and the report will be updated automatically, showing how many of each priority level is in each site (Figure 3).

Figure 4: Moving a site to the top of the list

Figure 4

Suppose that you want to see the data displayed by city rather than by priority as the first criteria. In the Row Labels area, simply drag the Site field above the Priority field, or click on the Site field and select the "Move Up" option in the menu that appears.

Figure 4 now shows the number of calls, by priority level, for each city. Note that on the Site lines in the report, the total for that site is shown on the same line (see circled CHI, for Chicago). I'll discuss later how totals and subtotals can be turned off.

Figure 5: Adding priority to columns

Figure 5

Another way to display the data would be to put either the Site or Priority field in the "Column Labels" area (Figure 5). Simply drag the Site field to the Column Labels area for a different view of the data.

Figure 6: Adding "time to close hrs."

Figure 6

Now let's add another criterion to the table. In addition to Priority levels by site, I want to see how many hours were required for each priority level at each site.

Figure 7: Field settings

Figure 7

Since we want to see values in the report for these hours, drag the "Time to Close Hrs" field from the field list to the Values area.

Figure 8: Average selection

Figure 8

Note that in Figure 6, a new column was added for this value in the report. Thus, we see that the P1 calls in ATL (Atlanta) took a total of 16 hours, or an average of 5.3 hours each. Compare that to the service-level agreement to see if the vendor is achieving the SLA.

Under Values, we have the "Count of Priority" and "Sum of Time to Close Hrs" fields.

To show the "Average of the Time to Close Hrs" field, click on the Sum of Time to Close Hrs field in the Values area (Figure 7), and in the Value Field Settings dialog (Figure 8), select "Average." Note there are other operations to choose from.

Figure 9: Average hrs. in report

Figure 9

Click OK, and the report now shows the Average hours in the Time to Close Hrs column in the report (Figure 9).

By following these simple principles, you can add and manipulate data to show various combinations of reports.

Once the Excel pivot table is created, the displaying of data is just a couple of mouse clicks away.

My next tip will look at formatting, how to save an Excel pivot table and more.

This was first published in January 2014

Dig Deeper on Microsoft Office Suite



Find more PRO+ content and other member only offers, here.

1 comment


Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: