How to Sort a List in Excel: 15 Steps (with Pictures)

Table of contents:

How to Sort a List in Excel: 15 Steps (with Pictures)
How to Sort a List in Excel: 15 Steps (with Pictures)
Anonim

Excel is great for displaying tables of data, but how can you manipulate and organize them to suit your needs? The Sort tool provides the ability to quickly sort columns in various formatting styles. In addition, you can create your own sorting for multiple columns and types of data. Use the Sort and Filter feature to logically organize your data so it's easier to understand.

Steps

Part 1 of 3: Sort by alphabetical or numerical order

Sort a List in Microsoft Excel Step 1

Step 1. Select your data

You can make a selection of the column you want to sort by clicking and dragging, or click on one of the cells in the column to activate it, after which Excel can automatically select the data.

All data in the column must be formatted in the same way (eg text, numbers, dates) in order to sort the entire column

Sort a List in Microsoft Excel Step 2

Step 2. The Sort buttons

These can be found in the Home tab or the Data tab in the "Sort & Filter" group. To sort quickly you can directly use the "AZ↓" or "AZ↑" buttons.

Sort a List in Microsoft Excel Step 3

Step 3. Sort your column

If you want to sort numbers, you can sort from smallest to largest ("AZ↓") or vice versa ("AZ↑"). To sort text, sort in ascending ("AZ↓") or descending ("AZ↑") numerical order. To sort dates or times, sort from early to later ("AZ↓") or vice versa ("AZ↑").

If there is another column of data next to the column you want to sort, you will be asked if you want to include that column in the sort. Sorting is based on the data in the column you initially selected. The data in the corresponding columns is then co-sorted, so that the data for each row remains correct

Sort a List in Microsoft Excel Step 4

Step 4. Check what goes wrong with columns that cannot be sorted

If you find that sorting fails, the most likely error is data format mismatch.

  • To sort numbers, make sure all cells in the column are formatted as numbers, not text. Numbers are sometimes accidentally imported as text from accounting programs.
  • When you sort text, errors can occur due to spaces at the beginning or incorrect formatting.
  • If you want to sort dates or time, problems often arise due to the formatting of your data. Just like with numbers and text, to ensure that Excel sorts dates correctly, make sure that all cells in the column are formatted as dates.

Part 2 of 3: Sort by multiple criteria

Sort a List in Microsoft Excel Step 5

Step 1. Select your data

Suppose you have a spreadsheet with a list of customer data (name and city). To make your life easier, it's a good idea to first sort alphabetically by city and then by customer name (alphabetical by city). You need a custom sort for this.

Sort a List in Microsoft Excel Step 6

Step #2. Click on Sort

This can be found in the "Sort & Filter" group of the Data tab. The Sort pane now opens with several options to create a custom sort.

If there are headers/labels above the columns in the first row, such as "City" and "Name", make sure the "The data contains headers" option in the top right corner of the sorting box is checked

Sort a List in Microsoft Excel Step 7

Step 3. Create your first sort rule

Click "Sort by" to choose the column you want to sort. In this example you first want to select by place of residence; so select the correct column in the menu.

  • Leave "Sort by" set to "Values".
  • Set the order from "A to Z" or from "Z to A" depending on how you want to sort.
Sort a List in Microsoft Excel Step 8

Step 4. Create a second sort rule

Click on "Add level". This adds a sort rule below the first line. Select the second column (e.g. the Name column of the example) and then choose a sort order (choose the same order as the first line, for readability).

Sort a List in Microsoft Excel Step 9

Step 5. Click OK

Your list will be sorted according to the rules you have established. You should now see the cities in alphabetical order next to the customer names associated with a city.

This is a simple example and contains only 2 columns. However, you can make the sorting as complex as you want for multiple columns

Part 3 of 3: Sort by cell or font color

Sort a List in Microsoft Excel Step 10

Step 1. Select your data

You can either click and drag to select the column you want to sort, or click on one of the cells in the column to activate it, and Excel can automatically select the data.

Sort a List in Microsoft Excel Step 11

Step #2. Click on Sort

This can be found in the "Sort & Filter" group of the Data tab. The Sort pane now opens. If there is another column of data next to the column you want to sort, you will be asked if you want to include that column in the sort.

Sort a List in Microsoft Excel Step 12

Step 3. Select "Cell Color" or "Font Color" from the "Sort by" menu

You will be given the option of choosing a color to sort by first.

Sort a List in Microsoft Excel Step 13

Step 4. Select the sort color

In the "Order" column, you can use the drop-down menu to choose a color to sort by first. You can only choose the colors that you have used for your data.

There is no default order for sorting by color. You will have to specify the order yourself

Sort a List in Microsoft Excel Step 14

Step 5. Add another color if necessary

You can add a sort rule for each color that you have used in the column you want to sort. Click "Add Level" to add another rule to the sort. Choose the next color you want to sort by and the order to keep.

Make sure that every order is the same for every line. For example, if you're sorting from top to bottom, make sure Order says "Top" for each line

Sort a List in Microsoft Excel Step 15

Step 6. Click OK

Each sort rule will be applied one by one and the column will be sorted by the color you specified.

Tips

  • Try sorting the data in different ways; this can provide a whole new understanding of the data you have collected.
  • If your spreadsheet has totals, averages, or other summary information at the bottom of the data you want to sort, be careful not to include this information in the sort.

Popular by topic