Help with filtering.

[kimberleyanddarren]

Hi Everyone,

I have a complex Excel document with lots of data. I use the filter option to regularly see the groups within the data, however the formula at the bottom does not adjust to take into account only the filtered data.

To simplify, if I had a data form with boy/girl and favourite colour and then filtered this down to only show girls the formula would remain the same despite the boys having been hidden by the filter. If the percentage of boys/girls who like blue was 62% it still says 62% even after all the boys have been hidden by the filter.

Is there a way to alter the formulas so that it can count and generate percentages on ONLY the data which still remains and not the entirety of the sheet? I would like to set something so that if I then clear the filters it will include all data again.

I hope this has made sense!!

I am using Excel 2010.

Thank you,

Kimberley

[mcmahobt]

Look into using the SUBTOTAL function - it works specifically with lists or databases (filtered or not). Otherwise, something like a drop down selection for differing criteria (using named ranges, etc.) could also be used, and analysis run based off those results.

[kev_]

Welcome to the forum

Have you considered using an Excel table?
subtotals.jpg


"Total Row" is similar to the subtotal function

TotalRow.jpg

TotalRowOptions.jpg