• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Dynamic Text Labels

You are here: Home / Excel Formulas / Excel Dynamic Text Labels
Excel dynamic text labels
February 17, 2016 by Mynda Treacy

I use Dynamic Text Labels all the time, whether it’s in a Dashboard report or chart title, and even to annotate variances like you'll see in this example.

Anyone can create an Excel chart or report but it doesn't mean your reader will get the message you're hoping/trying to convey. With a dynamic text label you can automatically highlight the key points so it's not left to chance.

What is a Dynamic Text Label?

You create Dynamic Text Labels with formulas that join together text and (typically) the result of other nested formulas (the dynamic part). Let’s look at an example.

In the image below cell C3 contains my dynamic text label. I used a formula to find the last month in column B that has an Actual value in column D, and the Cumulative Variance from column F, and then constructed the text string you see in cell C3: “September variance to Date: $9k”.

Excel Dynamic Text Labels source data

When October actual data is added to the table my dynamic text label will automatically update without me having to do anything:

Excel Dynamic Text Labels update

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Dynamic Text Labels in Excel Charts

Dynamic text labels are handy in a range of applications. Like I mentioned, I typically use them in report or chart titles, as shown below:

Excel Dynamic Text Labels as chart title

I also like to use them as an alternative to plotting variances on a secodary axis.

An aside; I personally try to avoid secondary axes because they make the chart slower to interpret as the reader has to figure out which series uses which axis.

Link Chart Title to Dynamic Text Label:

  1. Add a title to your chart
  2. Click on the chart title box
  3. While the title box is selected click in the formula bar and type the = sign
  4. Click on the cell containing your dynamic label formula with your mouse
  5. Press ENTER

Note: You must enter your formula in a cell and then link that cell to the chart title. You cannot put a formula in a chart title, or any other text box or Shape for that matter.

Dynamic Text Label Formula

Here is the formula I used (don’t’ be put off…I’ll explain it 🙂 ):

=TEXT(INDEX(Table1[Month],MATCH(1E+100,Table1[Actual $k],1)),"mmmm")

&" variance to date: "&

TEXT(INDEX(Table1[Cumulative Variance $k], MATCH(1E+100,Table1[Actual $k],1)),"$#,###0k;-$#,##0k"))

Note: the formula above uses Excel Table Structured References instead of regular cell references.

The formula is doing 3 things (hence the colour coding):

  1. First it uses INDEX & MATCH to find the name of the month where we have the last Actual value in column D:
TEXT(INDEX(Table1[Month],MATCH(1E+100,Table1[Actual $k],1)),"mmmm")

The MATCH formula looks for value 1E+100, which is scientific notation for a very big number. MATCH will never find that number in the Actual column of my table, and because I’ve set the last argument (match_type) to 1, which means ‘less than’, it simply returns the row number of the last value it does find.

That is, it looks for 1E+100 in column D, it can’t find it so it returns the row number for last value it finds and then INDEX returns the month from column B on that row. See, easy 🙂

Lastly, because the months in column B are actually dates, and in Excel dates are numbers, I need to convert the month returned by INDEX to text because after all, I’m building a text string. So the TEXT function wrapped around INDEX & MATCH tells Excel that I want it to format the date it returns as text, with date formatting “mmmm" applied. This gives me “September”.

 
  1. Next I join the text, “ variance to date: “, using ampersands:
&" variance to date: "&
 
  1. Lastly I use INDEX & MATCH to find the cumulative variance from column F using a similar formula to the first part:
TEXT(INDEX(Table1[Cumulative Variance $k], MATCH(1E+100,Table1[Actual $k],1)),"$#,###0k;-$#,##0k")

Then I wrap INDEX & MATCH in TEXT to tell it that I want the variance figure formatted as $#,##0k, which returns $9k.

And altogether I get:

September variance to Date: $9k

It might seem like a lot of work just to get “September variance to Date: $9k”, but remember we only have to do this once and then next month and the following month it’ll automatically update.

And bear in mind that this dynamic text label has two dynamic parts; the month and the cumulative variance, but they don't have to be this complex so you can modify the formula as required.

Functions Used in this Tutorial

INDEX & MATCH

If you haven’t made friends with INDEX & MATCH yet then I encourage you to master them because once you do you’ll see they’re not that tricky and they open up a lot of Excel opportunities.

TEXT Function

The TEXT function isn’t limited to the examples used here, there is a huge range of applications for it and it’s a great function to have in your Excel tool belt.

Other Ways to Display Variances

Custom Chart Labels

Emoticon Roller Coaster Chart

Excel dynamic text labels

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel Formulas
Previous Post:Ugly Excel ReportPower Query Reformats Ugliest Report Ever
Next Post:Excel Alternatives to Radar ChartsExcel Radar Charts

Reader Interactions

Comments

  1. Col Delane

    May 26, 2016 at 6:11 pm

    I agree Mynda – dynamic labels are very useful.
    I use them for similar purposes, as well as for on-sheet instructions (e.g. =”Insert rows above row ” & ROW(A123), and often incorporate TEXT functions such as LEFT, RIGHT, MID, etc. to extract the bits required – it’s just a pity that they can’t be used to create Field headings in Excel (Structured) Tables.

    You could also use COUNT( Table1[Actual $k] ) to return the index position of the latest month containing an Actual value in column D.

    Reply
    • Mynda Treacy

      May 26, 2016 at 6:36 pm

      Nice tip, Col. I like it.

      Reply
  2. Nate O

    February 24, 2016 at 3:50 am

    Very clever, thank you Mynda!

    Reply
    • Mynda Treacy

      February 24, 2016 at 8:06 am

      Cheers, Nate 🙂

      Reply
  3. Ade Orhiere

    February 19, 2016 at 4:04 am

    How did you create your chart the variance bars are very impressive. I looked into chart type and it was shown as template

    Reply
    • Mynda Treacy

      February 19, 2016 at 10:28 am

      Hi Ade,

      Hi Kevin,

      The budget series is a line chart with only the markers displayed. It’s a combo chart (column and line) which I created in Excel 2013, so that might be why it’s showing as a custom template for you.

      Mynda

      Reply
  4. Kevin Zandee

    February 19, 2016 at 1:03 am

    Great example. Is the chart a custom template? I would like to know how to create it.

    Reply
    • Mynda Treacy

      February 19, 2016 at 10:26 am

      Hi Kevin,

      The chart is just a combo chart; column (Actuals) and line (Budget). The line chart is only displaying the markers for the line. I created it in Excel 2013 so that might be why it’s showing as a custom template.

      Mynda

      Reply
  5. Wanda

    February 18, 2016 at 10:00 pm

    Why did you use “$#,###0k twice? “$#,###0k;-$#,##0k

    Reply
    • Mynda Treacy

      February 18, 2016 at 10:05 pm

      Hi Wanda,

      The first format is for positive values and the second format is for the negative value.

      Mynda

      Reply
      • gino

        January 11, 2018 at 11:33 pm

        I believe there is an extra “)” in the formula as shown in the post at the very end. One “)” at the very end of the formula works.

        This gives an error:
        =TEXT(INDEX(Table1[Month],MATCH(1E+100,Table1[Actual $k],1)),”mmmm”)

        &” variance to date: “&

        TEXT(INDEX(Table1[Cumulative Variance $k], MATCH(1E+100,Table1[Actual $k],1)),”$#,###0k;-$#,##0k”))

        Reply
        • Mynda Treacy

          January 12, 2018 at 10:56 am

          Well spotted, Gino. I’ve edited the post to remove the extra rogue “)”

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.