(C) 2009 - 2012 by Mourad Louha · All rights reserved
Imprint

Dynamic Excel chart with dynamic title based on cell selection

This following post is a guest post and was written by Roberto Mensa, Krisztina Szabó and Gábor Madács. Thanks Roberto, Krisztina and Gábor :-)

A few days ago we were working with the CELL formula to draw your attention to use the English info_type attribute if you share your Excel file with someone how uses Excel with different language setting. The second argument of this formula is the reference, which is optional. If it is omitted, the information is returned for the last cell that was changed, as we can read in the Excel Help.

Dynamic Chart in Excel

It is important to make it clear that “last cell that was changed” relates to the calculation. Change means a kind of change that triggers calculation. So if you manually re-calculate your sheet, the “last cell” will be the active cell where your cursor is positioned. My Hungarian friend Krisztina Szabó gave me an idea to build a dynamic chart based on the active cell. So we made a challenging example. In this article, we would like to show you how you can build up a similar solution based on our tricks. We have provided a sample file, which you can download at the end of this article.

Here we need to mention that there are some formula name “spelling” issues in case you assign the names to chart objects via the formula bar. Please read the note at the end of this article about the details.

Example – Quarterly sales by vendors

As an example we use a table with quarterly sales data of 9 vendors. Create a simple chart for Vendor_1 as you see below. You can delete the legend, we will not need it now. We will simply change the chart using some names and named formulas to make it dynamic. It will be a row-based dynamic chart to show vendor sales by quarters.

Dynamic Chart in Excel

Creating support formulas

First of all you need to name your input data table. We will use the name: Input_area only for the data part, without including row and column headers. You can set it directly on the worksheet by selecting the range from B2 to E10 and typing the name “Input_area” to the Name Box, as you see on the picture below, or you can choose the Name Manager and create a New Name.

Dynamic Chart in Excel

We are building a row-based dynamic chart, so we need to determine the active row within the Input_area table. First we determine the number of this row, after it we will index the data table to read the data from this row – this data will be used in our chart.

The CELL function with “row” argument but without second argument will give us the number of the actual row, the row of the active cell. If this row is outside of the Input_area, we need to modify it: in this case we will use the first or the last row of the table. For this purpose we use the MEDIAN function with 3 arguments:

  • row number of the active cell: CELL(“row”)
  • first row of the data table: CELL(“row” , Input_area)
    here we use the CELL function with Input_area range as second argument. CELL will return information only about the upper left cell of Input_area – so we have the row number of the first row.
  • last row of the data table: MAX(ROW(Input_area))
    here we take adventage of the special feature of named formulas that they are array formulas. ROW – as array formula – will give us an array containing all the row numbers within Input_area. With MAX we choose the highest number, which is the last row – and that is exactly what we need.

MEDIAN will return the number in the middle of the given 3 arguments – this row is within the data table. You need to use the Name Manager to create the new name “ActiveCellRow” referring to the formula:

=MEDIAN( CELL("row"),
 CELL("row" , Input_area),
 MAX(ROW(Input_area)))

Dynamic Chart in Excel

And now we can index the Input_area to have data from the active row. Create a new name: ActiveRowData referring to formula:

=INDEX (Input_area,
 ActiveCellRow-CELL("row" , Input_area)+1 , 0)

Dynamic Chart in Excel

Assign data to chart series

Right click on the chart area – choose Select Data. You have only one data series: Vendor_1. Select it and click Edit. Update Series Values to ActiveRowData, as you see on the below screenshot. If you need dynamic Legend, you can change the formula in the Series name field.

Dynamic Chart in Excel

Or you can change the 3rd parameter of the SERIES formula directly on the formula bar:

=SERIES(Sheet1!$A$2, 
 Sheet1!$B$1:$E$1,
 Sheet1!ActiveRowData , 1)

And now you can try: when you change the selection, and run a calculation (F9) your chart will be refreshed!

Dynamic chart title

The only problem is that the title of the chart is static. In the next steps we will make it dynamic, based on the active cell. First you need a name with reference to a single cell. You can simply name one of the Vendor cells on your worksheet. Let’s choose A2 and create the name: ActiveTitle.

Dynamic Chart in Excel

We put this name into the chart title using the formula bar. Select the chart title and click on the formula bar. You can choose which method is the most convenient for you:

  • Simply enter the formula with sheet name: =Sheet1!ActiveTitle
  • or after typing the equal sign you can click somewhere on the worksheet and delete back the cell reference, replacing with ActiveTitle.

Dynamic Chart in Excel

You can see that Excel replaces your sheet name with the file name if you use a workbook-scoped name. It is normal. Naturally you can enter the name in this format too, but usually it is more convenient to use the sheet name and let Excel change it. :-)

The last step is to change the name to be dynamic. We can use OFFSET formula to determine the vendor name, because it’s on the left of Input_area. ActiveRowData is the range we want to offset. We are in the active row, so we omit the second parameter. Step one cell left, so the 3rd parameter is -1. And we need only one cell, so obviously the last two parameters are 1. Concatenate a text with the formula to make it clear what we see on the chart:

=”Quarterly sales of “ &
  OFFSET(ActiveRowData ,,-1,1,1)

Go to the Name Manager and choose the existing ActiveTitle – click on Edit and write the formula into the Refers to box as you can see on the picture below:

Dynamic Chart in Excel

If you check the formula bar of the title now, you can see that Excel put the reference between quotation marks:

=”Dynamic_chart_base.xlsm!ActiveTitle”

Automated calculation for selection change

Now it is finished! The chart series and the title is dynamic, based on the active cell! Each time you move the active cell, followed by a recalculation (F9), the chart will be updated. In order to avoid having to press F9 you can add a simple event procedure that will re-calculate the sheet when the selection changes.

Open the VBA editor (Alt+F11) and write “Target.Parent.Calculate” into the Worksheet_SelectionChange event sub of your worksheet.

Please remember, in this case you must save the workbook as Excel Macro-Enabled Workbook – as .xlsm format, and enable macros when you open the file.

Dynamic Chart in Excel

Conclusion

While you read the section about dynamic chart title, you may ask why we create a name with range reference first? Why not create the name for the offset function and assign it to the chart title in one simple step? The answer is very simple: it is not possible. Excel does not accept a name as chart title when this name is not a reference. It can be cell reference as it was when created ActiveTitle first, and can be a formula which gives back reference – for example offset. BUT if you, for example, concatenate it with a string or other cell value, it will be refused.

The only way to use this non-reference kind of name as chart title is to create a name first with reference and add to chart title in the formula bar. After it you can modify the Refers to parameter of the name to a formula as you wish.

Important notes

We have made lot of tests and experienced some problems with names beginning with the letters “c” or “r” – as column and row. For example: rng_mydata, Range1, chart_1, ChartTitle. In your local language Excel, not only these two letters, but your local column and row letters can be problematic as first letter of a name. When you want to use these names on the chart formula bar in the SERIES formula, Excel will not accept them. If you want to use them, you can only change the series in the Edit Series window. Excel also will not accept the names entered on the formula bar for Chart Titles. If you want to use them, the only possibility is to assign them in VBA.

Finally the download link for the sample file and a link to Robertos Blog.

 
Comments
Danièle Questiaux

It is great to be able to pick such elaborate workpiece. It is even when it transpires that the team has had such fun time doing it!
Thanks!
Danièle

Hi Danièle…

Thanks (I think, I can speak in name for the authors) :-)

Best Regards

Yes Mourad, thanks and thanks Danièle …
I, Kris and Gabor have lots of fun to work together … so we created a page on the site where we collect links of the work team:
http://sites.google.com/site/e90e50/documento-plinius/frankens-team
We hope to collaborate again with Mourad … and maybe add a personage to the page :-)
r

Hi r, thanks, I would be glad to be part of the team :-)

Thank you very much! I tried to dynamically link my chart title to a range I had named “chtTitle” but couldn’t make it work. I changed the name as you suggested in your Important Notes and now it is working.

Hi Norm S…

Great that it worked :-)

Regards.

Great work my friends!

One tiny suggestions: I think it would be wise to change the one line of VBA to this:

Target.Calculate

Currently you have the entire worksheet being recalculated for each selection change. In your focused example there would be no difference. But in a practical setting, there will likely be many other calculations on a worksheet and so limiting the recalculation to just the Target range (and its dependencies) will minimize the hit.

You can also highlight the current row of the data with a simple Conditional Format Formula Rule (setting the Fill color to whatever you like):

=ROW()=RowActiveCell

Again, great stuff!

Daniel Ferry
Microsoft MVP

Hi Daniel…

Many thanks, I’ll inform Roberto, Krisztina and Gabor about this comment as I don’t know if they are getting e-Mails.

Regards :-)

thanks Daniel!
your suggestions are always welcome, i always learn a lot
best regards
r

Thank you so much for this awesome tip!! It impresses everyone!! Thank you for sharing and helping all of us out.
Also, a quick question, i’ve tried this with multiple series and had a hard time making it work. Any suggestions? Thanks, again!!

Hi Twee,

as this is guest post, I’ll contact Roberto and ask him, if he could give you some tips here.

Thanks :-)

Thank you Roberto and Mourad for taking the time to respond to my simple query. Roberto, the new examples that you sent are even more sophisticated than I had imagined these charts so it’s definitely inspiring some new chart ideas for me. I was trying to do something much simpler like by adding another line or series for Target by Quarters and will keep trying.

An excellent piece of work; as a finance user it’s great to find an explanation to do what we think is simple yet is quite complicated to achieve.

Mourad Louha

Hi Doald,

Thanks! Best Regards :-)