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.
6 Responses to “Using Lookup Formulas with Excel Tables [Video]”
H1 !
this is my very first comment.
Can you use same technique with Excel 2003 lists ?
thanks 😀
Thanks, Chandoo! I like seeing the sneak peak of what's to come on Friday too 🙂
@Damian.. Welcome to chandoo.org. Thanks for the comments.
Yes, you can use the same with Excel 2003 lists too.
@Tom.. You have seen future and its awesome.. isnt it?
[…] Using Tables – Video 1, Video 2 […]
[…] Using Tables – Video 1, Video 2 […]
Hi, is there a vlookup formula for the second example (IDlist)? I used a similar formula to look up the ID for the person, but the reverse way (look up the person with the ID) comes up N/A.