How to Sort data in Microsoft Excel 2013

Databases in Excel – introduction

A Database is a structured collection of information.

A Database in Excel is structured in a very particular way:

The data is broken up into separate Fields that can be applied to every record in the database e.g. an addresses database might have fields for first name, last name and so on. These fields make up the columns of the database. The field names are usually in the first row of the database.

Each subsequent row contains the data for each Record – one record per row.

Databases or lists have NO blank Columns and NO blank Rows. It is possible to have a blank cell, however as soon as Excel detects a blank row or a blank column the rest of the data is treated as a separate list. We will see an example of this a little later on.

Figure – Structure of a database

1. Field Names in the first row
2. Records – one separate record in each subsequent row

Sort data

In the Coffee Island sales list, there are different columns. Depending on the type of data analysis that needs to be done, the data will need to be sorted in different ways.

There are several ways to get to the tools to sort data. There is a Sort on the Home Tab, more detailed Sort options on the Data tab and of course there is a right click option. You can also add the Sort Icons to the Quick Access Toolbar.

To perform a simple sort from the Home tab

Figure – Click into the column to sort by and then sort

1. Click anywhere in the column you want to sort by
2. Click on the Home tab
3. Click on the Sort & Filter button (1)
4. Select Sort A to Z, Or
5. Select Sort Z to A

To perform a simple sort using the Data tab

Figure – Sort from the Data Ribbon

1. Click anywhere in the column you want to sort by
2. Click on the Data tab on the Ribbon
3. Go to the Sort & Filter group
4. Select Sort A to Z, Or
5. Select Sort Z to A

Sort with a right click

Figure – Sort on a right click

1. Click into the column you want to sort by
2. Right Click
3. Click on Sort
4. Select how you want to sort the data

To perform a custom sort (Multi Level Sorting)

Figure – Custom or multi-level sort

1. Click on the Data tab
2. Click on the Sort button
3. Click on the down arrow by Sort to select the Column you want to sort by
4. Choose what you want to sort on – Values, Cell Colour, Font Colour, Cell Icon
5. Select the Order you want to sort by, A-Z, New to Old, Z-A, Old to New depending on the data.
6. If you want to sort on more than one column: Click on the Add Level button
7. Repeat steps 3-5
8. Click OK

To delete a level, click on the Delete Level button

You can also access the Custom Sort via the right click option.

Change the order of the sort levels

Figure – Change the order

If you want to change the sort order, click on the up or down arrows along the top of the Multi-Level sort box. Here I have changed the order to Item first then CentreCentre.

Happy Learning!

This topic has been taken from my free eBook on Microsoft Excel Core: Advanced 2013 which can be downloaded here:

To find out more about our training and to discuss your organisation’s learning needs, give us a call on 020 8203 1774 or email us enquiries@theittrainingsurgery.com – we would love to chat.