Auditors often embed colored labels and background hues in spreadsheets to enhance a worksheet’s organization, readability and visual appeal. Perhaps less obvious is an auditor’s ability to use colors to increase a spreadsheet’s accuracy and efficiency. This article presents several ideas along these lines, using Microsoft Excel 2013. In the discussions that follow, the term “worksheet” refers to a single page in an Excel file, while the term “workbook” refers to the entire set of worksheets in an Excel file.
Conditional Formatting
The term “cell color” refers to the fill color of a spreadsheet cell, and it is sometimes convenient to use different fill colors to highlight different rows or individual cells matching certain criteria. Such coloring calls attention to these rows or values—for example, because they are exceptions to norms or because they are elements demanding special attention in some other way. Although this coloring task can be performed item by item, Excel has an automated tool for this: conditional formatting. To use it, first highlight the column of data to be formatted, and then click the “Conditional Formatting” option from Excel’s main (“Home”) menu. The drop-down choices in figure 1 appear, from which a selection can be made.
For numeric data, it may be easiest to select “Highlight Cell Rules” from this set of choices, as shown in figure 1. Here, the user can ask Excel to select values that exceed, fall short of, lie between or are equal to threshold values that can be selected in subsequent dialog boxes. For example, to highlight those purchase amounts that exceed a limit of US $5,000, the user can specify the rule “Greater than 5000” as illustrated in figure 2. Additional capabilities include Excel’s ability to use (1) the “Text that Contains” option to highlight those cells that contain specific text (e.g., a name, such as Acme) or (2) the “Duplicate Values” option to identify duplicate values within the same data range (e.g., a duplicate employee name or Social Security number).
Finally, Excel provides special tools for handling dates. For simple formatting requirements such as the desire to highlight dates exceeding or falling below a certain value, the “Highlight Cell Rules” option can be used to enter the date in the “Greater Than” or “Less Than” dialog boxes. Figure 3 provides an illustration for conditional formatting for dates after 1 June 2015. Excel also allows highlighting dates relative to the current one (e.g., to highlight dates occurring “in the last 7 days”). To utilize this latter option, select “A Date Occurring” from the menu shown in figure 1. Figure 4 illustrates the set of date choices for this option.
The objective of all conditional formatting is to have Excel automatically select and format those cells that satisfy a specific criterion. In many instances, this formatting can simply mean highlighting the cells with a desired background color. The choices for such formatting come from the drop-down list of options in figure 2 and are illustrated in figure 5. The “Custom Format” option that appears at the bottom of this list provides several additional formatting options, including changing the type of numeric format (e.g., integer or number of decimal places), font size, font color, text strike-through, underlining, font type (e.g., Times New Roman), bolding, italicizing, or border style and color.
Finally, using the “Icon Sets” option (see the last option in figure 1) allows the user to embed different types of colorful icons within the data range to emphasize important information. Figure 6 presents an example: a horizontal analysis of a condensed income statement, using two conditionally formatted icon sets. The different icons in both sets represent significant increases (the green up arrow), significant decreases (the red down arrow) or insignificant changes (the yellow circle). In this example, the icons in the first icon column (to the left of the Amount column) identify dollar differences exceeding US $1.5 million, while the icons to the left of the Percent column in the second icon column identify percent differences exceeding 10 percent. These thresholds are arbitrary, and an auditor can use whatever thresholds are appropriate for the task at hand.
Figure 6—Embedding Icons in a Horizontal Analysis
Source: Filzen and Simkin. Reprinted with permission.
Figure 7 illustrates the icon set choices available in Excel 2013. The choice depends upon how many different symbols are needed. Unless programmed otherwise, Excel will first divide the data into equal-sized groups within the data range, as dictated by the number of icons in the selected set. For example, if the spreadsheet developer chooses a set of four icons, Excel will assign icons in the selected data range based on quartiles. However, as illustrated in figure 6, these assignments are customizable using the menu options in figure 8. To display the dialog box in figure 8, click on the “More Rules” option in figure 7.
Filtering and Sorting With Colors
In addition to formatting spreadsheet data with different background colors, font colors or icons, auditors can also filter or sort by colors or icons. For example, a user selects the data in figure 6 and clicks on the “Filter” option on Excel’s Home page. If Excel detects the presence of icons, then clicking on the “Sort by Color” or the “Filter by Color” option will automatically permit the auditor to filter by cell icon, as illustrated in figure 9. If others who are less familiar with spreadsheet techniques will be using the spreadsheet, these sorting or filtering options may be easier for them to understand and use than creating custom filtering options based upon the actual values of the underlying data.
Finally, it is useful to know that the sort order can be customized if desired. For example, figure 10 illustrates Excel’s traditional “Sort” dialog screen. Instead of using the default option of “Values” for the “Sort On” setting in this screen; however, an auditor can also sort by cell color, font color or even cell icon, as suggested by the options in figure 10.
Auditing With Charts
Excel now provides a wide array of chart types with which to graphically display data in meaningful ways. But charts can also be useful auditing tools. One example is when testing data for outliers—i.e., data values that fall far outside a normal range. Sometimes these are typing mistakes, while at other times, outliers are data values that signal breaches of policy or procedure.
Figure 11 provides an example of a chart that might be used for audit analysis—a bar chart of 200 purchase transactions. The chart suggests that most of the data fall in a relatively narrow range between US $0 and US $200. But the chart also immediately enables the auditor to identify two outliers—one purchase that is very high and one that is negative and, therefore, very low. These values are likely to be data-entry errors—and certainly call for further analysis. Once an auditor knows that a data set contains such errors, one or more conditional formats can identify the wayward values exactly and enable further inquiry.
Using Themes
The term “theme” refers to a set of font sizes, font styles and background colors that enable spreadsheet developers to quickly display outputs in stylish formats. In addition, themes enable spreadsheet developers to coordinate colors with other worksheets or even with other Microsoft applications—for example, Microsoft Word. Figure 12 provides an example of a formatted worksheet using Excel’s “Integral” theme.
Themes provide consistency and improve readability by coloring every other row and formatting all top-of-column labels. Such coloring can also improve data accuracy and efficiency when manually reading such data or copying them elsewhere. By alternating row colors, themes help the reader to stay on the correct line and allow them to move from line to line quickly.
Auditors can create a theme for a single worksheet or apply a theme to all the worksheets in the same workbook. Themes can also be customized to include corporate logos, icons and similar graphics to enhance the visual appeal of a spreadsheet and increase consistency across spreadsheets. To apply a theme to a single worksheet, follow these steps:
- Highlight the cells needing to be formatted rather than the entire spreadsheet. Excel formatting can be slow, so it is better to apply theme formats to just the active portion of a spreadsheet.
- From the main menu, click the “Insert” tab and then click on “Table” from the set of choices. This alerts Excel to the area on which the theme format should be applied. Click “OK” in the box that appears.
- Now click on the “Page Layout” tab from the main menu and then the “Themes” icon on the far left side. A drop-down set of theme choices appears from which to choose. As the user positions the cursor over a particular theme, Excel provides a live preview of the formatting in the worksheet.
- Click on a particular theme to select that theme. This will be the default theme for all other worksheets that may need to be formatted. Themes can be further customized by modifying their colors, fonts and effects (that affect any smart art, shapes or object borders). To do so, click on each of the buttons displaying these choices to the immediate right of the “Themes” icon in the main menu.
Finally, although the choice of worksheet colors depends upon the developer, the authors recommend not using bright colors when creating themes. Garish colors tend to grate on people over time, making the choice of muted colors a better option.
Coloring Worksheet Tabs
If a given spreadsheet workbook contains several worksheets, auditors may find it useful to rename these tabs in order to more easily identify their contents. It is just as easy to color the tabs of each worksheet. To do so, simply right-click on a tab and then select “Tab Color” from the list of options. A palette of color options then appears from which to choose the desired color for the tab. Be aware that in Excel, the color chosen for a tab will not be completely apparent at first because the worksheet itself is active. The color will appear only when an alternate tab is selected.
One reason to color worksheet tabs is to enhance their visual appeal. Perhaps a more important reason is because coloring tabs enables spreadsheet developers to better organize the worksheets themselves—for example, to identify the different worksheets for the same division of a company or the same fiscal quarter of several years’ worth of data. Such organization is especially useful if others will access or modify the workbook, as colored tabs can help them recognize how the set of worksheets are related and perhaps avoid the mistake of changing the wrong worksheet in a workbook.
A final reason to color spreadsheet tabs is to enable auditors to better identify the current, active worksheet. In the absence of colored tabs, Excel does this by bolding the lettering of the open worksheet and changing the color of the tab from light grey to white—a convention that can leave some uncertainty. If the tabs of the various worksheets are in color, however, Excel not only bolds the lettering in the tab of the active sheet, but also uses a lighter color, which stands out more (figure 13). This makes it easier to identify the open worksheet.
Changing the Color of Worksheet Gridlines
The color of the gridlines that appear in Excel worksheets does not print on paper by default, but this can be changed. Printing gridlines is useful, for example, when there are many columns on a printed page or the auditor wishes to increase the accuracy in identifying the particular row and column coordinates on the print page. To print gridlines, select “Print” within the “Gridlines” section of the Page Layout menu.
By default, the color of these gridlines is set to “Automatic,” but auditors can change this. To do so, select the File menu option, then ”Options” and then “Advanced” from the set of choices provided in the menu. Scroll down in the Advanced dialog box to the section titled “Display Options for this Worksheet,” and then select the desired color from the “Gridline” color drop-down box. Click OK to complete the new setting. The spreadsheet now displays the new gridline color choice.
Using different colors for the gridlines of different worksheets is a subtle way of helping auditors distinguish among different worksheets when their formats must otherwise remain identical. Gridline colors matching tab colors provide a consistent touch that may add to the overall effectiveness of the workbook itself.
Summary and Conclusion
Using colors in spreadsheets helps auditors add visual appeal, enhance professionalism, and increase user accuracy in interpreting and using the data in them. Examples of such coloring include using conditional formatting, filtering and sorting by colors rather than content, plotting data to identify outliers, using spreadsheet themes, coloring worksheet tabs, and changing the color of gridlines. Although this article mostly illustrated these techniques using financial data, they are also useful when auditing nonfinancial data, such as server logs, manufacturing outputs, physical counts of inventory, customers or business activities.
Joshua J. Filzen, Ph.D., CPA, is an assistant professor of accounting in the College of Business and Economics at Boise State University (Idaho, USA). His previous professional experience includes assistant professorships at the University of Nevada (USA) and Michigan Technological University (Houghton, USA), as well as a position as a senior accountant at Moss Adams LLP. He can be reached at joshuafilzen@boisestate.edu.
Mark G. Simkin, Ph.D., is a professor of information systems in the College of Business at the University of Nevada (USA). Prior to this, he worked in the Industrial Development-Finance Division of IBM and later taught in the Department of Decision Sciences at the University of Hawaii (Manoa, USA). He can be reached at markgsimkin@yahoo.com.