Do you create dashboards in your job? Of course you do; which is why you’re here, perhaps looking for some tips and tricks. You’ve come to the right place.

When creating your dashboard you want to present trends in an eye-catching and consistent manner so you turn to standard Excel functionality and you will probably choose to use spark lines. Examine the scenario below: you have a number of projects with data sets for some sort of compliance measure. Perhaps RAG statuses for the past 12 weeks. This is what Excel’s column-based spark lines can do for you.

And it’s quite good; it shows the trends well with different heights for each value but there’s a flaw: the column heights are not consistent across all projects. In fact, the columns only have the same heights if all data points are present in each of the rows. You also have to force the issue by specifying minimum and maximum points in the spark line axis design ribbon group. Furthermore, what would you do if you had more than three data points to display? For example, if you want to show those weeks where the RAG status wasn’t recorded for some reason or other? What if you’re trying to represent five data points? In these cases Excel’s spark lines will fail you and you will be irritated.

Let’s try another approach and use the win/loss spark line format, Here’s what you get.

This is better. The columns have identical heights, but there’s still something about the gaps between them that I don’t like; and the squares just don’t seem large enough and it’s impossible for them to be scaled. It also doesn’t address the larger set of data points issue,

Here’s the answer to the problem. Spark lines generated using VBA. Well, perhaps not spark lines as such; more like trend lines. But that’s what we’re after – a consistent, solid bar showing the trend with no distractions. This is ready for insertion into my dashboard with the data feed coming from elsewhere in the workbook. This example shows three data points but, as you’ll see, it can easily be extended to cover as many as you require.

How to do it

1. Create a workbook and include three tables in a worksheet with some sort of historical data. I’ve used project RAG statuses going back twelve weeks, but you could equally well choose any other measure you want for as many periods as you require. Ensure the data values are between 0 and 3. Name the tables table1, table2 & table3.
2. Select the column to the right of the tables and in each cell corresponding to the data rows change the font to Webdings and type the letter g twelve times. Copy this down for each row in your tables. You should see twelve black squares in each cell. Each square represents one week’s data.
3. Select each of the three cells above the blocks of squares and create three named ranges called Trend1, Trend2 & Trend3 referencing those cells only. These will be referred to later in the code.
4. Add a button and attach its click event to the macro below.

Sub GetTrend()

 Dim i As Integer
 Dim j As Integer
 Dim k As Integer
 Dim x As String
 Dim myTable As ListObject

 Application.ScreenUpdating = False

 'Get some new random number data to populate the tables
 Application.Calculate

    'For each table...
    For i = 1 To 3

    'Use each table in turn
    Set myTable = ActiveSheet.ListObjects("table" & i)

       'For each table row...
       For j = 1 To myTable.Range.Rows.Count

          'For each table column...
          For k = 1 To myTable.Range.Columns.Count

          'Change the font colours in the trend line block character by character
          With Range("Trend" & i).Offset(j, 0).Characters(Start:=(k), Length:=1).Font

             'For each cell in the table (j + 1 to exclude the table header row)
             Select Case myTable.Range.Cells(j + 1, k).Value
                Case ""
                   'Highlight missing data
                   .Color = RGB(79, 129, 189) 'Blue
                Case 0
                   'Colour depends on that selected in the colour picker dialog that is activated by the button
                   .Color = ActiveSheet.CommandButton1.BackColor
                Case 1
                   .Color = RGB(0, 176, 80) 'Dark green
                Case 2
                   .Color = RGB(255, 192, 0) 'Amber
                Case 3
                   .Color = vbRed
                Case 4 'etc
                   'Add more colours here
                Case Else
                   'Error message to catch bad data
                   x = MsgBox("Table has bad data. The cell must be empty, 0, 1, 2 or 3 only.", vbCritical + vbOKOnly, "Error")
                   Range("table" & i).Cells(j, k).Select 'Select the bad data cell
                   Application.ScreenUpdating =  True
                   Exit Sub
             End Select

          End With

       Next k

    Next j

 Next i

Range("A1").Select

Application.ScreenUpdating = True

End Sub

How it works

• In my tables I’ve used the RANDBETWEEN function in each of the tables’ cells so that a new random number is generated each time the workbook is calculated. The Application.Calculate command simply enters some fresh data so I can tell that something is happening each time I click the button. You can remove this code.
• The code runs for each of the three tables and uses an offset from each of the three named ranges in turn as a reference to where to change the font colours.
• The code then selects each of the tables’ rows and each cell within each column.
• For each cell, the value referenced by myTable.Range.Cells(j + 1, k).Value (the data table value) is tested by the Select Case structure.
• J + 1 is used to skip past the table’s header row.
• The result of this test determines the font colour and this is set using the With structure.
• The Range(“Trend” & i).Offset(j, 0).Characters(Start:=(k), Length:=1).Font code is where the colour change work is done and defines the cell to be changed (the offset, row =  j & column = 0, from the named range currently in scope), and the character within that cell’s value string, k, for a length of one character.
• An error message is displayed and the subroutine is ended if the code finds incorrect data and the cell involved is activated.

More colours

You can set the colour values to whatever you want in the code and I’ve also included a case to test if there is an empty string and then colour the character accordingly.

To demonstrate the use of a four-colour trend line I’ve also included functionality to select the fourth colour from within the worksheet. This is a function and subroutine I found at Erlandsen Data Consulting’s web site which provides a useful method of selecting custom colours. They have a short, but interesting, selection of VAB tips.

More characters

The download below includes a number of trend lines using characters other than the Webdings square. Some might be useful, others look simply decorative at best. You decide.

Things you can do

• Find a way to transform the GetTrend subroutine into a function.
• Experiment using just two colours.
• Experiment with the upper limit for the number of colours that could be used meaningfully in a trend line.
• Determine if there a way to automatically specify the font and character from within the subroutine, thus allowing different characters to represent different values.
• Investigate other uses of the Font object properties in the Excel Object Model Reference.
• Investigate the colour picker dialog code at http://erlandsendata.no/.

Example download

• Trend Line

Lessons learned

1. Excel doesn’t always provide an in-built means of achieving what you want to do.
2. VBA usually provides a way round Excel’s shortcomings.
3. You have a useful new method of presenting trends in data.
4. Your trend lines are not limited to bars – you can use other characters as you see fit.
5. The Excel colour picker dialog is quite simple to implement.

Further reading

• Font.Color Property
• Font Object Members
• Excel Object Model Reference

Over to you

Please leave a comment if you can think of any other uses for this functionality or improvements to the code. Have you seen this technique used anywhere else or in different ways? Please let me know in the comments. I am not a professional programmer and only do it to save me time in the long run or when Excel annoys me. Or, occasionally, just for laughs.