Create dynamic chart titles with custom formatting

Titles are an overlooked aspect of many charts.  A bland title misses out on so much rich information which could be used to enhance a users understanding of the report.

Here is an example of bad and good chart titles:

The 2nd chart title adds additional insight and provides a much richer experience for the reader.

When creating static charts, this type of insight is no problem, but in a world of dynamic charts, it becomes very tricky.  This post considers these issues:

• Manually creating a chart title with custom formatting
• Dynamic chart text
• Dynamic chart title with custom formatting

Manually creating a chart title

When creating a chart, the title is automatically set as either the series name or the text “Chart Title”.  To change the title to something more meaningful, click the chart title (the title will highlight).

Type the text you wish to be displayed directly into the Chart Title.  Highlight sections of the text and apply standard text formatting from the Home ribbon.

Once finished, the chart title could look something like this:

Dynamic chart text

If the chart is dynamic, the text within the Chart Title should also be dynamic; the text it needs to change to reflect the data presented.

The difficulty is creating dynamic text which makes sense in all circumstances.  To do this we will makel use of three Excel features:

• Concatenate strings with the “&” symbol
• TEXT function
• Linking title to a cell value

Concatenate strings with the “&” symbol.

Within Excel, we can combine text into a single cell with the use of the & (ampersand) symbol. Here is a basic Example.

="Our Company controls " & A2 & "% of market share."

Assuming the numerical value in Cell A2 is 29, the result of the formula will calculate as:

Our Company controls 29% of market share.

TEXT function

The TEXT function in Excel is used to convert numbers to text using Custom Number Formatting.   Following on from the last example, if Cell A2 had a value of 29.578362, the output would be:

Our Company controls 29.578362% of market share.

That is probably too much accuracy, right?  This is where the Text function comes in.

 ="Our Company controls " & TEXT(A2,"0.0") & "% of market share."

The text function converts 29.578362 into a number with one decimal place, 29.6.  The TEXT function can use a variety of number formats, unfortunately, that is outside the scope of this post, but well worth investigating.

Link chart title to a cell

Once we have a cell containing the text we want to display, we just need to link the chart title to that cell.

Click the chart heading, in the formula bar type an “=” (equals) symbol followed by a reference to the cell which contains the text.

With these features combined, we can create a rich dynamic text heading.

Dynamic chart title with custom formatting

How can we apply the same custom formatting to the dynamic title as we can to the static title?  This is where things start to get hard.

We are going to use a formula on the worksheet.  But not any formula, a special formula which we will build ourselves with VBA.  This is called a User Defined Function (or UDF for short). Usually, UDF’s are used to calculate a cell value, but they can also control certain objects, such as chart titles.

For additional guidance on creating and using UDF’s please refer the last week’s post, which explained how to set the chart axis min and max values based on a cell.

Create a User Defined Function

To create a UDF click Developer -> Visual Basic (or shortcut ALT + F11).

The Visual Basic Editor window will open, click Insert -> Module.

Add the code to the Module as shown below.  The code for the UDF must be within a standard module to work correctly.

There are lots of comments inserted into the VBA code to help you can understand how it works.

The VBA Code

Option Explicit

Function FormatChartTitle(chartName As String, titleText As String, _
    defaultTextStyle As Range, powerWords As Range)

'Recalculate the formula every time
Application.Volatile

'Create a variable to reference the chart
Dim cht As Chart

'Create a variable to reference each cell in the powerWords range
Dim c As Range

'Create variables to loop through the text
Dim startPosition As Integer
Dim chrPosition As Integer

'Set the cht variable equal to the named chart on the same
'sheet as the formula
Set cht = Application.Caller.Parent.ChartObjects(chartName).Chart

'Turn the chart title on
cht.HasTitle = True

'Change the chart title to the text in the cell
cht.ChartTitle.text = titleText

'Reset the text in the chart to be the same as the defaultTextStyle
With cht.ChartTitle.Format.TextFrame2.TextRange.Characters.Font
    .Fill.ForeColor.RGB = defaultTextStyle.Font.Color
    .Bold = defaultTextStyle.Font.Bold
    .Italic = defaultTextStyle.Font.Italic
    .Name = defaultTextStyle.Font.Name
    .Size = defaultTextStyle.Font.Size
End With

'Loop through each cell in the powerWords range
For Each c In powerWords.Cells

    'Start searching for matching words at first position
    startPosition = 1
 
    'Keep looping until no more matches can be found
    Do While InStr(startPosition, cht.ChartTitle.Characters.text, _
        CStr(c.Value), vbTextCompare) > 0

        'Search for the text in the powerWords range
        chrPosition = InStr(startPosition, cht.ChartTitle.Characters.text, _
            CStr(c.Value), vbTextCompare)
 
        'If a match is found, format that text
        If chrPosition > 0 Then
 
            'Format the sections of the chart title which match the
            'powerWord range
            With cht.ChartTitle.Format.TextFrame2.TextRange. _
                Characters(chrPosition, Len(c)).Font
                .Fill.ForeColor.RGB = c.Font.Color
                .Bold = c.Font.Bold
                .Italic = c.Font.Italic
                .Name = c.Font.Name
                .Size = c.Font.Size
            End With
 
        End If
 
        startPosition = chrPosition + 1
 
    Loop

Next c

'Return the result of the chartName
FormatChartTitle = chartName & " - Formatted"

End Function

That’s all we need to do to create the UDF.  It’s ready to be used.

Using the User Defined Function

The UDF uses the following syntax:

=FormatChartTitle(chartName, titleText, defaultTextStyle, powerWords)

• chartName = the name of the chart as a text string, or a cell reference.  If you’re not sure what its name is, click on the chart and look at the NameBox (the box to the left of the formula bar).
• titleText = can be either a cell reference to a text string, or the text string itself.
• defaultTextStyle = reference to a cell containing any text formatted in the same style as the default chart title should be.
• powerWords = reference to a range containing a list of words formatted in the say way as they should be formatted in the chart title

A UDF must return a value into the cell in which it is used.  As we are using the formula to control a chart, the output of the formula serves no purpose.  In our code, it is set to return the name of the chart into the cell.

The screenshot shows the formula being used.

Cell J17 contains the following formula:

=FormatChartTitle("Chart 3",B22,I4,I5:I13)

Notes

The dynamic heading with custom formatting is reasonably experimental. I have not tested this extensively, so please let me know of any issues you encounter with using this method.

The list of powerWords does not need to be a static list, it can also be the result of formulas.  In the example file, Cell I12 is a formula, this ensures the market share % is formatted correctly within the dynamic heading.

The order of the powerWords list is important.  The last instance of each text string will be the formatting style applied.  If the words “increase” and “in” are both powerWords in that order, the “in” at the start of “increase” will be formatted differently to the “stance” of “instance”.  However, if the words are in the opposite order, “increase” will be the final format.

The UDF above applies the color, size, font name, bold and italic settings.  If you wish to add other formatting the UDF will need to be enhanced.

---

What next?

Discover how you can automate your work with our Excel courses and tools.