Create a Dynamic Chart in Excel in 2 Minutes [spreadcheats]

Hey, this is pretty cool.

Propably the easiest way to make dynamic charts I’ve ever seen. Doesn’t require nor named ranges, nor the knowledge of advanced functions. Well done, Chandoo.

Nice one!

It’s a nice idea, but there’s one thing I like dynamic charts for that this can’t do: I want charts that are guaranteed to have the same format and different data. The same size, same shape, same text etc. And when I need to alter the formatting I don’t want to have to change it several times and risk making a slip of the mouse; I want to change it once and know that when I paste the multiples into a report, they are guaranteed to be the same except for the data and maybe the y scale.

But it’s still quite clever.

Did you try this in Excel 2007? It looses a lot of its coolness, because it takes more mousework to change a filter than in 2003.

Although I did not try the Dynamic Charts using INDEX() function but this is the easiest and simplest way I ever seen.

As I said I learning new good things from you THANKS AND MANY THANKS

Hey,

Pretty easy !!!

I have also written about dynamic charts in my blog at
http://www.webanalyticsindia.com/2008-12-17/dashboard-design-tricks-using-combo-box-for-a-neat-treat/

Hi Chandoo,

Hats off to you Sir !!!

Well this is by far easier then using INDEX() method, thanks once again for spreading valuable knowledge.

Regards
Rohit

Have to agree with Derek – I much prefer to have the data dynamic rather than the charts themselves. That was the basis of my ChandooChallenge: http://www.box.net/shared/5ny59hc0al

…but a neat trick none-the-less.

thank your idea for data filter.

Hi Chandoo. This tip is absolutely fantastic. I have already implimented into a business report that has many charts running down the page. As always managers want everything to fit in one page and now it can. Also the added ability to be able to group Charts by a theme and filter makes this a very powerful tool indeed. Bueatiful in its simplicity. Outstanding work!

Here is a function to display the filter criteria currently in use. Very handy

Function FilterCriteria(Rng As Range) As String

Dim Filter As String
Filter = “”
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column – .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & ” AND ” & .Criteria2
Case xlOr
Filter = Filter & ” OR ” & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
‘ =FilterCriteria(A3)example function
End Function

Very interesting tip. I view this as a “quick & dirty” technique rather than something I’d want to do in a production report. The drawback being the extra filter values that AutoFilter creates that just don’t fit the situation (Top 10, Custom, etc.).

One improvement: put titles on the charts so that you always know what you’re viewing.

If one or more column before the charts column also had a list of relevant words, one could do a dynamic multi-level category display, e.g.:

Costs | Staffing
Costs | Facility
Costs | Shipping
…
Sales | Product1
…

Any number of other arrangements of categorization are also possible, of course.

The enhanced abilities of 2007 filters might be quite useful, depending on the nature of the categories and list of charts.

So simple, even I could do it! Very, very clever. The more sophisticated techniques are nice, but this is the one that I can walk away from and repeat without having to consult notes. That’s priceless!

This is a great idea.

You can also do the same with pictures – remember to format them to “move and size with cells”.

In the comments to one of Jon Peltier’s articles about the more conventional sort of dynamic chart, a commenter asks if there’s a way to show only the charts in a KPI scorecard where the status stands at Red or Amber. It strikes me that this could be a situation where your trick would work better. Not for showing always one, and only one, of a range of graph alternatives, but a showing or hiding a varying *number* of them.

Derek – Good idea….

You are brilliant. My life is better. This is the best Excel site- and I’ve been to dozens. Makes me want to send you money.Love and kisses.

You are great this works much better. You can actually scroll in behind your exsisting graph and key in the title and set up the filter and your are set. Much easier than define name….

Thanks

Chandoo – been on your site for 20 minutes now and love the “2 minute style” tutorials – fast and to the point!

will try this first thing in the morning at work, actually second after a cup pf coffee

thanks for asking, the coffee was ok but the chart was fantastic

Gr8 ! Tip

This is simply brilliant for a “quick and dirty” setup. Hat’s off for thinking this one out.

Hi – I like this trick; but I’m unable to get it to work in Excel 2007. I guess they have changed the structure of the Filter menu now and so I actually am presented an option with checkboxes to select which of the three I want to see

From a UI perspective, I’m no longer able to restrict users to viewing only chart at a time (unlike the drop down menu shown in the example here). Is there any workaround for that?