Excel is perfect for working on tabular data, but how do you manage and organize it in a way that meets your needs? The "Sort" tool allows you to quickly sort the data in a column based on different criteria, or to create a custom sorting algorithm based on multiple columns and different data types. Use the "Sort" function to logically organize your data, so it will be easier to understand its meaning and be able to make the most of it.
Steps
Part 1 of 3: Sort Alphabetically or Numerically
Step 1. Select the data you want to sort
To do this you can select a range of cells present in a column, or simply choose a cell in the column of your interest so that it is active: Excel will then automatically select the related data.
In order to be sorted, all the data in the chosen column must be formatted in the same way (e.g. text, numbers, dates)
Step 2. Locate the "Order" button
You can find it in the "Data" tab within the "Sort and Filter" section. To perform a quick sort, you can use the "AZ ↓" and "AZ ↑" buttons.
Step 3. Sort the selected column by pressing the appropriate button
If you want to sort a range of numbers in ascending order, from smallest to largest, use the "AZ ↓" button, if you want to sort it in descending order, ie from largest to smallest, use the "AZ ↑" button. If you want to sort text strings in ascending alphabetical order, use the "AZ ↓" button, while if you want to sort them in descending alphabetical order, use the "AZ ↑" button. If you want to sort a range of dates or times you can do it in ascending order using the "AZ ↓" button or descending using the "AZ ↑" button.
If the column you want to sort isn't the only one in the spreadsheet, choose whether to include the other columns of data in the sort as well. The sorting will still be applied to the chosen column, but the data in the other columns will be organized accordingly, to maintain consistency and referential integrity
Step 4. Solve common problems related to sorting a column
If you encounter any error messages when trying to sort a column of data, the problem is most likely in the formatting of the data.
- If you're trying to sort a set of numbers, make sure all cells are formatted as numbers and not text. Numeric data from certain accounting programs may be accidentally imported as text.
- If you are trying to sort text, the problem may come from incorrect formatting or leading whitespace in the text to be sorted.
- In case you are ordering a set of dates or times, the problem could be caused by the formatting of the data. For Excel to successfully sort a set of dates, all data must be formatted and stored as dates.
Part 2 of 3: Sorting with Multiple Criteria
Step 1. Select the data to sort
Let's assume that the spreadsheet contains the list of your customers' names, together with the city of residence. To use a simple approach, you can first sort the data alphabetically, by city of residence, and then by name. Using a custom sorting algorithm, you will solve your problem brilliantly.
Step 2. Press the "Order" button
Look for it in the "Data" tab, in the "Sort and filter" section. The "Sort" dialog will appear, allowing you to create a sorting algorithm based on multiple criteria.
If the first cell of each column corresponds to its header, in our example "City" and "Name", be sure to select the "Data with headers" check button in the upper left corner of the window
Step 3. Create the first sorting rule
Access the "Sort by" drop-down menu and select the column you want. In our example, the first sorting must be based on the city of residence, then select the appropriate column from the menu that appeared.
- Keep the default "Values" option for the "Sort by" menu.
- Set the "Order" field with the values "A-Z" or "Z-A", according to the type of sorting you want to apply.
Step 4. Create the second sorting rule
Press the "Add level" button: this will add a second sorting criterion subordinate to the first. Select the second column (in our example the one containing the names of the customers), then choose the type of order. For greater readability of the data, choose the same sorting used for the first rule.
Step 5. Press the "OK" button
The selected data range will be sorted according to the set criteria. The resulting list should be sorted alphabetically, first by city of residence, then by customer name.
This example is very simple and is based on two columns only, but the concept is also applicable to more complex data ranges based on multiple columns
Part 3 of 3: Sorting by Cell or Content Color
Step 1. Select the data you want to order
To do this you can select a range of cells present in a column, or simply choose a cell in the column of your interest so that it is active: Excel will then automatically select the related data.
Step 2. Press the "Order" button
Look for it in the "Data" tab in the "Sort and filter" section; the "Sort" dialog box will be displayed. If the column you want to sort isn't the only one in the spreadsheet, choose whether to include the other columns of data in the sort as well.
Step 3. From the "Sort by" drop-down menu, choose the "Cell Color" or "Font Color" value
This will allow you to choose the first color to sort by.
Step 4. Select the first color to sort by
Use the "Order" drop-down menu to choose which color to appear first - or last - in your order. The choice of colors will obviously be limited to those present in the data column.
In this case, there is no default sorting method. You will have to create it yourself based on the colors present
Step 5. Add another color
In order to complete the sorting algorithm, you will need to add a row for each color in the data to sort column. Press the "Add Level" button to create a second sorting criterion. Choose the following color in which to organize the data and the order in which to sort.
Make sure you choose the same order for each of the criteria that make up your sorting algorithm. For example, if you want your sort to be sorted from top to bottom, be sure to select the "From Top" value for the "Order" field of each of the criteria
Step 6. Press the "OK" button
Each criterion present will be applied to the selected column giving life to the desired sorting based on the order of the colors chosen.
Advice
- Sort the data according to different columns: the new perspective could be of great help and show you the information you were looking for in an instant.
- If, in addition to the data you want to sort, your spreadsheet has subtotals, averages, or summations, make sure you don't include them in the sort.
