Excel tables aren't just for end users or the financial side of the organization. As my previous articles explained, IT administrators can use this part of the Microsoft Office suite to justify expenses, review support levels and provide detailed reports.
As my first tip noted, it's possible to generate and sort data in an Excel pivot table by model, amount, location or time. As my second article showed, you can modify the criteria by which you sort and display data, making it easier to determine whether a service-level agreement is being followed.
We often talk about IT-specific monitoring tools -- many of which generate Excel-readable data -- but a good working knowledge of Excel pivot tables can help you communicate with nontechnical colleagues, especially those in upper management. Here are a few more basic Excel capabilities to know.
Formatting IT reports with Excel tables
To provide different formatting for presentations, you may want to use the Tabular form where the examples are shown in outline form. To change the format, click one of the fields in the Row Labels (or Column Labels) area, and then click Field Settings. You've probably been here before, but since we are editing Row labels, we have different options.
First, in Figure 1, subtotals can be set to Automatic (default), None or Custom. For our example, we'll pick None.
Now select the Layout & Print tab, and select the "Show item labels in tabular form" option (Figure 2). This is a very important option and will help format data in easy-to-read tables without further manipulation. Select OK.
Figure 3 shows the Priority data in a new column rather than in the Site column, and the subtotals per site have been eliminated.
Note that you can treat this pivot table in much the same way you canany other table. For instance, you could change the column headers -- such as renaming Count of Priority to Priority. You can also add a column in column E and do a calculation, such as multiplying the value in column D with that in column C. When you're done with this, type the explicit cell number, such as D4, rather than clicking on the cell.
In addition, cell borders can be added, text format changed, cells shaded, etc., although I'd recommend copying it to a new worksheet and formatting it there (see below).
You can also filter the data in the report. For example, suppose all you wanted to see was the data for the ATL (Atlanta) site. Go to the Row Labels header and click the arrow. It works just like a filter in any worksheet (Figure 4).
Saving an Excel pivot table
The Excel table can also be copied to another sheet to do more manipulation or to save it. If you like the report you have created, select it, copy it and paste it to a new worksheet.
Be aware: When pasting a pivot table, a simple paste value will copy the active pivot table -- usually not what you want. Right-click, select Paste Special and select the Text option. This is the only way to save an Excel pivot table, unless you want to build several pivot tables, which come in handy when you are working with different reports.
Updating IT report data
Be careful -- if you change, add or delete data to the source worksheet, you will need to refresh the pivot table. For instance, suppose you want to see how many calls were logged to replace disk drives. The text in the Description column isn't consistent, so it would be helpful to add a new column and just put "HDD" for every event that involves replacing a hard disk drive.
To get this new column and values to show up in the pivot table, go to the pivot table, right-click in the report area and select Refresh (Figure 5). The new column heading will appear in the Field List and you can add that to the report. This is the process for deleting or changing data as well.
Losing the Field List
Figure 6 shows a pivot table without a Field List. This happens when you click outside of one of the pivot table fields, such as the Field List, Values area or report area. To get it back, just click in the report area.
A colleague once told me that pivot tables would change my life, and he was right! In addition to my tips, there are numerous books on Excel. You mainly need an Excel spreadsheet to practice with, and any time spent will pay dividends.
Excel pivot tables allow IT admins to easily manipulate data and see it in different combinations. You may see data in ways you had not thought of. With the added value of formatting, you can smoothly go from a pivot table to a PowerPoint presentation.
This was first published in January 2014