Search Not Just Numbers

Tuesday 18 November 2014

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".

6 comments:

  1. Hi Glen,
    Some help please. When I created a simple new graph to test, the Chart Title box disappears when I click in the formula box and I get an error message "References in series formulas must be external references to worksheets." Are we missing something else?
    Thanks,
    Carl
    Surrey, BC

    ReplyDelete
    Replies
    1. Carl

      You've just reminded me of an 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.

      Sorry about that, I will add an addendum to the post too.

      Thanks for pointing it out, I normally have my data on a separate sheet, so had forgotten all about that!

      Delete
    2. Hi Glen,
      Unfortunately I now have a chart title =Sheet!A2 I appear to be missing something else. I've tried " " in different positions but this only added to the above.
      Thanks,
      Carl

      Delete
    3. Hi Glen,
      I just realized I missed the "1" in the reference =Sheet1!A2. The title I created now shows in the box! However, new problem, I have the title I created plus "Chart Title" above it like this:

      Chart Title
      Cumulative Profit to Nov 18, 2014

      How do I get rid of this box name? I can't find a solution.
      Thanks,
      Carl

      Delete
    4. Ok, embarrassing ... I somehow added 2 chart title boxes - all is good now.

      I noticed that Excel automatically added $ to display in the formula box as =Sheet1!$A$2 and I can't get rid of them. I was concerned that creating a copy of this tab would result in the date from Sheet 1 carrying forward to other sheets but it does not. The formula reference is updated to the new sheet and cell A2.

      Thanks for the tip.
      Carl

      Delete
  2. Carl - sorry I missed your series of posts last night. I.m pleased you got there in the end!

    ReplyDelete