OzGrid

Lessonn 31 - Sorting in Excel

< Back to Search results

 Category: [Excel]  Demo Available 

SORTING IN EXCEL

Lesson 31 - Excel Sorting. Sort Data in Excel. Level 1 Free Training

EXCEL SORTING

Download the associated Workbook for this lesson

Data sorting is an extremely handy and versatile feature within Excel. The sorting feature is found by going to Data>Sort to access the Sort dialog box. Generally, sorting is performed on a list, which is defined as a contiguous (no blank cells) group of data where the data is displayed in columns and/or rows. Excel allows you to sort Worksheet data alphabetically, numerically or chronologically. You can sort by columns, by rows, in an ascending or descending order and from left to right. When you sort within Excel, it will rearrange the contents of the sort area according to the instructions that you give it. Excel will always sort blank cells to the bottom of a list.

There are a few basic rules that you need to follow when setting up your list in the first place. These are:

  • Check That Any Numbers In Your Spreadsheet Are In A Numerical Format.
  • Cells Containing Both Text And Numbers Need To Be Formatted As Text.
  • Dates And Times Must Be Formatted Correctly.
  • You Must Unhide Any Hidden Rows Or Columns (We Will Cover Hiding Rows And Columns Later In This Lesson).
  • Make Sure You Have No Leading Spaces. This Can Happen When You Push Your Space Bar Before Typing Into The Cell.
  • Enter Column Labels In One Row (Use Alt + Enter To Put A Hard Return In If You Need To) Or Use The Orientation Feature Under Home>Alignment.

Excel has specific sort orders to arrange data within your Worksheet according to the value (not the format) of the data. If you performed an ascending sort, numbers would be sorted from the smallest negative number through to the largest positive number. If you performed a descending sort, numbers would be sorted in reverse.

Sorting Alphanumeric Text

If you performed an ascending (lowest to highest) sort on alphanumeric text, Excel will sort your data from left to right, character by character. For example, if a cell contained the text B200, Excel will place the cell after a cell that contains the entry B2 and before a cell that contained the entry B22.

Text that includes numbers and normal text are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Apostrophes (') and hyphens (-) are ignored, with one exception; if two text strings are the same except for a hyphen, the text with the hyphen is sorted last.

If you are sorting logical values, FALSE will always be sorted before TRUE and if you are sorting error values, they are all equal. We will be covering logical values in the last lesson when we look at the IF function.

As a tip, although it is not really necessary with the fabulous and very handy Undo feature, it is a good idea to save your Workbook prior to performing a sort, just in case.

Let’s have a go at performing some different types of sorts.

Single Column Sort

  • Open The Workbook Data Sorting. Xls.
  • Make Sure You Are On The Sorting With Text Sheet Tab.
  • Click In Cell D5.  
  • Go To Data>Sort To Display The Sort Dialog Box. You Will Notice Here That When You Do This, Your List Will Be Highlighted (Not Including The Headings). This Is, Because You Are Clicked In A Cell Within The List, Excel Will Search In All Directions From Your Active Cell For The End Of The List. The End Of The List Is Deemed To Be The First Blank Cell Encountered In All Directions, Up, Down, Left And Right. If You Manually Highlight Your Data And Excel Sees There Is Data Immediately To The Left, Right, Top Of Bottom Excel Will Ask If You Wish To Include The Data.
  • Notice In The Top Box Sort By You Have The Column Heading Staff Payroll No. Displayed.
  • Click The Drop Down Arrow To The Right Of The Sort By Box And You Will See The Other Column Headings From Your List.
  • We Wish To Leave The Default Last Name As Our Choice As This Was The Column That We Have Clicked In.
  • Ensure The Ascending Option Box Is Ticked.
  • At The Bottom Of The Sort Dialog Box Under My List Has Make Sure That Header Row Is Selected. If You Had No Header Row Selected, Excel Would Include The Headings In Your List As Part Of The Sort Operation.
  • Click On OK To Sort Your Data By Last Name.

You will notice now that the Last Name column is sorted in alphabetical order, from the lowest value to the highest value.

Let’s reverse the order from Ascending to Descending.

There are two ways you can do this.

  • Stay Clicked In Cell D5.
  • Go To Data>Sort To Display The Sort Dialog Box.
  • Ensure The Descending Option Box Is Ticked.
  • Click On OK To Sort Your Data By Last Name In Descending Order.

OR

  • Click The Sort Descending Tool, Which Is The Z On Top Of The A Next To A Downward Pointing Arrow On Your Standard Toolbar.

By selecting this icon, you can effectively by-pass the Sort dialog box and perform a Descending sort. You will note that to the left of the Sort Descending icon is the Sort Ascending icon. If you have previously performed a sort on a list, Excel will remember the last sort that you did, but be aware that if you are uncertain of the last sort, your list data may not sort as you expect. In other words, if you are using a file or computer that is shared, it is always best to use the Sort dialog box.

Sorting by Multiple Columns

When you perform a multiple column sort, each column is sorted one at a time. The list will be sorted by the first column then Excel will check each of the entries to see if there are duplicates. If there are, then the duplicates will be sorted by the second column and so forth.

  • Click In Cell E5.
  • Select Data>Sort To Display The Sort Dialog Box.
  • Click On The Drop Down Arrow To The Right Of The Sort By: Box To See The Other Column Headings.
  • Click On Department.
  • Select The Ascending Option.
  • Select The Drop Down Arrow To The Right Of The Then By: Box.
  • Select Last Name From The List.
  • Select The Ascending Option.
  • Click On OK.

Now you have performed a sort on more than one column, let us have a look at sorting numbers. These will work just as efficiently as text when performing a sorting operation.

Sort by More Than 3 Columns

Excels sort feature only allows to nominate up to 3 columns to sort by, here is how to get around this. The key to this is sorting by the last key first and working back to the first key. Say you data is in Column A:E and you want to sort by A, B, C , D then E

1. Select all of Columns A:E
2. Go to Data>Sort> sort by C then by D then by E
3. Click Sort
4. Now again with Columns A:E selected
5. Go to Data>Sort> sort by A then by B
6. Click Sort

Sorting Numbers

Let’s find out which Department generated the most income during the month of June.

  • Click On The Sorting With Numbers Worksheet Tab.
  • Click In Cell G5.
  • Select Data>Sort.
  • Ensure That The Sort By Box Has June In It.
  • Select Ascending Order.
  • Select OK.
  • Now Click In Cell A5 And We Will Sort The List By Departments.
  • Select Your Descending Icon On Your Standard Toolbar.

As you can see, sorting is a very simple operation to perform with either text or numbers. You can also sort from left to right in rows, this is a little trickier, but you will find it a very handy feature to know. Let's have a go at performing a left to right sort on our Sorting with Numbers Worksheet.

  • Highlight The Range B5:G12. Quick Way – Click In B5, Move The Mouse Over Cell G12, Hold Down The Shift Key And Press Your Left Mouse Button – Quick And Easy.
  • Go To Data>Sort To Display The Sort Dialog Box.
  • Click On The Options Button To See A List Of Options Available To You.
  • Under Orientation Select Left To Right.  
  • Click On OK To Return You To The Sort Dialog Box.
  • In The Sort By Box, Click On The Downward Pointing Arrow To The Right And Select Row 12.
  • Click OK.

As you can see, sorting, once mastered, can be a huge benefit to a user. Remember to set up your data in a list. That is with column headings (defined in some way, such as bolded and centered) and without any blank cells, rows or columns. If you don’t, your sorting operation can turn into a nightmare! Remember the very handy undo key that you can use if you make an error, however, it is a good habit to get into to save your Worksheet first before performing a sort.

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

 

Go back to:

Lesson 1 - Excel Fundamentals
Lesson 2 - Starting Excel and Excel Workbooks
Lesson 3 - Excel Toolbars and Task Panes
Lesson 4 - Excel Worksheets
Lesson 5 - Excel Cells and Navigating a Worksheet
Lesson 6 - Excel Cut/Copying and Pasting Data
Lesson 7 - Excel Copying with the Fill Handle
Lesson 8 - Excel Paste Special
Lesson 9 - Excel Insert Command
Lesson 10 - Excel's default options
Lesson 11 - Excel's Undo and Redo
Lesson 12 - Excel's Format Painter
Lesson 13 - Excel's Dates and Times
Lesson 14 - Excel's Custom Formats
Lesson 15 - Excel Formulas
Lesson 16 - Excel Cell References
Lesson 17 - Excel: Avoid Typing
Lesson 18 - Excel Formulae Arguments & Syntax
Lesson 19 - Excel Autosum Formula
Lesson 20 - Excel Auto Calculate
Lesson 21 - Excel's Insert Function
Lesson 22 - Excel's Useful Functions
Lesson 23 - Excel's Named Ranges
Lesson 24 - Excel's Constants and the Paste Name Dialog
Lesson 25 - Excel's Calculations
Lesson 26 - Excel Comments Cell
Lesson 27 - Excel Find and Replace
Lesson - 28 - Clear Excel Cell Contents
Lesson 29 - Effective Excel Printing 1
Lesson 30 - Effective Excel Printing 2

See also:

Lesson 32 - Hide/Show Row/Columns in Excel
Lesson 33 - Auto-Formats in Excel
Lesson 34 - Creating a Basic Excel Spreadsheet
Lesson 35 - Excel Charting Lesson: The Basic Excel Spreadsheet
Lesson 36 - Excel Worksheet Protection
Lesson 37 - Excel IF Formula Nesting
Lesson 38 - Excel Function Now/Today Formulas

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 


Gallery



stars (0 Reviews)