Painless Chart Title Updates

I originally called this post “Dynamic Chart Titles in Excel”.

SEO-friendly, perhaps.  But it could have been catastrophic.

In weaponized form, that five-word combination could unleash a wave of drowsiness not seen in modern times.  Even in an 11-point font, it will cause you to blink for a second or two longer than normal.

Use with caution!

This post takes the tedious task of updating an Excel chart title and makes it less painful.  Typically, we send the same chart to the same people day after day after day after day…

You get the point.

To get us started, we’ll use a sample chart looking at my daily steps so far in August:

I want to track my daily step average and maximum step count for the month.  I also want the chart to update when I add more data.  The axis titles, “Steps” (y-axis) and “Date” (x-axis), stay the same but my chart title will change every day I add more information.

Doing this manually gets very old very quickly.

Any data moves require a micro-adjustment to the title.  No biggie until you realize that three minutes of daily tweaks costs you 12.5 hours of labor per year.


No need to fret, however.  We can do it automatically.

Building the statistics

To start, we need to determine the month’s start date, last day of data entered, average steps for the month, and month’s maximum steps.

Briefly reviewing these formulae:

  • Month start date:  EOMONTH(DATE, 0) returns the last day of DATE’s month (e.g. 7/5/2020 will yield 7/31/2020).  EOMONTH(DATE, -1) yields the last day of last month.  By adding 1, we get to the start of the current month!
  • Last date entered:  Excel converts dates into the number of days after January 1, 1900 (or 1/1/1904 for some Mac users).  The largest number is the most recent date, hence the use of MAX().
  • Avg. steps in month:  AVERAGEIFS() will average together all values in Column B with corresponding values, i.e. in the same row, that satisfy all conditions of the expression.  For example, B2 (value of 5000) will only be included if A2 (value of 8/1/20) is between the start of the month (D16 or 8/1/20) and the last date entered (D17 or 8/6/20). Since it satisfies all conditions, it is averaged in.
  • Max. steps in month:  Similarly, MAXIFS() finds the largest value of Column B with corresponding dates between 8/1/20 and 8/6/20.

Building the title

Now, we can create a descriptive title that updates as we add more data by combining three separate lines:

  1. August Steps – Through 8/6/20
  2. Average = 6567 steps
  3. Max = 9000 steps

The TEXT() function and ampersand “&” will come in handy.  Observe:

For the first title line (cell D21), we use TEXT() in two ways:

  1. TEXT(D17,”mmmm”) converts “8/6/20” into month’s full name, i.e. “August”
  2. TEXT(D17,”m/d/yy”) maintains the “m/d/yy” format in the formula result.  Without it, “44049” would appear in its place.

For more details on date formats used with the TEXT() function, check out this post.

Using “&” we can stitch together “August” with “ Steps – Through “ and “8/6/20” to complete the line.  Keep in mind that spaces are important in the connecting text.  Sans spaces,  “AugustSteps-Through8/6/20” borders on unreadable.

TEXT(D18,”0”) in cell D22 and TEXT(D19,”0”) in cell D23 keep each formula result as an integer for tidy reporting.  Without it, August’s average becomes 6566.66666666667 steps, an impressive (though ridiculous!) display of accuracy to within 10 picosteps.  “Average = “ and “Max = “ are tied with their corresponding statistic with “&” to complete each line.

To finish the title, we use “&” to combine these three lines (i.e. D21, D22,and D23) with line breaks (a.k.a. carriage returns) as shown in Cell M25 below (bordered in red).  Hold down the “Alt” key and then press Enter” to insert the carriage return.  (Akin to “Shift+Return” used in several apps to insert a line without sending a message.)  This keeps the title clean with each statistic on a separate line.

Cell D25 (bordered in purple) is the output, though there is no evidence of carriage returns.  That’s ok.  We will see them in a second.

Our chart is now ready for the final step!

Automating the title

Select the chart title’s text box and click in the formula bar toward the top of the page.  Instead of typing in text, select cell D25 to reference our well-crafted title!


Now comes the fun part.  When we enter the next day’s data, the title updates automatically!  Let’s try 10,000 steps for August 7th and see what happens:


All our stats updated, including the new max steps for August, just by adding the data. A couple extra minutes of work should save you a ton time going ahead!

Thank you for reading! Feel free to add your comments below – what you liked or other Excel tricks worth learning about.

Shout out to Megan, Tyler, Joel, Eugene, Tom and colleagues at Compound Writing for your feedback!

Similes and Alt+Esc

Occasionally, from the mind fog of an early morning, a jumble of words makes its way out of my subconscious.  Observe:

He has the charisma of a bowl of shredded wheat.

Sadly, Ron Weiskind of the Pittsburgh Post-Gazette can lay claim to an earlier rendition of the phrase.  Curse you Google…  As useful as a simile, as they say.

Moving on.

I had an issue with an Excel program today .  A VBA script that wouldn’t die even though I “Ctrl + Break”ed (or is that “Ctrl + Broke” it?) a few hundred times.  Normally, I would restart Excel, but I would need to re-run the program for another 20 minutes to get the output I was looking for.  Google came to the rescue this time.

This post saved my bacon.  “Alt” + “Esc” caused my computer to flash between all of the open programs, but within a few seconds the program stopped its endless loop and brought a welcome sight:


“Alt” + “Esc” is a different/faster version of “Alt” + “Tab” to switch between open applications.  It helps to switch between Word or E-mail or Excel or your web browser without having to use the mouse while adding to your geek cred.  Less mouse equals more better.  True story.


12/27/2015 = 42365?

Happy third day of Christmas!  ‘Tis the season for celebrating with friends and family, a bit of introspection, and a good a cup of coffee.  Best wishes to you and yours!

And now, for something completely different.

What is 12/27/2015?  My calculator tells me 0.000220567962503446.  My calendar tells me there are four days left in the year.  Half of the world’s population wonders what the 27th month of the year is.  As for Excel, it tells me it’s equal to 42,365. Of course it is…

Day 1, according to Excel, is January 1, 1900.  Day 42,365 is December 27, 2015, by Excel’s reckoning, though that’s not quite right.  Apple fans have a different quibble.

Converting the date into a number is useful for sorting data and for making calculations based on dates, but it’s a bit frustrating when you expect to see 12/27/15 and get 42,365.  This can be easily remedied by tweaking the cell’s format.

“Ctrl” + “1” opens the “Format Cells” dialog box:

Format cells.png

Excel recognizes 12/27/2015 as a date, and represents it as such.  Don’t like it?  Pick another format!  “OK” makes the update.

A different way to format dates on the fly is with the TEXT() function.  Here are a few examples:

Several versions of the 3rd day of Christmas.  Formulas (Col. A) with output (Col. B)

By using TEXT(), we can change how the date is displayed without changing the format of the cell.  This also forces Excel to maintain your desired format even when it wants to change it for you.

TEXT() comes in handy for creating chart titles, but I’ll save that for another day.  As for now, I need to figure out what to do with these three French hens from my true love…

Control + Arrow

An easy way to appear to have ninja skills in Excel is to skip around a spreadsheet without a mouse.  With this easy trick, you can channel your inner Kal-El and leap 16,833 columns or 1,048,575 rows in a single bound.

  • Open a new spreadsheet
  • Hold down the “Ctrl” button (and don’t let go!)
  • Hit the right arrow key “→”
  • Hit the down arrow key “↓”
  • Hit the right arrow key “←”
  • Hit the right arrow key “↑”

Here’s an animation of what it should look like:

Run circles around your spreadsheet in under a second

Once through the procedure, you should be back to cell A1 (as a bonus, you also have 30 lives). Note that you can release the “Ctrl” button between moves, but it must be held down before pressing the next arrow key to make the next jump.

I’ll explore this trick further in spreadsheets with data as this comes in handy when working with blocks of information.