Adding Tickmark Indicators To Your Excel Analysis Comments

By Chris Newman • Updated: 02/24/16 • 9 min read

Adding Indicators To your Analytical Story in Excel Spreadsheets

We Visualize Data, Why Not Commentary?

Have you ever heard that age old phrase, “A picture is worth a thousand words”?

My guess is you probably have and you intuitively know visuals leave more of an impact than dry-heaving numbers onto a page and handing it to your manager.

I think most people who provide data analytics understand the power of charting their data to tell their story, but what I see overlooked time and time again is the lack of visualization with the commentary that compliments those beautiful charts.

Today, I am going to show you how to easily add indicators to your commentary so your audience (ie the decision-makers) already has the right state of mind before reading a single letter of your comments. This will tremendously help them comprehend your analysis and score you “brownie points”, because they will be able to do it in a fraction of the time as well!

The Power Of The Indicator

BONUS TIP: You can add bullet points to a cell by using the shortcut key ALT+7 (use the seven on the number pad not main keyboard)

Above is an example of some commentary that describes the story of the bar chart. One might think at first glance because the numbers are increasing over time, that the metric is performing well. However, after reading through the commentary you can come to the conclusion that the company is drastically headed in the wrong direction.

Here’s how it would look if you added some simple little indicators to go along with each comment…

Chart With Tickmark Indcators Microsoft Excel Spreadsheet

By literally adding 2 characters, the perspective of glancing over this report immediately changes and your audience knows that this area has some problems and are likely to spend some time looking into the area of this business.

How I Add Indicators To My Comments

There are a TON of ways you could approach adding indicators to your comments.

The first way that jumps from your mind might be to use the stoplight icons within Excel’s built-in Conditional Formatting in combination with some sort of formula tied to your data. Your logic might look something like below, were you can simply enter in a “g”, “y”, or “r” to get your desired colored tickmark.

Excel Conditional Formatting Stoplight Icons

This methodology works fine, however I personally like to have complete control over my formatting options. My personal preference is to use an icon font such as Webdings or Wingdings and combine that with some conditional formatting to change the font color based on the icon font character.

Here’s a cheat sheet for all the icon fonts you have access to within Microsoft Office.

To help you visualize how this concept works. Let’s say I want to use triangles within the Wingdings 3 font to indicate good/bad comments. Here are the steps you would need to take to manually incorporate this:

Add the regular character that corresponds to the symbol you want to display in front of your text (see my Character Map for reference)
Highlight your symbol character and give it a specific font color.
Highlight the symbol character, navigate to the Home Tab, and change the font name to your desired icon font (in my example below, I use Wingdings 3)

How To Automate This With A VBA Macro

Now if you plan on doing this on a regular basis, you are going to love what I am about to share with you in this section. Below is my personal VBA code, where you can cycle through three different indicators at the click of a button!

Take a look at how easy it can be:

VBA Macro To Add Indicator Tickmark to Text

Retaining Bold Characters

The one super-cool part about this macro is it remembers characters that are bold. A classic format that I use in the “Real World” at my day job looks as follows:

When the code line cell.FormulaR1C1 = Right(cell.Text, Len(cell.Text) – 2) is executed, the entire text losses any bold characters that may have been formatted. To counteract this, I use an array (ie list) variable to record the position of each bold character prior to any changes. Then once the modifications are made, the code cycles through the list of positions and bolds the specific characters to their original format.

Enough of the nerdy stuff….here is the actual code for you to automate this process!

Hopefully, my code below is commented well enough to where you can easily modify it with your own symbols & colors with little effort.

The VBA Code

Sub TextTickmark_Triangle()
'PURPOSE: Add Triangle Tickmark Indicator to beginning of selection text
'SOURCE: www.TheSpreadsheetGuru.com
 
Dim cell As Range
Dim TextFont As String
Dim TickChar As String
Dim TickColor As Long
Dim BoldArray() As Variant
Dim BoldOffset As Integer
Dim y As Long
Dim x As Long
 
'Determine Direction and Color
   If TypeName(Selection) <> "Range" Then Exit Sub
  
'Loop through each cell in selection
  For Each cell In Selection.Cells
        
  'Store font type
    TextFont = cell.Characters(1, 1).Font.Name
  
  'Determine which color/type of tickmark to add
    If TextFont = "Wingdings 3" Then
      Select Case Left(cell.Text, 2)
        Case "p "
          TickColor = -16776961 'red
          TickChar = "q "
          BoldOffset = 0
        Case "q "
          TickColor = 49407 'Orange
          TickChar = "u "
          BoldOffset = 0
        Case "u "
          TickColor = 0
          TickChar = "" 'reset
          BoldOffset = -2
        Case Else
          Exit Sub
      End Select
    Else
      TickColor = -11489280 'green
      TickChar = "p "
      BoldOffset = 2
    End If
    
    'Reset out Bold Character array
      Erase BoldArray
      ReDim BoldArray(0)
      y = 0
      
    'Record Any Bold Characters Within Text
      For x = 1 To Len(cell.Text)
        If cell.Characters(x, 1).Font.FontStyle = "Bold" Then
          ReDim Preserve BoldArray(y)
          BoldArray(y) = x + BoldOffset
          y = y + 1
        End If
      Next x
    
    'Remove Previous Tickmark from Text
      If TickChar <> "p " Then
        cell.Font.Color = cell.Characters(3, 1).Font.Color
        cell.Font.Name = cell.Characters(3, 1).Font.Name
        cell.FormulaR1C1 = Right(cell.Text, Len(cell.Text) - 2)
      End If
      
    'Add Tickmark
      If TickChar <> "" Then
        cell.FormulaR1C1 = TickChar & cell.Text
        cell.Font.FontStyle = "Normal" 'Ensure text is not bold (for now)
        
        With cell.Characters(Start:=1, Length:=1).Font
          .Name = "Wingdings 3"
          .Color = TickColor
        End With
      End If
    
    'Re-Bold Any Text previously bolded
      If Not IsEmpty(BoldArray(0)) Then
        For x = LBound(BoldArray) To UBound(BoldArray)
          cell.Characters(Start:=BoldArray(x), Length:=1).Font.FontStyle = "Bold"
        Next x
      End If
       
  Next cell
 
End Sub

Learn More With This Example Workbook

I have created a sample workbook with some of the methodologies described in this article (including some conditional formatting examples). The workbook is completely unlocked so you can dig in and discover how the magic works.

If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.

Download Example File