Advanced Auto Filter

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
123
I have the table below, when I select my auto filter criteria (Overservice <50% IN RED) all rows except the first are hidden rather than just the ones over 50%. Can anyone see why this may be occuring. thanks

OverserviceOptimal Time CostTime Cost ReductionAvg Rate HoursOverservice
151.5% $ 27,455.79 $ 18,582.96 $ 147.50 126<50%
61.9% $ 28,731.51 $ 2,274.74 $ 129.64 18
25.0%
46.5% $ 170.20
81.9% $ 28,500.00 $ 6,068.75 $ 175.24 35
185.9% $ 29,462.60 $ 26,693.66 $ 165.26 162
133.7% $ 28,613.16 $ 15,974.34 $ 164.03 97

<tbody>
</tbody><colgroup><col><col><col><col><col><col span="2"></colgroup>
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Are you using Advanced Filter or AutoFilter? For me Advanced Filter hides all except rows 4 and 5.
 

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
123
Advanced Filter. With the filter being <50% all rows should show except 3 & 4
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Are you sure that the entries in the Overservice column are actually numbers? You can use ISNUMBER to check. Here's what I get:


Excel 2010
ABCDE
1OverserviceOptimal Time CostTime Cost ReductionAvg RateHours
425.00%
546.50%$ 170.20
Sheet2
 

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
123

ADVERTISEMENT

They are as ISNUMBER shows "TRUE" I do have an IFERROR formula where the error will return a "-" I dont know how to return a numeric value that this filter formula will recognize as "0" returns the ISNUMBER formula as "FALSE"

How are you copying your spreadsheet into the dialogue box?

IN the advanced filer box I am selecting all cells under the "Overservice" Column and my filter criteria is "Overservice' & "<50%"

Why i apply all rows are hidden but the first row. I dont understand.
 

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
123

ADVERTISEMENT

I got it to work but i put the criteria filter above the set of data. is that the only way it works?
Also is there a way I can use a formula to link this criteria to another user inputted cell? For example, i have a number of forumulas driven by the percentage entered, in this case 50%. can I enter a formula in the cell the autofilter picks up that will pickup the user entered infomation? ie. (>&"K1")
 

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
123
Sorry one other question.. if i insert a chart will it show the data that is filtered or all data within the data series. in other words will the chart change based upon the data that is shown
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
For the criteria try:

="<"&K1

A chart will plot only the visible cells provided you have checked that option.
 

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
123
ok thank you.

Regarding my chart, i am plotting a client, the period and overservice percentage. in my data the client can repeat multiple times as we are charting overservice by different periods (Jan, Feb, etc) Is there a way to do a dropdown menu on a chart where i can choose the one client and then it will plot the periods on the horizontal axis and the percentage on the vertical?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,199
Messages
5,835,933
Members
430,396
Latest member
dzifna

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top