Making The Most Of Filtering In Excel

In some ways, the advent of spreadsheet applications such as Microsoft Excel was even more revolutionary than the arrival of the word processor. If we travel back in time to imagine Dickensian ledger clerks sitting on their tall stools, the nibs of their pens scratching the paper as they enter figures onto pages that lie on sloped writing desks, we think of an age as far removed from the modern day as a pea pod is from an iPod. And yet up until the 1970s ledger clerks were still to be seen entering figures by hand, although, according to my mother who worked in the accounts department of a huge multinational company, a pencil and a good eraser had replaced Dickens's scratching pen.

Whereas Microsoft Word was a natural progression from the typewriter, the advent of Excel and its ilk was a giant leap from hand-written to typed entries. And yet when my mother's department switched to a computerised system in the late-seventies, many of her colleagues yearned for a return to the manual system. Old habits really do die hard.

I am pretty sure that this yearning would not have lasted very long, however, as employees became accustomed to the new technology and discovered the many advantages of computerised data entry. The ease with which data can be manipulated, and the array of tools that can perform essential tasks will have converted even the most die-hard traditionalist.

Even basic functions that we take very much for granted today, such the Filter command would have been considered major breakthroughs back then, as they can deliver at a stroke on screen what would be very difficult to do by hand.

The Filter tool is a particularly useful function that allows the operator to extract data from a list according to certain criteria. This filtered data can then be manipulated as though it was a distinct group, separated from the list to which it belongs. For example, the operator may wish to send an email to all females on the database, or people from a certain postcode area. In its most basic form, the Filter tool enables the operator to separate selected groups from the main 'database', although more complex tasks can be performed.

To experiment with filtered data: on an open worksheet, select a cell within the list range. Click Data/Filter/AutoFilter. You will see drop-down arrows appear at the head of each column. Click the arrow at the head of the column you want to filter and study the options. You can then select from the drop-down menu the records that you wish to separate from the list. Having done this, you can click on the arrows of other columns to filter out even more records. Returning to the drop-down lists, as well as the contents of the cells in that column, you will see a Top 10 command, and clicking this will display the first ten items in that list (you can also view the bottom ten).

You can carry out more complex searches by clicking the Custom command, although this does introduce more complicated actions, such as searching for records that meet more than one of the criteria, and so it may be better to explore Excel at this level by enrolling on a training course. To remove the filter and return to the full worksheet, simply click Data/Filter/Show All.

Separating certain groups from a list in the old days of hand-written ledgers would be a very time-consuming task that would involve writing a new list. To be able to perform the same task with only a few clicks of the mouse shows the value of familiarising yourself with some of the many useful tools that lie hidden beneath the surface of Excel.