Safeguards to Take Before Sorting Data
Save Your Original Table Before Sorting
When you save a workbook, sorts get saved with the workbook. When you reopen the saved workbook, you cannot revert back to that table’s original configuration you had prior to sorting. In case you ever need the table in it’s original configuration, before you do any type of sorting, save the table, preferably with the name “original table”, or some type of identifier that flags this table version as the original table. If you originally obtained the table through an email from a colleague, retain that email.
It is not a problem to get back to your original table configuration if you have not saved the workbook. Simply select the Undo button until you are back to it.
Missing or Mixed Up Data
Whenever you do a sort, there is always the possibility that Excel could omit some data from the sort and scramble your data.
The most common cause for this problem is that Excel isn’t recognizing all your data. If you select a single cell in the data table, and then click on either the Sort Ascending or Sort Descending tool, Excel makes its best guess as to what data you want sorted.
2 Safeguard Checks to Prevent Data Loss / Mixed Up Data:
- Press Ctrl + Shift + * (asterisk). This shortcut selects the region around the current cell. In theory, when you start a sort from a single cell, Excel initiates this command before doing the actual sort. If you press Ctrl + Shift + * before the sort, you can get an idea of exactly which columns and rows Excel will sort.
- To make sure there is no confusion in what Excel actually sorts, all you need to do is select the range of columns and rows that you want sorted, and then do the sort. This may often mean selecting all of the data before doing the sort.
Sorting with Filter Buttons: Using the filter buttons on the column headers is one way to sort. If you want to see all of the associated columns in the table besides the sort columns, ensure that Excel sees all of that data by selecting all of the data with the mouse. If you only want to see the data in the column(s) you are sorting by only, then only those column(s) are the only ones that need to be selected prior to the sort.
If you want to do a multiple column sort using the filter buttons, perhaps a 3-column sort, the column that you want to use as your number one sort criteria will be selected last. The second column priority is selected second, and the third column priority is selected first.
When a filter button is being used in a sort, the filter button icon will look differently from its default, downward-pointing arrow that is shown when it’s not used.
Sorting with a Custom Sort: When you want to do a multiple column sort you can define which columns you want to sort by first by making that selection first in the Custom Sort dialog box. Then the second column to be sorted is your second addition, and the third column is your third selection.
Sorting in Ascending (A to Z) or Descending Order (Z to A): When you select the choice in the drop-down menu, a popular choice although not always a choice is Sort A to Z. When selected, it will sort alphabetically (text) and from 0-9 (numbers). Date/time values are from earliest to latest. Logical values are listed FALSE first, then TRUE.
When selecting the choice Sort Z to A, it is the exact opposite of an ascending sort, A to Z. it will sort alphabetically from Z in the alphabet first, and continue to sort by it’s alphabet placement from Z going back to A. Numbers are also sorted backwards, placing first priority on 9 and sorting in priority in numbering going down to 0.
The choices you see in the drop-down menu will be based on the data in the column, but this same scheme works in the same way with other choices listed.