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.
A. 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 at the bottom of the page. You can also download this Excel file 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
Icon sets visually depict certain circumstances within data. For example, the illustrations below 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 below. 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 ≥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 <1 and ≥0, meaning the number represents no variance. Lastly, set the value for the third icon in the set. In this example, we want any Number to show the red upside-down triangle if the value is <0, meaning the number represents a negative variance. Click OK.
Sparklines
Sparklines show trends and patterns in data with small charts that fit in a single cell.
For example, the two screenshots below show sales performance over five months. The second screenshot contains sparklines that help the user easily and quickly see trends such as Smith's sales performance consistently declining while Adams's has consistently improved. The sparklines also make it obvious that the dip for February's sales performance is largely due to a decline in Thomas's February sales performance.
To create sparklines, select the cell or cells where you would like the sparkline(s). On the Insert tab, within the Sparklines group, choose Line, Column, or Win/Loss. This will open the dialog box shown in the screenshot below. Select the data to be included in the sparkline. Click OK.
Once you create your sparklines, you can leave them as is or modify their design. To modify the design, select the sparkline(s) you want to modify and go to the Design tab (Sparkline tab on some Excel versions). Here, you will have many options for modifying the design of the sparklines, such as changing their color and adding points to accentuate specific data.
If your data contains empty values, you have options on how these values are included in the sparkline(s). Select the sparkline(s), go to the Design or Sparkline tab, click Edit Data, and select Hidden & Empty Cells. You will be given three options for handling empty cells: Gaps, Zero, and Connect data points with line.
Selecting Gaps will produce sparklines that look like the screenshot below.
Selecting Zero will produce Sparklines that look like the screenshot below.
Selecting Connect data points with line will produce sparklines that look like the screenshot below.
In addition to sparklines and icon sets, Excel offers several other techniques (including data bars and charts) to visually display all types of data. In fact, Excel 2016 and newer versions offer 19 categories of charts, including waterfall charts, sunburst charts, and map charts. Is there a particular type of chart you'd like to see explained in future columns? Please let us know by emailing us at jofatech@aicpa.org.
About the authors
Kelly L. Williams, CPA, Ph.D., MBA, is an assistant professor of accounting at Middle Tennessee State University. Byron Patrick, CPA/CITP, CGMA, is senior applications consultant at botkeeper.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
