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
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...
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.
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:
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
'PURPOSE: Add Triangle Tickmark Indicator to beginning of selection text
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
TickColor = -11489280 'green
TickChar = "p "
BoldOffset = 2
'Reset out Bold Character array
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
'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)
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
'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"
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. As always, in order to download this example file you will need to be a subscriber of my free tips newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.
Other Articles You May Like
Wingdings & Webdings Font Icon Character Map [TheSpreasheetGuru.com]