Microsoft optimize

Create a dynamic Excel chart and make your own dashboard

A dynamic chart reuses the same chart object to chart different source values. You'll save time and your users will appreciate the effort.

Charting data is an easy task and reusing a chart is efficient! By reusing, I mean to use the same chart object to chart multiple sources. In other words, I'm going to show you how to create a dynamic chart. They're common in dashboards and you might be surprised to learn how easy they are to implement.

Instead of launching right into the technique, let's take a quick high-end look at what this technique entails, because it has several steps:

  1. Add a dynamic column to the data range using a HLOOKUP() function. This column will be the chart's real source, not the data range.
  2. Insert a simple chart.
  3. Add a scroll bar control that lets the user easily update the chart's source, without really knowing what's going on behind the scenes.

Now, let's chart the data shown below. Specially, we'll create a simple bar chart that displays the sales for each salesperson, by year. Ordinarily, you might think that you need four different charts, but that's where our dynamic solution comes in- we'll use the same chart object to show all four years.

In the first step, add a column of HLOOKUP() functions, as follows:

  1. In cell F2, enter a year from the headings. For instance, enter 2009.
  2. In cell F3, enter the following function: =HLOOKUP($F$2,$B$2:$E$6,2). Be sure to note the absolute addresses—they're vital!
  3. Copy the function to cells F4:F6. The functions in column F return the appropriate values for the year identified in F2. In the following sheet, that's 2009.
  4. In the copied formulas, update the last argument from 2 to 3, 4, and 5, respectively. (F4 is 3, F5 is 4, and F6 is 5.)

The next step is to insert a chart based on the personnel values in column A and the source column (F), as follows:

  1. Select A3:A6.
  2. Hold down [Ctrl].
  3. Select F3:F6. You just created a non-contiguous selection.
  4. Click the Insert tab.
  5. In the Charts group, click the Bar dropdown.
  6. Choose the first 2-D cluster bar chart option.
  7. In the resulting chart, delete the legend - just click it and press Delete. You don't need it.

Changing the year value in F2 updates the chart. Go ahead and try it. Enter the year values 2008 through 2011 and watch the chart update automatically!

As is, it's a handy chart, but users won't know to update the value in F2. You need to provide a more intuitive method. We'll use a scroll bar Form control, as follows:

  1. Click the Developer tab.
  2. In the Controls group, click the Insert dropdown.
  3. Click Scroll Bar (third control on the second line).
  4. Click inside the sheet to insert the Form control.
  5. Right-click the newly inserted control and choose Format Control.
  6. Click the Control tab (if necessary).
  7. Enter the following values: 2008, 2008, 2011, and F2 as the linking cell (as shown below).
  8. Next, drag the current year value in F2 and drop it near the scroll bar (see below as a guide). This last step isn't necessary, but doing so will help the user understand what the scroll bar is for. Reformat the linking cell if you like (I chose not to so you could easily see that I dragged the cell from F2. You'd probably want to reformat it in a production workbook.)

Simply click the scroll bar arrows to increment its values from 2008 to 2011 - those are the controls minimum and maximum values, incrementing by 1. The chart will update accordingly. Using the scroll bar isn't necessary, but it will make things much easier for your users, especially if you separate the chart from the actual data range in a dashboard type setting.

This workbook, for use as an example, is available for download.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

23 comments
dljohn7
dljohn7

Use a count function inside of the HLookup so that you don't have to manually enter the row that you want the data returned. Applying the absolute constrain on the begining of the table but not the end allows the formula to be draged down. =HLOOKUP($E$2,$B$2:$D$5,COUNT($E$2:E3)+1)

nedfraser
nedfraser

My headings were not numeric, so i needed to add an "invisible" row (white font), did a lookup to load the slider label, and worked perfect! (Eventually). Also translated a VLOOKUP, to analyze the data another way. I get so much from your articles, applying them to my needs really teaches me a lot! Thanks!

zimmerwoman
zimmerwoman

This is one of the best online "communities" I belong to for acting like a real community (along with my neighborhood yahoo group). Thanks, everyone, for all the help!!!

ronwwallace
ronwwallace

For consistency, set the chart vertical axis for categories in reverse order (Axis Options). This will cause the names in the chart to be in the same order as in the table. Also try highlighting the data that are being shown in the chart by using conditional formatting as follows. Select the range B3:E6. Select conditional formatting, new rule. Select rule type: use a formula to determine which cells to format. In the rule description, enter =B$2=$I$8. Select format, fill and choose the fill color (I like yellow). Now when you select a year the data from the bars in the chart will be highlighted in the table.

dave
dave

I like to use data validation since it also works when selecting non-numeric data such as the salesman (and charting their sales threw the years). It is a more general approach. I also use the Match function to find the column number for the table $B$1:$E$6 and then use the INDEX function to select just that column of data. It is easier to see what you are doing.

ppg
ppg

Rather than use a scrollbar I would find it easier to set up a datavalidation rule on cell F2 where the Data List of allowed values is B2:E2. In this way it doesn't matter if you miss a year. You can even use non-numeric values so it can be converted to provide monthly summaries. Also I would prefer to use MATCH to find the column and OFFSET( ) to find the value since it is easier for me to follow.

zimmerwoman
zimmerwoman

I get the top number repeated all the way down ($19,419,721). I thought maybe somehow I had set it to manual recalc, (since I am the one with the bad MS mojo), so I pressed the F9 key and still no change. That's when I copied your formula from the page and pasted that in. No chnage. Downloaded your spreadsheet to see if I could find some enlightenment. Still in the dark.

ssharkins
ssharkins

If you're not getting the correct result when copying the formula, what are you getting?

zimmerwoman
zimmerwoman

Ok, what did I do this time? When I copy the formula down, I get the top value for 2009, that of Mr. or Ms. Smith. I even copied the formula as you have entered it on this page and pasted it into excel to make sure I wasn't making some typo I could spot because I made it. No go. I hate being stuck on the dime. Interesting thing too. I downloaded the example to see what I had done wrong, and couldn't figure it out any better. What I did find was that in your spreadsheet, when I hid the numbers so the user was looking at the horse race and not at the numbers, the chart did not show any results. What's up with that?

mostria914
mostria914

How do I print this article. Everytime I've tried I get and error saying I/E stopped working, do you want to find a solution in the interent and it re-starts the I/E

sandra.scrivens
sandra.scrivens

"1.Select A3:A6. 2.Hold down [Ctrl]. 3.Select F3:A6. You just created a non-contiguous selection" I think you meant to specify A3:A6 and F3:F6 as your non-contiguous data for the chart.

rajan.sowri
rajan.sowri

Superb article! tried out the entire table. Worked great. Very interesting and useful for all of us. Keep up the good work! Madam.

ssharkins
ssharkins

Thank you, that's a very kind thing to say. I agree with you -- I visit a lot of forums, and honestly... the folks who frequent here are some of the nicest and most helpful around!

Marshwiggle
Marshwiggle

... and it also put the number scale at the top. Since 20,000,000, etc. were cumbersome anyway, I cleared that scale and put text boxes at the bottom, w/ 20K, 40K, etc. Looks pretty good, if I do say so myself.

ssharkins
ssharkins

Thanks for the enhancement -- that's pretty slick!

ssharkins
ssharkins

I agree, the dropdown would be a visual clue to the user. It's a good alternative to the scroll bar.

andrea.shannon
andrea.shannon

Hi zimmerwoman You need to change the reference at the end of the HLookup. In the first formula it is 2. Cell F4 needs to be 3, F5 needs to be 4 etc. Hope this helps!

jgrob3
jgrob3

The reason you're getting the same value is that the formula mentioned =HLOOKUP($F$2,$B$2:$E$6,2) only works for the second row. By hard coding the row offset of 2, you'll just get the value from the second row of the table all the way down the column. If you move the table to row 1, you could easily change this to =HLOOKUP($F$2,$B$2:$E$6,ROW()) There are many other ways of doing this but I'd suggest this is perhaps the easiest & most consistent with the original formula. Jeff Robson Access Analytic Financial Modeling | Management Reporting | Spreadsheet Auditing | Training www.accessanalytic.com.au

sandra.scrivens
sandra.scrivens

See my comment above: "1.Select A3:A6. 2.Hold down [Ctrl]. 3.Select F3:A6. You just created a non-contiguous selection" I think you meant to specify A3:A6 and F3:F6 as your non-contiguous data for the chart.

Marshwiggle
Marshwiggle

... in the spreadsheet, you can just change the font color to match the cell color (white, in my case). That will not affect the chart.

DBlayney
DBlayney

If you hide the source numbers then they are also hidden on the chart. Sometimes this is a great way to tidy up a chart that has gaps in it but it can also be baffling when you do it by accident. Check it out by hiding just part of the source dat and you will see how it works. If you do not want the user to see the source numbers I suggest you put the chart and the slide bar on a separate sheet and then hide the source data sheet. Hiding the sheet does not have the same effect as hiding the columns or rows contining the data.

lbkilpatrick
lbkilpatrick

You could copy and paste it into a Word document. Worked for me.

ssharkins
ssharkins

I've added that step to the instructions -- totally missed it earlier. I apologize to everyone!