Unit 25: Sorting Data

Introduction

This document covers both the simple and more advanced sorting facilities in Excel and also introduces you to subtotals and outlines.

Sorting Data in Excel: When sorting data in Microsoft Excel, it’s very important to decide whether you want just part of the data sorted or the whole dataset. To understand how sorting works, try it out on an example file:

  1. Load up Excel and [Open] the file called phoenix.xlsx in the D:\Training folder

  2. Decide which column you want to sort on (eg: Column B) and click on any cell in that column

  3. Click on the [Sort & Filter] button in the Editing group on the right of the HOME tab and choose Sort Smallest to Largest to sort the data into increasing values

  4. Repeat step 3 but choose Sort Largest to Smallest to sort the data into decreasing values

  5. Press (or click on the [Undo] button) twice to return the data to its original order – or, with this data, you could use [Sort & Filter] on Column A

Note how all the data in the rows moved together as the sort was applied. Excel sorts blocks of data, delimited by blank rows or columns. This next exercise demonstrates this very clearly:

  1. Select all the data – press

  2. Press to [Copy] the data, click on cell I1 and press to paste in the copied cells

  3. Right click on the 15 in the row numbers down the left and choose Insert to insert a blank row

  4. Click on any cell with data in column J then on [Sort & Filter] and choose Sort Smallest to Largest – you will find that only the area of data containing that cell is sorted

  5. Press (for [Undo]) to return the data to its original order then again to remove the empty row.

 

Sorting Selected Data

If you only want to sort part of the data, you have to select it first:

  1. Click on the column heading letter B to select that column

  2. Click on [Sort & Filter] and chose Sort Smallest to Largest – a warning message appears:

  3. Select Continue with the current selection – press for [Sort]  You will find column B is now sorted but the rest of the data hasn’t moved. This could be a disaster if the rows represented data records (as they do here – the data is now corrupted). Fortunately, the default is to sort all the columns (you had to reset this option at step 3).

  4. Press (or click on [Undo]) to return the data to its original order The warning doesn’t appear if cells in two or more columns are selected, as you’ll see next. You can sort on more than one column in a selection, but the columns must be next to each other (ie you can’t select – if necessary, move the columns around to get them in a suitable order) and sorting is carried out based on the left-most column:

  5. Drag through the column heading letters B to E to select those columns

  6. Click on [Sort & Filter] and choose Sort Smallest to Largest – all four columns are sorted, based on column B, with no warning

  7. Press (or click on [Undo]) to return the data to its original order. You can also sort on part of one or more columns

  8. Drag through cells A1 to G15

  9. Click on [Sort & Filter] and choose Sort Largest to Smallest – just those cells are sorted

  10. Press (or click on [Undo]) to return the data to its original order

If you wanted the sort based on column B then you have to use Custom Sort…, as you’ll see next.

UNIT 25 – Sorting Data


There aren't any posts currently published in this category.