Sorting

The quickest way to sort a table of data is to use the two sorting buttons on the Standard toolbar.
These two buttons allow you to sort your table based on a single column into either ascending or descending order.
To sort a single column just select any cell within the column you want to sort. Press either the ascending or descending button.

	Ascending - Sorts the data alphabetically from 1 to 100, A to Z, Jan to Dec and numerically from the lowest number to the highest. This is the default.
	Descending - Sorts the data alphabetically from 100 to 1, Z to A, Dec to Jan and numerically from the highest number to the lowest.

When you want to sort a table of data, there is no need to highlight the whole table first as the current region is automatically assumed when you only have a single cell selected.
Excel assumes that the list of data you want to sort is in a contiguous block separated by at least blank row and column.
If you have highlighted only a selection within a contiguous block then Excel will warn you that you might not have the entire list selected and asks to confirm. The following dialog box is displayed.

Using the (Data > Sort) dialog box

Using the (Data > Sort) dialog box allows you to sort on as many as three columns at once.
If you have a table of data you want to sort there is no need to highlight the whole table before pressing (Data > Sort) as the current region will be selected for you automatically.
Data is sorted in the following order: - Numbers, Text, Logical values, Error values, Blank cells.
Select any cell in the range "B2:C9" and when you select (Sort > Data) the whole current region will be selected automatically.

Example of a table of data with no heading row

Your table of data may or may not contain a header row. A header row is the first row at the top of your table containing heading labels for each of the columns.
Assuming your table does not contain a header row the column letters are displayed in the drop-down boxes to allow you to identify the columns you want to sort.
The column containing the active cell is assumed to be the column you want to sort by first and will appear selected by default.

(Data > Sort) dialog box without Header row

Select the column letters in the order you want to sort the columns and then choose whether to sort the column into ascending or descending order.
If you have sorted that particular table of data before then the dialog box will display the parameters you last used.
You can easily undo the sort operation afterwards if you do not like the results by selecting (Edit > Undo) or pressing the Undo button on the Standard toolbar.

Using a Header Row

You table of data can contain a header row that can stay in place while all the other rows are sorted.

Example of a table of data with a heading row

When your table of data contains a header row the actual headings will be displayed in the drop-down boxes and not the column letters.
The column containing the active cell is assumed to be the column you want to sort by first and will appear selected by default.

(Data > Sort) dialog box with Header row

Excel can normally recognise if your table of data contains a heading row or not and will change the option button accordingly.
You can change whether to include a heading row or not with the two option buttons at the bottom of the dialog box.

(Data > Sort) Options dialog box

This is normally worth checking before you sort the data. If you accidentally sort the heading row you can just press (Edit > Undo).

Sorting cells with Formulas

It is possible to sort cells that contain formulas although be aware that some relative references will become invalid.
If you are sorting from top to bottom (i.e. rows) then any references to other cells in the same row will be unaffected.
Any references to other cells in different rows will become invalid.
The same is true when sorting by column, any references to other cells in different columns will become invalid.
you can avoid this potential problem by making sure that any cells referenced outside the table use absolute references or named ranges.
If your table of data is not too large, manually moving the rows or columns will maintain the correct references.

Sorting a single column

When you perform a sort on a single column of cells there is a chance that the continuity across the table may be broken.
Excel will now always prompt you to confirm the selection made when you try to sort a single column of data.
This gives you option of automatically expanding the selection to include the other columns in the table.

Sorting more than 3 columns

It is possible to sort on more than 3 columns if you need to.
You just need to sort the columns starting with the least important one first and finishing with the most important one.

Automatic Header Row

When you have text column labels and numerical values below Excel will automatically recognise the first row as a header row.
When you sort your list the header row will stay in place.

Important

When you want to sort a table of data you do not need to select the table first as the current region will automatically be used or selected for you.
Excel will always sort the data based on the actual contents of the cell and not on the value that is displayed. This can be important if you are sorting dates.
It is possible to only sort on part of a list as long as you select the cell range first. This applies to using the buttons on the standard toolbar as well as the (Data > Sort) dialog box.
If you are trying to reduce the size of your toolbars you can remove the descending sort button from the Standard toolbar. The next time you want to sort a column in descending order hold down the Shift key while pressing the ascending order button.
Excel sorts objects, along with data as long as each object is contained within a single row or column.
Excel sorts the FALSE logical value before TRUE.
Any blank cells are always placed at the bottom of an Ascending Order list except when formulas return empty strings. When sorting these cells they appear below the numbers but before the text.

© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited