Excel Tip: Using formulae in text boxes and chart titles

Do you have charts with titles that regularly need to be changed? Or text boxes in diagrams that need to be edited to reflect the numbers shown?

It is really easy to have these update automatically, but it is not obvious how you are supposed to do it in Excel.

Say you have several charts that feature in a monthly pack and each chart refers to the current month in its title. "Cumulative Profit to November 2014" for example.

In a normal cell we can create this sentence in a formula. If A1 contains the period-end date, then we can use the following:

="Cumulative Profit to "&TEXT(A1,"mmmm yyyy")

Unfortunately, we cannot just type a formula like that into a chart title or a text box,  but we can do the next best thing.

We could type our function above into, say, cell B1, then go to edit the chart title or text box (so we can see the cursor ready for us to type). We can then click in the formula bar and type =B1. The chart title or text box will then always show the contents of cell B1.

No more editing charts each month!

We can use exactly the same technique in a text box, which might refer to this month's sales figure (held in cell A2). Our function would be something like:

="Your sales this month are "&TEXT(A2,"£#,0")

Once the text in our charts and diagrams is automated like this, they can simply be an output from the numbers, requiring no manual input.

Addendum: There is a little oddity when it is a chart title (not a text box). If the reference is on the same sheet, it must still have it's full name, e.g. =Sheet1!A1 rather than =A1. This is not an issue when the data is on a separate sheet.




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".