A great way to make your charts and dashboards more usable and include dynamic titles, legends, and text is with a combination of the TEXT and concatenation. Here are some examples,
One of our readers, Joe, added a dynamic title to the scrolling project timeline described in Creating Dynamic Excel Timelines that Scroll. His cell formula was,
=”Timeframe: “&TEXT(H5,”mmm dd”)&” – “&TEXT(H16, “mmm dd”)
This uses the text, Timeframe, and concatenates it with the first and last dates used in the scrolling timeline. Pretty cool. Thank you Joe for sending in this idea!
Another example is a related post, Creating Dynamic Excel Chart Titles that Link to Worksheet Cells.
You can make almost any text dynamic so it changes as data and dates change. Here’s an example of creating a dynamic legend in the post Creating Dynamic Excel Chart Legends that Link to Worksheet Cells which produced this example,
It’s easy in Excel to combine text, numbers, or dates. Use the TEXT() function to convert numbers or dates into text, then use the concatenation symbol, &, to join the textual numbers or dates with real text. The numbers, dates, or text can be explicitly written in the function or referenced in a cell or formula.
For example, if C12 contains the text, “Today is”, and today is April 1, 2016 then
=$C$12&” “&TEXT(TODAY(),”ddd mmm d, yyyy”)
Returns to the cell containing the formula
Today is Wed Mar 4, 2015
Use the concatenation character, &, to join text, numbers and dates. This enables you to easily put numbers and dates in titles, legends, labels, and numeric report layouts.
If you do a concatenation and you see your dates appear as a number like 42342, then you are seeing the serial date number Excel uses to identify dates. You need to enclose the date reference in the TEXT() function and specify a date format to make it appear correctly.
See these related links,