SubscribeMake a Dynamic Chart using Data Filters
Posted on February 12th, 2009 in Charts and Graphs , Learn Excel - 14 comments
This post is part of our spreadcheats series. Please read other posts in this series to know how you can cheat excel to become more productive at work.
Do you know that you can create dynamic charts in excel using data filters ? Yes, that is right, we can use data filters as chart filters too. When you apply a data filter on a chart’s source data, the chart is also filtered.
See this screencast to understand it: (if you cant see it click here)
This technique is much simpler than dynamic charts using drop-down lists and INDEX formula idea presented earlier. All you need to do is,
- Create chart for all your data. Include sales, profits and expenses
- Now, apply data filters to the source data range (menu > data > filter > auto filter, in excel 2007, use home ribbon > filter & sort button > filter)
- Select the type of data you want to use in the chart by applying a data filter
- Bingo, you have a dynamic chart that can be controlled using data filter settings.
How do you like this technique ?
Browse more such nifty hacks by reading previous posts in spreadcheats series.
Trackbacks & Pingbacks
- Pingback by Excel Yogi’s » Blog Archive » Automatic Updating “Dynamic” Charts in Excel on February 16, 2009 @ 8:30 pm
- Pingback by Create a Dynamic Chart in Microsoft Excel | Charts & Graphs | Excel @ Work: Charting & Excel Tips - Chandoo.org on May 19, 2009 @ 10:51 am
Comments
RSS feed for comments on this post. TrackBack URI
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 350+ articles and tutorials on using excel, making better charts. 
Agreed this technique is much simpler than your previous one, however (yes we have to throw in that
) for user purposes, it’s not as friendly.
You previous method was neater in the dropdown tab, so the user will not be “bombarded” with “all, custom, filter” etc.
But technically, this is certainly easy and less of a hack.
Personally, I make use of a macro hack (I know the purpose is always to avoid these) but it improves the eye candy effect especially if your reports are for top level management.
@Adam: very good point, of course there is “however”, but I wanted to keep the post simple and hence removed that part. But here goes a partial list of things that are wrong / missing in this technique:
1. you cannot have selection on top (think of it, that is more intuitive than having selection at bottom) since that way chart would be moving up and down when you change the filter
2. the filters in excel 2007 are weird, you need 2 clicks to do the same stuff that you can do in excel 2003 and earlier with one click, worse still they have a ton of useless options
3. you can not juxtapose data and chart sideways as this would
lead to chart getting re-sized based on selection
4. finally this technique keeps data and chart in same place, which goes against traditional argument of keeping view and data separate
your turn…
Of course
1. Agreed, the chart would be moving up and down, although you set the properties for the chart not to move and play around with the placing.
2. Don’t even go there. Since your one post with the Excel interview I have stuck to 2003 on my laptop. Although unfortunately I have 2007 at work. Will stick to 2003 for the time being…
3. Similar thought to point above.
4. Not only separate, but actually hiding the data.
Given the nature of my work, the people I report to don’t care about the data…they are only interested in the charts and if they have clickable buttons, then even better.
I don’t have access to the one sheet now, but will send through to you how I played around with the idea of selecting the desired chart.
Can you put a post on how this can be done with Offset? … I need a defined name that is over 255 Characters and won’t fit in there.
@Sridhar, Can you elaborate your request, I am not sure I understood what you meant by “I need a defined name that is over 255 Characters and won’t fit in there.” and why the defined name is interfering with data filters ?
“you can not juxtapose data and chart sideways as this would
lead to chart getting re-sized based on selection”
I tried this methoed then reread the comments and saw this. Oh well back to learning dynamic ranges and how to filter in them. I really do not like using pivot charts in 2007, to unstable.
@Brian: take a look at our tutorial on showing one chart from many using index function. It might help you…
http://chandoo.org/wp/2008/11/05/select-show-one-chart-from-many/
Its a great feature but very weak.
Can you connect selection box to the chart?
Can you set the chart as selector?
@Dynamic Charts: There is a tutorial for that too.. http://chandoo.org/wp/2008/11/05/select-show-one-chart-from-many/
There are few more ways you can do this, one is to use selection box to change the chart range values (using some variation of index formula)
or even using VBA to automate and make things smooth.
With your dynamic chart above, can you also turn it into a Pareto Chart (descending order) automatically when you use your data filter?
Thanks for the great advice!
I don’t understand how this chart was created with all three data types. I would normally use column headings to define the Sales, Expenses, and Profits bars in the clustered column chart. Otherwise, I would think a pivot chart would be necessary. Can you elaborate on how to create the initial chart? Thanks.