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


  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?
    Surrey, BC

    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!

    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.

    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.

    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.

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

  3. It's too late to pick your tree this year, but before you gucci replica haul yours outside for curbside pick-up (or back to the attic for storage), replica louis vuitton take a minute to consider your 2010 options. Fake tree fans argue that their version can be reused again and again each year, louis vuitton replica thus sparing trees from being chopped down for a mere month or so dior replica of enjoyment. Real tree users however remind dior replica us that only their version can be recycled, thus sparing landfills from getting clogged with plastic memories tag heuer replica of Christmas past. We decided to investigate the environmental impact of each type, and while we're at it bell ross replica, encourage one uber-green option (living tree rental) that West Coasters are already all over iwc replica. With more support, we'd love to see this choice spread nationwide.

  4. Did you know that replica handbags Designer originated in the year 1925? A sweet husband and his wife made the decision to get started a little store in Italy specializing in little leather goods and furs. In the yr 1965, Fendi handbags became well-liked and Fendi replica louis vuitton employed a genius of a guy who was a young designer from Paris.The credit score for designing Fendi's double "FF" emblem goes to Karl Lagerfeld.The logo was precisely what Fendi essential to make its mark in the vogue world. You will have to shell out anywhere in between 200 and 2000 if you wish to personal a Fendi designer handbag, based on which accessory you choose to buy.In a somewhat ironic twist, the R4 staff has announced on GBAtemp that from September 15th onwards, R4 firmware updates might brick your DS. Why have they been upto Discount gucci replica belts this? Why is it that for an extended time no firmware update has come out? Do the R4 team dislike us? WHHY, R4 group, Why?Most discounted handbags that are sold would be the outdated variations of the bags. Given that designers always have to make a new line every time, there are some variations which are previously regarded previous. What is far more crucial is that these designers are ready to keep up with the trends so they continually make new handbags ever so often. These Chanel replica bags are typically sold at clearance sales that are priced as much as 75% off the unique price tag.Ahead of getting a bag on e bay go to their Neighborhood Chat Space. There's a good results of information on how to detect counterfeit bags. Even an e bay "powerseller" can be delivering fake bags. When e bay is alerted about a specific vendor it could get them some time to examine. All through the time it will take for your investigation the seller may have presented a great deal of counterfeit bags. E bay has shut down dealers advertising counterfeit bags, but even e bay can't shield you from all the replica wallets unscrupulous sellers on the industry.Consider a search at on line sites that promote luxe designer handbags and you'll see there are a lot of bargains to be identified. I've witnessed designer handbags from Dolce & Gabanna, Fendi, Zac Posen an Dior at cost savings of up to 50%. These handbags will even now run you about $600 or more at that cost savings, but if you can even now afford it, it's a bargain.

  5. These appointments are effective rolex replica as of Oct. The Fashion and Leather Goods division includes Louis Vuitton, Celine, Loewe, Kenzo, Givenchy, Christian Lacroix, Fendi hermes replica and Pucci. LVMH is also present in the Fragrances and Cosmetics sector with Parfums Christian Dior, Guerlain, Givenchy and Kenzo, and replica watches has recently acquired five promising cosmetic companies, Bliss, Hard Candy, BeneFit Cosmetics, Urban Decay and MAKE UP FOR EVER. LVMH rolex replica is active in selective retailing through DFS, Sephora, Le Bon Marche and Thomas Pink. The Group has established a Watch and Jewelry division comprising bell ross replica TAG Heuer, Ebel, Chaumet, Zenith, iwc replica Fred, as well as Omas, the prestigious Italian writing instruments company.

  6. Your article is very helpful, thank you
    I like jogos barrie l Download now baixar facebook gratis