I'm having a slightly frustrating issue with autofilter. My spreadsheet includes headers, about twenty rows of data below those headers, and then two rows below the data presenting averages of each column. I'd like to use the autofilter to sort by value for each row, but it keeps pulling my average rows into the sort. I've tried setting the sort manually for just the rows of data, even in VBA, and it keeps pulling my averages in. Does anyone have a good sense for a) why this is happening, and b) how to fix it?
Autofilter messing up my spreadsheet
- Thread starter nseelen
- Start date
Debraj
Excel Ninja
Hi nseelen!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
First select A1 to E21 (assuming E is your last used Column)
then apply AutoFilter. Now all your filter criteria are limited only within selected Area.
Don't select all Columns at once..
BTW. In A21/A22 instead of
or [/code]=SUBTOTAL(101,A2:A20)` (For Excel >= 2007) then after applying filter will always assume that A21 is you grand Average Row, and exclude it, in the similar case, and works great in case of Filter and Hidden Rows..
Regards,
Deb
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
First select A1 to E21 (assuming E is your last used Column)
then apply AutoFilter. Now all your filter criteria are limited only within selected Area.
Don't select all Columns at once..
BTW. In A21/A22 instead of
Code:
=AVERAGE(a1:a20), use [code]=SUBTOTAL(1,A2:A20)Regards,
Deb
Hi Deb,
Thank you for the warm welcome! Unfortunately, neither of your suggestions seems to have helped. My headers are in cells A4:H4. My data are in cells A5:H19. My calculations are in cells C20:H21, with descriptions of the calculations in B20:B21. Even though I highlight only cells A4:H19 and substitute the SUBTOTAL formula for AVERAGEA, the autofilter still pulls my bottom rows into the data when sorting. I did note, however, that if I delete one of the two calculation rows, using SUBTOTAL keeps it out of the sort process. In this case, though, I do need the two rows. Any other thoughts?
Nat
Thank you for the warm welcome! Unfortunately, neither of your suggestions seems to have helped. My headers are in cells A4:H4. My data are in cells A5:H19. My calculations are in cells C20:H21, with descriptions of the calculations in B20:B21. Even though I highlight only cells A4:H19 and substitute the SUBTOTAL formula for AVERAGEA, the autofilter still pulls my bottom rows into the data when sorting. I did note, however, that if I delete one of the two calculation rows, using SUBTOTAL keeps it out of the sort process. In this case, though, I do need the two rows. Any other thoughts?
Nat
Debraj
Excel Ninja
Hi Nseelen!
I would like to request you to add a blank row in between A19 and A20.
Now select A5:H19 and apply filter. It will works for you.
Please find the attached and let us know it its works for you..
https://dl.dropbox.com/u/78831150/Excel/Autofilter%20messing%20up%20my%20spreadsheet%20%28nseelen%29.xls
Regards,
Deb
I would like to request you to add a blank row in between A19 and A20.
Now select A5:H19 and apply filter. It will works for you.
Please find the attached and let us know it its works for you..
https://dl.dropbox.com/u/78831150/Excel/Autofilter%20messing%20up%20my%20spreadsheet%20%28nseelen%29.xls
Regards,
Deb
Debraj
Excel Ninja
Hi Nat,
Happy to Help..
When we apply Filter, at that time Excel by default select a AREA (surrounded by blank cell). When you are applying Filter, Calculation portion is also falls under same area.
Sometime is helps, sometime it makes a great mess. For example, If there was a blank row in between total data and you pressed Ctrl Shift L or Alt D F F, it exclude the area below blank row.
Use this problem as your power, and apply accordingly, use blank row when you need only some area to apply filter, and take care of Blank row when you need to include the bottom portion also.
Regards,
Deb
Happy to Help..
When we apply Filter, at that time Excel by default select a AREA (surrounded by blank cell). When you are applying Filter, Calculation portion is also falls under same area.
Sometime is helps, sometime it makes a great mess. For example, If there was a blank row in between total data and you pressed Ctrl Shift L or Alt D F F, it exclude the area below blank row.
Use this problem as your power, and apply accordingly, use blank row when you need only some area to apply filter, and take care of Blank row when you need to include the bottom portion also.
Regards,
Deb