Working with tables in many cases requires an analysis of the data contained in them. Excel provides powerful tools for processing information, the ability to build statistical reports and sample data on the condition. But sometimes it’s enough just to sort the elements to solve the task.
Ordering numbers
Sorting numbers in ascending or descending order is a common task. It helps to build numerical parameters - the number of goods and customers, the amount of sales and income, and present information in a more visual way. Let's sort this data in Excel.
For example, a travel company’s file table stores information about the cost of a hotel stay. The price depends on the type of room and the food offered. A tourist is interested in finding the most economical accommodation, so you need to sort the data by price increase.
To do this, select the column of the table "Cost, rubles." and go to the "Home" tab. In the “Editing” block there is a group of tools “Sort and filter”. Choose “Sort Ascending” from the menu.
The program, having detected the presence of related columns, specifies whether to sort in Excel only by the selected field, or expand the range.
Choose “Expand”, since we are interested in organizing the full records of the table, and click “Sort”. Entries are arranged in ascending order of cost of living.
This operation is even simpler if the table is formatted by style. In this case, you need to call the context menu of the column by clicking on the drop-down list in the column header. The menu contains similar commands with which you can sort in Excel.
In order for the numbers to be sorted correctly, pay attention to the correctness of the data format in the column. If the values are entered in the table as text, not a number, the alignment will not take place in the expected order. The table shows an example of sorting the numbers 10, 11, 100, 15, 110, 132 depending on the data format.
| Number format | Text format |
| ten | ten |
| eleven | one hundred |
| 15 | eleven |
| one hundred | 110 |
| 110 | 132 |
| 132 | 15 |
Text ordering
In Excel, the sorting of columns containing text is the same as the operation with numbers. We will produce this command in the column of the table of prices for rooms in the hotel “Food”, already known to us. Select a column by clicking on its heading and open the "Edit" toolbar of the "Home" tab. Now, in the drop-down menu, instead of sorting commands in ascending and descending order, sorting commands from A to Z and vice versa are located.
We are constructing cells from A to Z, again indicating the expansion of the range of operations.
When arranging the string data, make sure that the values do not contain leading spaces, since in this case the desired cell order will be violated. This problem happens if the information is uploaded to a file from another source: a database, a third-party application, or another file.
For formatted tables, sorting in Excel is even easier. By going to the context menu of the column heading, you will also see commands for constructing cells in the order from A to Z and vice versa.
Date Ordering
All of the above operations, applicable to numbers and string information, are also used to arrange dates and times. The difference is only in the name of the command, for this type of data it is called "Sort from old to new" and vice versa. An example is shown below.
For dates, the same rule applies as for numbers: for ordering to work correctly, make sure that the sorted information is presented exactly in the date format, and not in text.
Custom lists
But it’s not always possible to build the necessary list using regular tools, it may be necessary to sort in Excel by fields that none of the methods described above apply to. For example, in the to-do list for the week, we want to build tasks by day of the week.
To perform this sorting, the program provides the ability to use custom lists. MS Excel has a number of predefined lists, one of which is a list of days of the week. To see them, go to the "Data" tab and find the Excel sorting and filtering toolbar. The “Sort” button opens a window for performing operations on several columns.
In the "Order" field, select the "Custom List" element. From the list, select the days of the week and click OK.
Sorting by color and icons
Another convenient option for arranging cell elements in order is the ability to sort them by color. In this case, the color of both the fill and the font of the text can be used. In the to-do table, we designate work tasks in orange, family affairs in blue, holidays in beige and yellow. And we will build them in type.
To do this, go into custom sorting. In addition to the method described above, you can use the menu of the "Edit" panel of the "Home" tab. Select the item “Custom Sort” from the list of “Sort and Filter” commands.
Select the "Task" field, type "Cell Color" and set the orange color on top. So that household chores are at the bottom of the list, we add one more level and for the same parameters we set the color blue below.
This method allows you to organize the table elements and by icons. For example, we can mark individual tasks with an exclamation mark as important and display them at the top of the list.
Above, we described how to sort data of different types in Excel using the color of the cells and font of the text and marking in rows. We hope this information will help you in your work with the program.