Practical Excel skills sharing: Explore the "little secrets" hidden in automatic sorting

It is said that there are hidden secrets in Excel. The MAX function for finding the maximum value can be used for searching, and the LOOKUP function used for searching can round the data... Even Excel’s automatic sorting, which everyone seems to know, has many hidden secrets. The “little secret” we don’t know. Today we will explore these "little secrets" hidden in automatic sorting.

1. Expanding the selection

"Teacher Miao, why is my sorting wrong? ", hearing Xiaobai's call, I walked over quickly.

"Look, I just want to sort this table by serial number." (As shown in Figure 1)

Picture 1

"But after finishing the arrangement, it turned out like this. The numbers were arranged, but the names did not change with the numbers. The names and serial numbers were all messed up."

Figure 2

"When this problem occurs, you must have not expanded the selection. Usually when you select a column of data in the table for sorting, such a window will pop up", as shown in Figure 3.

Figure 3

Generally, by default, "Extend selected area" is selected and then click "Sort" to complete the overall sorting. Or before sorting, select the entire data area and then click Sort to complete the overall sorting and this reminder window will not pop up again.

2. Primary and secondary keywords for multi-column sorting

When we perform multi-column sorting, we often encounter such a category Question: If there are two columns of data that need to be sorted, how do we distinguish between primary and secondary? As shown in Figure 4, the following table needs to be sorted first by "department" and then by "region".

Figure 4

At this time, do not click the "Ascending" and "Descending" buttons directly, but click the "Sort" button first, as shown in Figure 5.

Figure 5

After opening, as shown in Figure 6.

Figure 6

We add all the sorting conditions that need to be set into the setting box. After adding a condition, click "Add Condition" to add the third 2 conditions, as shown in Figure 7.

Figure 7

After completing the settings, click "OK", and you will get a table sorted first by "Area" and then by "Department", such as As shown in Figure 8.

Figure 8

Since what we are starting to do is a table sorted by "department" first and then by "region", we want to achieve this effect. , you need to change the primary and secondary keywords. How to change it? Just click the small triangle in the sorting window, as shown in Figure 9.

Figure 9

Here we select the "Main Keywords" row, click the "Down" arrow, and finally "OK" to achieve what we originally wanted. desired sorting effect. As shown in Figure 10.

Picture 10

3. Sort by color

"Teacher Miao, I touched again I have a question." Xiaobai sent another table. She said: "I have some cells marked with a yellow background. Now I want to separate the yellow parts from the ones without a background. What should I do? I copy and insert I've been doing this for a long time."

"It's sorting. If you copy and insert one by one like this, you won't be able to finish it tomorrow."

"Can this also be sorted? You need to use Function?"

"No. In fact, this function is in the sorting, but you don't pay attention to it."

said and opened her table, as shown in Figure 11.

Picture 11

In the "Data" tab, click "Sort" to enter the "Sort" setting box. Since our data has titles, we need to check "Data contains titles". Then set the "Sort by" to "Cell Color". In "Order", you can choose whether to sort colored data or uncolored data first according to your needs. Here we set the colored cells to be ranked at the top. As shown in Figure 12.

Figure 12

After clicking "OK", you can separate the colored cells from the uncolored cells, as shown in Figure 13

Figure 13

In fact, in the "Sort by", there are other options, such as sorting by font color, etc. You can try it yourself. As shown in Figure 14

Figure 14

4. Custom sorting

In fact, In addition to the sorting mentioned above, there is another function that is most easily overlooked by everyone, which is "custom sorting", which is also hidden in the "sorting" interface. As shown in Figure 15.

Figure 15

We can sort numbers and text, but what if we need to sort specific content? For example, the list of branches in various locations, the sequence of positions and leaders within the company, etc. They all have a specific order and often need to be produced or arranged in daily work. The picture below shows the order of departments in our company. Suppose it is disrupted. How can we restore it to the normal order?

Figure 16

At this time, it is definitely impossible to use ordinary ascending and descending order, so this sequence needs to be set by ourselves. In the sorting settings box, click "Custom Sequence" to open the custom sequence settings box, as shown in Figure 17.

Figure 17

In the "Input Sequence" area, create the sequence we need. For example, if we want to make a sequence of departments, in the "Input Sequence" area, enter the department names in sequence, and press the Enter key to separate each department name. After completing the input, click "Add" and the sequence just added will appear in the "Custom Sequence" on the left. As shown in Figure 18.

Figure 18

After the setting is completed, just select "Custom Sequence" in the sorting setting box, click on the set department sequence, and the data can be The order we set is sorted. As shown in GIF19.

GIF19

Extended functions: In addition to the usage mentioned above, using custom sequence sorting, department names can also appear in a drop-down manner, as shown in GIF20 Show.

GIF20

With this function, as long as you write down the first department name or the first leader name, you can complete the entry of the sequence through the drop-down method. No more worries about confusing department names or missing leader names! How is it? Is it useful?

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: Explore the "little secrets" hidden in automatic sorting. For more information, please follow other related articles on the PHP Chinese website!