Make a Dynamic Chart using Data Filters

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.