The AutoFilter Function
Definition
This is a function you can use to summarize your data. It shows a drop down menu for every column, which you can select to give certain data for you.
Example
If we have the following data:
| 1 | Account | Type: | Code: | Name: |
| 1 | Customer A | Sales Code: | ||
| 1 | Contact: | John | ||
| 1 | Mobile Phone: | |||
| 1 | Email: | |||
| 1 | Telephone: | |||
| 1 | Fax: | |||
| 1 | Account | Code: | ||
| 1 | Customer B | Sales Code: | ||
| 1 | Contact: | Pieter | ||
| 1 | Mobile Phone: | |||
| 1 | Email: | |||
| 1 | Telephone: | |||
| 1 | Fax: | |||
| 1 | Account | Code: | ||
| 1 | Customer C | Sales Code: | ||
| 1 | Contact: | Craig | ||
| 1 | Mobile Phone: | |||
| 1 | Email: | |||
| 1 | Telephone: | |||
| 1 | Fax: | |||
| 1 | Account | Code: | ||
| 1 | Customer D | Sales Code: | ||
| 1 | Contact: | Gagiano | ||
| 1 | Mobile Phone: | |||
| 1 | Email: | |||
| 1 | Telephone: | |||
| 1 | Fax: | |||
| 1 | Account | Code: | ||
| 1 | Customer E | Sales Code: | ||
| 1 | Contact: | Rundligh | ||
| 1 | Mobile Phone: | |||
| 1 | Email: |
We want to get the contact names. Highlight the first row and go to DATA > FILTER
Can you see that the drop down cursors appears on the first line? Click on the third one and then click on “contact”.
All the lines with the names “contact” are shown. You can now copy this to a new sheet, and use as you like. Can you see that autofilter does not change the format of your sheet? It only shows the data you require. When you go back into the filter and select “all”, the original format will be shown.
You can also filter out amounts. When you have huge amounts of data, you can go into the drop down cursor and select custom.
- Here you can select number that is more or less than an amount.
- You can select only words containing a certain phrase.
Play around in there and see what you can come up with.
Troubleshooting
The following problems occur with autofilters
- When the first column is empty, the autofilter does not always work. Make sure that column is not empty. Even if you only type in “a” in the first column to the end of the data range.In our first example above, we inserted 1's to overcome this problem.
- When the data is very big, the autofilter will not always go until the end of the data range.
- Make sure you type the phrase when using the custom function; otherwise no information will be given.
- When you have used the filter option, and you copy this filtered sheet to another sheet, only these data will be shown, all the other information will be lost. Remember that the information is still in your original sheet with the autofilter. Just click on All
- When you have a lot of columns, the ones that are filtered is the one with the cursors shown as blue.
Things you can do with the autofilter
- Filter any data without changing the format...contact us for more info
- Filter by colour...contact us for more info
- Find any data you are searching for...contact us for more info
<-----Back to index
