Sorting Out Your Data In Excel

Learning how Excel can sort your data for you, and knowing how to use filters, can be a great advantage in avoiding cluttered up spreadsheets. It also makes them more user friendly and easier for other people to find what they want, if you’re sharing your data.

As you get more familiar with Excel, no doubt the kind of spreadsheets you produce get bigger and more complex. Soon you're managing huge projects or tasks in a single worksheet or workbook. Eventually, the search and find functions have you wasting time trawling, because there's so much data to plough through (did you know that you can have almost seventy thousand rows? Ouch!). Learning how Excel can sort your data for you, and knowing how to use filters, can be a great advantage in avoiding cluttered up spreadsheets. It also makes them more user friendly and easier for other people to find what they want, if you're sharing your data.

The two fastest and most effective ways of sorting out your data are the sort or filter (or auto filter) options. Sorting or filtering your data doesn't just mean it's easier to find what you're looking for, it also makes manipulating your data far more fluent - such as spotting patterns in the data or creating reports based on them.

Taking the same example for both functions, here's how they're used. In our example, let's say that you're managing your mortgage payments (or similarly, hire purchase arrangements if you're a car business or dealer). Interest rates go up and down with the base rate in most cases, so you can sort your data by added percentage on top of your basic payment, to see which months cost you the most. Sorting can also be in two stages - for example, the interest added to your payments being sorted, followed by the month or year, or followed by what you budgeted that month - so you can see whether you hit or missed your own target.

If you were a car dealer, you could use the obvious sorts - if you not only wanted to see how many blue cars you sold (sorting by colour), you could then sort by make, model or year to see what your best-seller is. One of the most common sorts - chronological or alphabetical order (great for customer or address lists), is the AZ (or ZA) button shortcut, which does an instant sort for you. It will also ask, (depending on how your spreadsheet is laid out) if you want to expand your sorting to the other columns - for example, sorting A-Z by surname is fine, but saying "yes" to Excel's expansion will sort the addresses out next to the names they relate to.

Filtering is a slightly more advanced kind of sorting, advantageous in that it removes any data from your immediate view that isn't relevant to what you're looking for. If you wanted to view orders by one customer at a time, an auto filter (the tool will apply this to the top of columns) would be a great way to start. It's a very quick, easy way of pulling up data that you need - and printing it out with only the relevant details on the screen. The auto filter itself can do further sorts within, such as the highest to lowest numbers, or only showing you blank cells - this is a great way to spot any missing data that you've accidentally omitted on a large spreadsheet.

Once you've mastered the rudimentary skills of sorting and filtering out data you need, then that would be an excellent time to move on to more advanced ways of sorting, macros and pivot tables, which are - although complicated-sounding - just more variations of data sorting. Often it's the first, basic, simple step such as auto filters that really lets you start to harness the true power of Excel, so why not give it a go?