Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel Zone compendium: Sorting and filtering tips

by
17th Apr 2008
Save content
Have you found this content useful? Use the button above to save it to your profile.

Simon Hurst offers a primer on they whys and hows of using Excel's various sorting and filtering mechanisms.

Introduction: practical uses for sorting & filtering
Excel's sorting and filtering functions are comparatively straightforward and easy to use, although even something as apparently innocuous as sorting could cost you your job if you get it wrong, [[[as demonstrated by 'EuSpRIG spreadsheet mistakes – news stories' number 78 http://www.eusprig.org/stories.htm]]]. Often the issue with sorting and filtering a block of data relates as much to getting the data into an appropriate form as using the sorting and filtering tools themselves.

Solutions: Use text functions to make sorting and filtering easier

Getting to grips with Excel's text functions will be invaluable if you need to split a single column into multiple columns, or create a single column from multiple columns.

Peter Hool's Expert guide to splitting cells in Excel and several additional comments covered the use of some key Excel text functions:
TRIM() - removes extra spaces
FIND() - finds the first occurrence of a particular item of text within a text string
CHAR() - enables non-printing characters to be used
SUBSTITUTE() - replaces one bit of text with another
LEN() - calculates the number of characters in a text string
LEFT() - returns a number of characters from the left of a text string
RIGHT() - returns a number of characters from the right of a text string
MID() - returns a number of characters from a designated starting point within a text string

Going the other way, The CONCATENATE() function or the '&' operator can be used to combine the contents of several cells into just a single cell:
=A1 & " " & B1
Or:
=CONCATENATE(A1," ",B1)

Solution: Use word wrap to avoid blank rows and columns

Usually when you use the sort and filter tools it makes it easier if Excel is able to automatically recognise the block of data you want to work on, including identifying the heading rows. Avoiding unnecessary blank rows and columns, and keeping the headings within a single row by using Format Cells, Alignment, Wrap text can all help achieve this.

Understand Pivot Tables

Sometimes creating a Pivot Table from your data can be the simplest and most effective way of sorting, filtering and summarising data. The Pivot Table section of the ExcelZone Compendium should guide you through most of what you could possibly want to know about Pivot Tables.

Some simple sorting tips

First, the career-threatening danger. If you select a single column, or a block of cells, out of your full set of data and then use one of the quick sort buttons, only the selected area will be sorted. This could mean that your exam scores are sorted independently of the names of the candidates for example. In more recent versions of Excel you may see a warning message with the default option set to expand the selected range to preserve the integrity of your data:

However, earlier versions carry out the partial sort without warning, and even in the recent versions, if you select a block of cells that spans more than one column, there will be no warning.

To sort by a column in a block of data, just click on a single cell in that column before clicking on the AZ or ZA button on the standard toolbar (Excel 2007: Data ribbon, Sort & Filter section)
For a more complicated sort, you can choose Data-Sort (Excel 2007: Data ribbon, Sort & Filter section, Sort button OR: Home Ribbon, Editing section, Sort & Filter button, Custom sort). The Sort dialog lets you set up to three levels of sorting as well as specifying whether the data includes a header row:

The Options button provides further options to set the First key sort order as 'normal' or to use a custom list to define the sort order; to make the sort Case sensitive or not and to sort from top to bottom or left to right.
In Excel 2007 the detailed sort options have been extended to allow for more than three levels and to allow levels to be deleted, copied and the order changed:

Excel 2007 also allows for sorting based on certain cell formats: Cell colour, Font Colour and Cell Icon:

Excel 2007's new Format as Table feature automatically adds dropdowns to each column heading giving access to sorting and filtering options.

For those not yet using Excel 2007, Any Answers has some alternative suggestions based on the use of VBA code and the DigDB Excel add-in to cope with sorting and filtering by cell colour .

Filtering: AutoFilter

Data-Filter-AutoFilter (Excel 2007: Data ribbon, Sort & Filter section, Filter button OR: Home Ribbon, Editing section, Sort & Filter button, Filter) adds dropdowns to the headings in a block of data that enable you to choose a particular item in a column as the filter criteria. If you select the dropdown in more than one column then the filters will act as an 'AND' - ie each of the criteria must be satisfied for any particular row to be displayed. As well as simply selecting from the contents of the column, the AutoFilter dropdown also includes the ability to only display the top or bottom however-many items, either by actual number or by percent. The Custom option allows more complicated filters to be set up: for example greater and less-thans, rather than just equals, and to use wildcard characters, and multiple And/Or criteria within a single column.

Excel 2007 has significantly improved the available filter options. The type of data in the column is recognised, and a list of suitable filter types presented, including a custom option:

In addition, in the same way that you can sort by colour in 2007, there is now a Filter by Colour option.

Advanced Filter
Data, Filter-Advanced Filter (Excel 2007: Data ribbon, Sort & Filter section, Advanced) allows you to create more complex filter criteria. Rather than just adding dropdowns to your column headings, Advanced Filter allows you to create a separate area of your spreadsheet to hold a second set of headings and some free format criteria. You can create OR criteria by including multiple rows in your criteria range - a record will have to satisfy all the criteria within criteria row 1 OR those within criteria row 2 to be included in the output, for example. Criteria in a single row operate as ANDs - all the criteria within a row need to be satisfied for records to be included in the output. You can include one or more AND criteria relating to a single column, by including the column heading more than once in the criteria area.

The output of the advanced filter can be placed in a separate location, or the data can be filtered "in place" and there is an option to return "Unique records only" which can be very useful in removing duplicate items from a block of data.

Excel 2007 includes a more sophisticated method of removing duplicate records. The Data ribbon, Data tools, Remove Duplicates button allows you to specify the columns to check for duplicates, so out of a block of data you can select one or more columns and any rows that have duplicated data in just those columns will be deleted. In this example rows where both the Customers.CompanyName and the ProductName are duplicated:

Further reading

Recommended compendium items:
Finding duplicate records in lists in Excel 1
Finding duplicate records in lists in Excel 2
Finding duplicate records in lists in Excel 3
Finding non zero values in Excel
Sort worksheets by tabs
Subtotal with AutoFilter
ExcelZone Compendium Archive - more articles and queries on sorting and filtering

About the author

Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping practitioners make effective use of technology. He is also the author of '100 Time-saving Tips for Microsoft Office'. For more information, visit The Knowledge Base website. .

Subscribe to the ExcelZone NewswireSubscribe to the ExcelZone newswire
To keep up with all spreadsheet-related developments, click the button to subscribe to the ExcelZone newswire. You will return to the AccountingWEB home page when your name has been added to the mailing list.

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.