Creating Dynamic Excel Chart Titles that Link to Worksheet Cells

Charts

You don’t have to settle for Excel’s static chart titles. Your Excel chart titles can be dynamic and link to cell contents. When the worksheet changes, the title changes. They can even have multiple lines that include dynamic data.

Dynamic Excel chart titles link to cells and show multiple text lines.

Dynamic Excel chart titles link to cells and show multiple text lines.

Link Chart Titles to a Worksheet Cell

It’s not hard to link worksheet cells to a chart title. Here’s how to do it,

1. Create chart from data in B7:D13 and deleted background lines for clarity.

Create a dynamic Excel chart title on any Excel chart.

Create a dynamic Excel chart title on any Excel chart.

2. Add a title by choosing Chart Tools Layout tab > Labels > Chart Title > Above Chart.

Start by adding a static title to the Excel chart.

Start by adding a static title to the Excel chart.

3. Select the Chart title object by clicking on it. Do not type a new chart title.

4. While the title object is selected, click in the formula bar and type an equal sign, = , then click on cell F2 containing the title in the worksheet and press Enter.

With the title selected create a formula that references a cell.

With the title selected create a formula that references a cell.

5. The worksheet title will update to show the cell content.

Try changing the cell content and you’ll see the title change.

But, wait! There’s more…

Using what you’ve just learned you can link the chart title to any cell content. In some older versions of Excel you can even enter complex formulas in the title object’s formula bar. However, some versions will not accept a complex formula for the title. But, there’s a way around that shortcoming.

To create fancy dynamic titles that work with any version of Excel follow these steps,

6. In cell F4, enter the following formula,

=$F$2&CHAR(10)&TEXT($F$3,”mmm d, yyyy”)

The result in cell F4 from this formula should look like,

Regional RevenueApr 20, 2014

There should be no space between Regional Revenue and the date. That “no space” is where the CHAR(10) is and this hidden space actually contains a little magical that does show up in the chart.

7. Select the chart’s title again and in the formula bar, type an “=”, and click on cell F4. Again, this will link the chart title to the cell F4. The formula should look similar to this,

=’Dynamic Chart Titles – Dates’!$F$4

8. Press Enter and you’ll see a dynamic title that changes as cell F4 contents change. This title should also contain a line break because of the CHAR(10) in the formula.

Click the picture to expand it to see more detail.

Dynamic Excel chart titles link to cells and show multiple text lines.

Dynamic Excel chart titles link to cells and show multiple text lines.

Here’s the formula in F4,

=$F$2&CHAR(10)&TEXT($F$3,”mmm d, yyyy”)

The & (used as a shortcut for the CONCATENATE function) joins together cell contents and turns the result into text. CHAR(10) returns the Line Feed character from your computer’s standard character set. This is a character used by printers, but here it works to create a line break.

The TEXT function used in the formula converts the numeric date stored in the cell into a text date with a format like that shown within quotes. The TEXT function is very handy whenever you’re creating reports in Excel that need to combine text with a currency or date in a special format. There are a bazillion different formats you can create with TEXT. If you want some examples of valid ways to specify formats in TEXT, right click on a cell and choose Format Cells, then look in Custom Category of the Format Cells dialog box for examples.

Use the TEXT function and custom formats to create any number, currency or date format you want.

Use the TEXT function and custom formats to create any number, currency or date format you want.

Notice that within the TEXT function the custom format must be inside quotes. You can create many custom date, currency, and numeric formats using these special characters with the Format Cells dialog or within the TEXT function; however, that is a topic for another article.

 

Share the power...