Q Can you suggest any quick ways to help data stand out in large tables of information? Not dashboards, but something within the tables themselves to allow for quick recognition of patterns without losing access to the numbers themselves.
As CPAs, we are experts on compiling, understanding, and explaining financial data. We also need to be proficient at presenting data in visually compelling ways.
Supplementing spreadsheet data with visual representation aids the user in identifying trends and patterns that may not be evident otherwise. Additionally, data visualizations can make spreadsheets look more professional and aesthetically appealing.
Icon sets and sparklines are among the many ways to visually represent data in Excel, and they are easy to use. Each walkthrough below includes a video you can access with the online version of this article atjournalofaccountancy.com/data. You can also download an Excel file (available at tinyurl.com/yyyjvp8w) with separate worksheets for each visualization method. Note that the content of this item is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.
Icon sets visually depict certain circumstances within data. For example, the illustrations below and at the top of the next column show budget variances for several departments. The second illustration contains an icon set that helps the user quickly see which departments have positive variances (green triangle pointed upward), negative variances (red triangle pointed downward), and no variances (yellow, horizontal rectangle).
To create an icon set, select the data you would like represented by icons (the variances, in this case). On the Home tab, click Conditional Formatting, Icon Sets, More Rules. This will open the dialog box shown at the top of the next page. Click the dropdown arrow next to Icon Style and select the icons you like.
Next, set the value for the first icon in the set. In the example above, we want any Number (selected from the Type dropdown menu) to show the green triangle if the value is [greater than or equal to] 1, meaning the number represents a positive variance. Then, set the value for the second icon in the set. In this example, we want any Number to show the yellow rectangle if the value is
Sparklines show trends and patterns in data with small charts that fit in a single cell.
For example, the two screenshots at the bottom right of the previous page show sales...