Master Sorting in Excel – part 1

One of the primary steps in analyzing any data is sorting it. This sorting can be simple like ascending or descending on a given dataset. Once basic sorting is done on dataset, you may further expect to sort the data based on case sensitivity of text values, cell colors, font colors if you highlighted any data.

In Excel, sorting features has many of these capabilities in built itself. In these series of posts I will be explaining you ALL in & out about this feature in Excel. These posts will take you step by step from basic sorting to complex sorting of dataset.

Steps involved in sorting data in Excel are :

Each of the above steps can be further broken down individually as follows:

Step 1: Sorting by

Here you have to decide whether you want to sort the data by Rows or Columns.

Rows to be sorted could be from single column only without affecting the data in other columns. Or you could sort the selected rows from selected columns only.

Further, you could sort the column left to right or vice versa.

Step 2: Identify Data Type

Based on the data stored in rows or columns further options available for sorting will change. You would be then able to sort the data based on full cell values or partial cell values.

If you have selected a field which contains multiple datatypes e.g. Text, Numbers, Data, Blanks, etc. then following sort order of precedence is followed by Excel when ascending order is selected:

  1. Numbers in increasing – 1,2,3,etc.
  2. Text in alphabetic order – a,b,c,etc. if the text begins with a number e.e. 38 Dummy, then the 3, as text, appears before the ABCs.
  3. Logical values – False, True
  4. Error Values – #Div/0!, #N/A, etc.
  5. Blanks.

Hence, to get things to sort the way you want, you just need to make sure that all the cells in a selected field contain the same data type.

Step 3: Method to sort

There are different ways in Excel through which you can sort the data. With each method the complexities with which you can sort the data increases.

Detailed sorting has all the options which are available in partiality in previous three methods.

Step 4: Sort on

Now once you have selected detailed sorting method you be presented with a separate sort window. This window contains various options to sort on selected field as follows:

Step 5: Sort Order

Finally, now you can select the sort order in which you want the data to be sorted as follows:

 

Now let’s summarize some of the known issues / limitation with Excel sorting:

  • Cells should not be merged in selected data range else you would get error.
  • Regular cell formatting is not sorted but conditional formatting will be sorted. e.g. Cell Borders.
  • Formulas in cells referring to sorted cells may give different results after sorting. Need to double check.
  • Hidden rows and columns are not considered while sorting the data.
  • Column heading not converted to BOLD may also get sorted with other data.
  • Numbers stored as text may not be sorted properly. Re-check the alignment of numbers to identify whether those are stored as text.

We will look at a more details on each of the above feature in next post with sample dataset.

To stay updated Subscribe immediately.

Leave a Reply

Your email address will not be published. Required fields are marked *