You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Data, Filter, Auto Filter

Advertisement

QUESTION: Hi,

Often I set up spreadsheets and use the above tools. But one problem I have is when I first do a subtotal in one column and I might end up with a couple hundred separate subtotals. I want to filter with the custom option for the subtotal, but this tool ignores the subtotals and just looks at the entered data. Is there another way. Maybe my words don't explain, so I'll set up a simple example.

ABC 1/12/13 30.00

ABC 2/13/13 40.00

ABC 2/26/13 16.00

BCD 2/13/13 60.00

BCD 2/28/13 25.00

CDE 1/06/13 20.00

CDE 1/29/13 25.00

CDE 2/15/13 50.00

CDE 2/17/13 25.00

Okay, I've already done some sorting by column A amd B. Now I do a subtotal of column C, and I'll get 86.00, 85.00 and 120.00.

I would like to auto filter - custom those subtotals say with anything equal to or greater than 100.00, and I would only get the one for 120.00. But there doesn't seem to be a way to do that. Is there?

ANSWER: Art -

I would like to come at this question from what the end result is to look like. Would you put together a quick "screen" of that for me, like you did for your example?

Thank you - Andrea

---------- FOLLOW-UP ----------

QUESTION: Okay. I can't scan my excel spreadsheet so I did a copy/paste. Following from the original data in my question, I then did a Data/Subtotals and at each change in name use function Sum, Add subtotals under amt. Then I get the layout below that I call Problem Set Up. Look below it for what I want to do next.

PROBLEM SET UP

name date amt

ABC 01/12/13 30.00

ABC 02/13/13 40.00

ABC 02/26/13 16.00

ABC Total 86.00

BCD 02/13/13 60.00

BCD 02/28/13 25.00

BCD Total 85.00

CDE 01/06/13 20.00

CDE 01/29/13 25.00

CDE 02/15/13 50.00

CDE 02/17/13 25.00

CDE Total 120.00

Grand Total 291.00

Now what I tried to do is Data, Filter, Autofilter and go under the selection box in the amt column and choose Custom. Then the new selection criteria comes up and says "Show rows where amt..."

then you choose the phrase "is greater than" in first box and "100.00" in second box and hit Okay. Below is what I would like to see, but what comes up is 0.00, because the Excel tool looks for the entered data and not for the calculated subtotal data.

DESIRED RESULT

name date amt

CDE Total 120.00

Art -

Here is one answer to your request. I am sure there are other answers that can be done with macros or VBA; I went for the most direct and easiest to manipulate.

The problem was filtering out only those companies with a total over 100. The problems with this are that the Grand Total is also over 100, and you don't want that; and that when you use the Subtotal the subtotals created aren't actual numbers - they're formulas. So they don't show up in a filter.

I've solved this problem two ways: I am using Autofilter but through a macro, so I have many more options. Also within the macro I can copy the table and PasteSpecial, converting the subtotals into hard numbers. Then the filter works.

Here is the macro, which I based on the small table you provided:

Sub FilterToCriteria()

With Sheet1

.Range("A1:C15").Copy

.Range("A1:C15").PasteSpecial (xlPasteValuesAndNumberFormats)

.AutoFilterMode = False

.Range("A1:C15").AutoFilter

.Range("A1:C15").AutoFilter Field:=1, Criteria1:="<>Grand Total"

.Range("A1:C15").AutoFilter Field:=3, Criteria1:=">100"

End With

End Sub

Notice that the PasteSpecial pastes over the original table, which means the Subtotal function won't work. If you want to be able to modify the table and get new results, have the PasteSpecial Range be somewhere else. You'll have two tables, but everytime you run the macro it will be updated.

The macro filters out the Grand Total line, but any other lines meeting the criteria for column 3, AMT, will show up.

Copy and paste this code in the sheet that contains the table (just right-click the sheet tab and select "View Code"), customize it and you're ready to go.

Let me know if you have problems - Andrea

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

I am very familiar with teaching excel concepts, and formulas. I am not as capable with VBA questions.

I currently tutor in this area. I worked for four years developing curriculum and teaching Microsoft applications to adult audiences. I also previously volunteered for allexperts.com.**Education/Credentials**

BA, English, Western Washington University
Certificates in C Programming, ASP.NET, VisualBasic.NET, University of Washington Extension**Awards and Honors**

Rated in the top ten instructors (national), New Horizons Computer Learning Center.