|
| |
• Home • AutoFilter • Count words • Sumproduct • Pivot Tables •
Excel AutoFilter
If you have a large range of data that you need to filter on an ad-hoc basis,
the auto filter can prove to be very useful.
Turn it on by selecting Data, Filter, Autofilter. Turn it off by reselecting
Data, Filter, Autofilter.
To select from your list, use the triangular markers which will allow you to
select from entries in your data. I have selected creditors here.
You can also use the (custom...) option on the drop down, which will allow you
to select from entries in your data, or to manually type in a value for the
filter (in the blacked out area).
In this case, I have inserted a row and added a useful formula to total my
selection. Using the SUBTOTAL formula, we can sum all of the entries, or just
some of them, as the balance changes according to the filtered selection made.
If you use the SUM icon on the toolbar, Excel will write the formula for you.
All you need to do is to filter first, then drag the sum selection over the sum
range.
AutoFilter Limit
The auto filter has an automatic selection limit of 1,000 rows hard coded into
Excel.
This is not a true limit, as you can select larger ranges by setting the filter
criteria manually, or by macro.
For large data sets manually insert the filter criteria manually using the
custom dialog or insert some code.
The following code will show all the "Creditors" entries in 6th column of the
data set:
Sub Filtr()
Selection.AutoFilter Field:=6, Criteria1:="Creditors"
End Sub
| |
|
