Modify an existing chart in Excel to show different data with a little filtering ingenuity

 

by Frank Kuhn

Application: Microsoft Excel 2000/2002/2003/2004/2007/2010/2013

Operating Systems: Microsoft Windows, Macintosh

Download: http://download.elijournals.com/excel/201812/parameters.zip

In Excel, it’s common practice to edit a data table, or create an entirely new one, just to chart different portions of it — but that doesn’t mean it’s a best practice. By using Excel’s AutoFilter feature, you can set parameters to your chart data and exhibit only the data that means most to you without starting from scratch.

To specify our chart data with help from the AutoFilter feature, we’ll:

  • Apply simple data parameters to a sample chart using the AutoFilter feature.
  • Create a customized AutoFilter to apply a single threshold to our chart data.
  • Use the customized AutoFilter feature to chart only data in a specific range.

You’re often dealing with a lot of information, but for purposes of display or analysis, you only need to graph certain elements of it. AutoFilters allow you to dynamically change your charts to show only data that you need, so you can save vital time and effort when charting. Using the AutoFilter feature, you can build parameters into your charts, so that if you only want to see branches that fall within the top fifteen percent of fixed expenses, for example, or only data from a certain range of dates, you can do so.

Apply a dynamic parameter

We’ll use the example of quarterly fixed expenses across 16 international branches, as shown in Figure A. We’re interested in displaying only the four branches with the highest Q4 fixed expenses because the company wants to audit these branches to find places where they can cut expenses. You can use the AutoFilter feature to quickly display only the top four fixed expense values.

Download: You can follow along with our example by downloading and extracting the file fixed expenses.xls from the URL given at the beginning of the article.

To display only the top five representatives:

  1. Select any cell in your data table.
  2. Choose Data | Filter | AutoFilter from the menu bar to display two dropdown lists in your list column headings, indicating that the AutoFilter feature is active.
  3. Click on the Fixed Expenses column’s dropdown list and select (Top 10). The Top 10 AutoFilter dialog box opens, as shown in Figure B.
  4. Enter 4 in the middle spin box and click OK to filter out all but the top four fixed expense values from your list.

IME18c01a

A: We’ll use the AutoFilter feature to change the data in our table, and consequently, in our chart.

IME18c01b

B: The Top 10 AutoFilter allows you to choose either the top- or bottommost elements or percentage of elements in a list.

Your chart, when it’s linked to your data table, dynamically adjusts to reflect only the four branches with the highest fixed expenses, as shown in Figure C. You may need to adjust the size of your chart so all entries are clear. Please note that you could have just as easily requested to see the top (or bottom) four percent of your data as well.

IME18c01c

C:

The AutoFilter you apply automatically adjusts the data in your linked chart.

 

Caution: If you’ve filtered your data table, and you apply another filter, it only filters the data already visible, not the entire data set. You mus[...]

 
Join NowClose
Return to the ExcelSkillsSociety's homepage