Written by Andy Trainer – Thu 29 Jan 2015
Use these step by step instructions to sort your lists with maximum efficiency in Excel.
Excel can sort columns into order alphabetically and numerically. You can perform a single column sort of multi column sort.
The Sort command can be found on the Home tab under the Editing group.
Important: When setting up the list, include a set of column headings (as example below). These are used to control the sort columns. The list must be sequential, i.e. no row gaps from the top of the list to bottom.
Click into a cell in the column that you wish to sort by, as in the example below the First Name column.
Select the Data tab, then to sort by A to Z or Z to A.
You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns.
The table is sorted by the selected column.
Note: Data sort can also be found under the Home tab, under the Editing group.
A list can be sorted by a maximum of 64 columns. In the example below you may wish to sort by Company, then Department, Job Role, Last Name and First Name. A five column sort.
Click in a cell within the table.
Select the Data tab, then click on Sort in the Sort and Filter group.
Select the first Column to sort by from the Sort By box, i.e. Last Name.
Select to Sort On Values.
Select the order A to Z or Z to A.
As we are sorting by more than one column we now need to add the further levels.
Select Add Level.
Choose then to Sort By - Department on Values.
Repeat this process until the five sort levels are set.
Note: Make sure that the tick is in the box My data has headers, otherwise the headings will be sorted into alphabetical order as well.
Click on OK.
When a list has been sorted into order, it is very easy to ask Excel to subtotal a list for you. Say for instance that you wished to sort by Department and then subtotal the Departmental salaries.
Other Options in the Subtotal Box
Replace Current Subtotals
If further subtotals are required in the same list, de-activate this box. Within a list you could find the sum of salaries and count how many people in the department. To do this change the sum function to count.
Page Break Between Groups
Ensure each subtotalled Group is on a separate page.
The list is subtotalled. An Outline Format is created; brackets appear on the left hand side of the screen. These can be used to Expand or Collapse the list. In addition at the top left of the screen, buttons appear that enable the list to be Expanded or Collapsed.
You can have multiple subtotals in a list, so you could choose to find average salaries per department. Just make sure you take off Replace current subtotals.
Note: If you have several subtotals, remove all will take all of them away.
Quite often it is necessary to look through a list of information for records meeting a certain criteria, i.e. everybody that works in the Marketing Department, earning over £15,000. It is very easy to extract information from a list (Database) by using Excel's Autofilter. This enables selection from various columns within the database.
A series of filter arrows are placed alongside the headings. These are used to filter the information.
Objective: To find all personnel working in the marketing department
Records that do not match are hidden and only records matching the Marketing Department are displayed.
Notice that the filter arrow changes to indicate the column that has been filtered. You will see this symbol on all columns that have been filtered.
To see all the records select Clear from the Sort and Filter Options, or choose Select all from the filters arrows of the filtered columns.
Excel 2010 also provides filters relevant to the type of column. Columns can be made up of text, numbers and dates. Select the filter arrow to the right of the column heading to see the type of filters available.
Select the Data tab, click on the Filter button to turn this option off.
ITIL®, PRINCE2®, PRINCE2 Agile®, MSP®, M_o_R®, P3O®, MoP®, MoV® courses on this website are offered by The Knowledge Academy, ATO of AXELOS Limited. ITIL®, PRINCE2®, PRINCE2 Agile®, MSP®, M_o_R®, P3O®, MoP®, MoV® are registered trade marks of AXELOS Limited. All rights reserved.