Excel's Autofilter Function

Many QI Macros customers find themselves faced with huge data dumps from corporate systems. And they wonder: "What do I do with this?" Sometimes the first step is to reduce the data to a manageable quantity using Excel's Autofilter.

Create Subsets of Your Data Using Autofilter

Getting data out of corporate systems into Excel is usually easy. Virtually all allow some sort of export to Excel. Then comes the hard part: reducing the data. A data dump might look like this: (If you have loaded QI Macros or a 30 day evaluation copy, this spreadsheet is on your computer at c:\qimacros\testdata\crosstab.xls.)

Excel data that has not been filtered

But maybe we only want to look at Hospital 1. You can use Excel's Filter function to reduce the data displayed. Click on Data > Filter. Then click on the pull down arrow next to ENT and only select the box next to Hosp 1:

select a filter using Excel's autofilter function

This will select only the data that contains "Hosp1":

data view after using Excel's autofilter function

To remove the selection, simply click on the pull down menu and Select All:

Excel's autofilter function

Custom Autofilters

Or you might want do a custom selection. Let's say that we only want to see the patient accounts that have a unpaid balance of more than $2,000. To do this, click on the pull down arrow next to ACCT BAL and select Custom:

custom autofilters in Excel

This opens a dialog window. Change the selection to "is greater than or equal to" and set the value to 2000:

define custom autofilters in Excel

Then click OK to see the data the balances over $2,000:

filtered data using custom autofilters in Excel

If you only want to see the balances for hospital 1 that are over $2,000, click on ENT and select Hosp1:

filtered data using custom autofilters in Excel

As you can see, you can continue to select and subset the data in any way you choose. You can copy and paste these subsets into another spreadsheet without any of the hidden rows using Edit-Copy/Paste. You cannot, however, paste data back into the autofiltered sheet without overwriting hidden rows.

If you get lost and want to start over, simply click on Data and then Filter to show everything.

turn off Excel's autofilters

Even Huge Spreadsheets Can Be Manageable

Excel's Autofilter can help reduce the amount of data served up by corporate systems to a manageable level. From there, you can:

  • Use QI Macros to graph the selected data.
  • Use Excel's PivotTable to summarize the selected data.

Use the power of Excel to narrow your focus to the few key things you want to understand, diagnose or improve. Then use QI Macros to graph the results and present your business case (i.e., improvement story) to the decision makers.

Isn't it Time to Upgrade Your Excel Charting Skills?

Stop trying to make your spreadsheets easy to read. Nobody wants to read your spreadsheet!
And stop using primitive line, pie and bar charts.
Isn't it time to graduate to Smart Charts: Control Charts, Pareto charts and Histograms?

primitive charts
Primitive Charts
smart charts
Smart Charts

Stop Struggling with Excel Charts!
Start creating Smart Charts in just minutes.

Download a free 30-day trial. Get Smart Charts for Excel now!

Smart Charts Included in QI Macros for Excel