Titles on charts are not always necessary but when you use them, you will have many, many choices on formatting of which you may not be aware. In this post, you will learn how to make your chart titles show more information to really make your Dashboard charts pop with 2 different techniques.
For this posting, I will use the following data and layout.
Here is the 1st Technique:
1) Create a quick chart with any random numbers for Quarter 1 through Quarter 4 on a spreadsheet in cells A3:E7.
2) Now highlight that data range (A3:E7) and create a Stacked Column Chart. If you used the same numbers as I used, then your chart will look like this:
3) Now add the chart title by clicking on the chart and adding it from the Layout:
4) Now lets link the Title to any cell that is NOT in your original data range. In my case, I am going to link it to cell B1. If you don’t know how to do this, check out this blog post and video:
How-to Make an Excel Chart Title Change Dynamically
5) Now lets take this concept even further by creating a text formula in the title spreadsheet cell. What I want to do is have the Chart Title show dynamically which category has the highest value.
So, in cell B1, I am going to put in the following formula:
=”Annual Pet Store Sales”&CHAR(10)&E3&” Best Seller : “&INDEX($A$4:$A$7,MATCH(MAX(E4:$E$7),$E$4:$E$7,0),1)&” (“&MAX($E$4:$E$7)&”)”
Here is what the formula is doing. It is concatenating the text along with the name of the best seller and the value.
If you don’t know why I am using this formula instead of Concatenate, check out these post:
How NOT to use Concatenate Function to build Dynamic Text in your Excel Dashboard Templates
VLookup Week at ExcelDashboardTemplates.com
Excel VLOOKUP–Left or Right and HLOOKUP Up or Down
Here is what the formula will look like as a chart title:
Now that is really how to make a dynamic dashboard title.
Here is the 2nd Technique.
I really don’t like the way the chart title appears in the chart. It is just one big block of text. I really like the way the Wall Street Journal charts have a title in bold and a subtitle in non-bold and a smaller font. Here is a sample of what I mean:
So I really like to do this in my dashboard charts. So how do you do this?
Click on the title, then click on the title again in the subtitle area (the second line):
After you have selected the subtitle text, you can change the font to a smaller number than the original title by going to the Home Ribbon and selecting a smaller font like 14:
and then unclick the Bold icon:
Here is what the final chart could look like with a Bold title and a non-bold and smaller font subtitle:
There you go, 2 techniques to make your Dashboard Chart Templates pop for your Company.
Please let me know in the comments how else you like to modify your chart titles and other tricks or tips.