How to sort in excel(11 examples), include sort by column,color,date,row and use shortcut keys

In Excel, sort can be sorted by "Continue width the current selection" and sorted by "Extend the selection", one column is sorted by the former, and one row is sorted by the latter. Excel can sort numeric and text, in addition, it can sort by cell color, font color, and cell icon. Numeric and text sorting can be divided into ascending and descending order. Sorting by cell color and font color can only sort the selected color to the front or the back. Sorting by cell icon can only sort the selected icon to the front or the back.

By default, Excel sorts by only one condition, but you can customize two or more criteria. In addition, Excel sorts by column by default. If you want to sort by row, you can also change the default value to sort by row.

I, How to quickly sort in excel

(I) How to sort data in excel(Excel sort by column)

If you want to sort the "Advanced Mathematics" of a class transcript in "ascending order". Click any cell of the second column, select "Data" tab, click "Sort A to Z" icon (A → Z), the "Advanced Mathematics" are sorted in ascending order; the operation steps are as shown in Figure 1:

Figure 1

(II) How to sort by date in excel(Excel sort by column)

If you want to sort by "Date" in "descending order". On the "Data" tab, Click any cell of the second column, click "Sort Z to a" icon (Z → A), the "Date" are sorted in descending order; the operation steps are as shown in Figure 2:

Figure 2

II, How to choose the basis of Excel sorting

(I) Continue with the current selection(Excel sort by column)

1. If you want to sort by "Advanced Mathematics" in "ascending order". Click the second column, column B, select "Data" tab, click "Sort A to Z" icon(A → Z), open the "Sort Warning" window, select "Continue with the current selection", Click "Sort", the high scores are sorted in ascending order; the operation steps are as shown in Figure 3:

Figure 3

2. As you can see from the demo, only the "Advanced Mathematics" are sorted in ascending order, and the other columns are not adjusted to sort, indicating that "Continue with the current selection" only sorts the data of the selected column.

(II) Extend the Selection(Excel sort by column)

1. If you want to sort the sales in the fruit sales table in ascending order. Select D2:D7, select the "Data" tab, click the "Sort A to Z" icon(A → Z), open the "Sort Warning" window, keep the default "Extend the Selection", click "Sort", then the table is sorted in ascending order; operation process steps, as shown in Figure 4:

Figure 4

2. sort by "extend selection", not only the "sales" column sorted in ascending order, the other columns are also sorted, that is, a column of sorting drives a row of sort. For example, the sales of "Longan" is 2685, before sorting, it ranks first, after sorting in ascending order, it ranks sixth, and "longan, berry and 3" are also ranked sixth.

The difference between "Continue with the current selection" and "Extend the Selection": the former sorts only the selected columns, and the latter sorts all the columns (that is, sorts one row).

(III) Excel sort by color

1. Right click method. If you want to put the "orange" cell to the front; right-click the "orange" cell, select "Sort" in the pop-up menu, and select "Put Selected Cell Color On Top" in the expanded options, the "orange" cell is ranked first, but the order of other cells has not changed; then the right-click "green" cell, the same way to put it to the front, two green cells to the front; Operation process steps, as shown in Figure 5:

Figure 5

2. Choose the color method. If you want to sort the "red" cell to the front or the end; select C2:C7, select the "Data" tab, click "Sort", and click "Sort" in the "Sort Warning" window that opens. Open the "Sort" window, select "Price" for the "Sort by", select "Cell Color" for the "Sort On", click the black small inverted triangle under "Order", select "Red" in the pop-up color, click "OK" , the "red" cell is ranked first; open the "sort" window again, expand the "order" drop-down list box, "red" has been ranked automatically to the first on the right(it was originally on the left second), indicating When "On Top" is selected, the color that was ranked will be transferred to the first on the right. Click the drop-down list box that includes "On Top", select "On Bottom", click "OK", "Red" The cells are sorted to the end; the operation steps are as shown in Figure 6:

Figure 6

(IV) Excel sort by font color

1. Right click method. If you want to put the "red" "Banana" in the first place; right-click on the cell where the "Banana" is located, in the pop-up menu, select "sort" → "Put Selected Font Color On Top", the red "Banana" is ranked first, while the order of characters in other cells does not change; the operation steps are as shown in Figure 7:

Figure 7

2. Select the font color method. If you want to put the "purple" "Orange" in the first place; select any cell in column A, such as select A4, select the "Data" tab, click "Sort" to open the "Sort" window, "Sort by" Select "Name", "Sort On" Select "Font Color", click the black small inverted triangle under "Order", then select "Purple", click "OK", then "Purple" "Orange" row In the first place; the operation process steps, as shown in Figure 8:

Figure 8

Tip: Do not select cells, just select one of the cells, then sort by "Extend the Selection" by default. In addition, if you want to sort the selected color to the end, on the right side of the Sort window, select "On Top" as "On Bottom", which has been demonstrated by sorting by cell color.

(V) Sort by cell icon

In one table, the sales are divided into three grades, the silver star is one grade (below 2000), the half gold star is the second grade (2000 - 3000), and the golden star is the third grade (3000 or more). Now it is required to put the golden star in the front.

1. Right click selection method. Right-click one of the cells in which the golden star is located (such as D4), and in the pop-up menu, select "Sort" → "Put Selected Cell Icon On Top", then all sales rows marked with gold stars To the front, the order of sales that were marked with other stars has not changed; the operational steps, as shown in Figure 9:

Figure 9

2. Select the icon method. Select any cell in column D, for example, select D3, select the "Data" tab, click "Sort", open the "Sort" window, select "Sales" for the "Sort by", select "Cell Icon" for the "Sort On", click the black small inverted triangle under the "Order", then select " half golden Star", click "OK", then all sales that were marked with a half gold star are also ranked first, and the order of sales that were marked with other stars is not Change; operation process steps, as shown in Figure 10:

Figure 10

III, Excel sort use shortcut keys

1. If you want to use the shortcut keys to sort the fruit sales in descending order. Click the fourth column column D, select the fourth column, hold down the Alt key, press H, S, and O one time, open the "Sort Warning" window, press Enter, the sales will be sorted in descending order; the operation steps, such as Figure 11 shows:

Figure 11

2. The ascending shortcut keys are: Alt + H + S + S; the custom sorting shortcut keys are: Alt + H + S + U; the button method is the same as the descending sorting shortcut key of the demo.

3. Right-click menu sort shortcuts

Ascending order: Shift + F10 + O + S;

Descending order: Shift + F10 + O + O;

Put selected cell color on top: Shift + F10 + O + C;

Put selected font color on top: Shift + F10 + O + F;

Put selected cell icon on top: Shift + F10 + O + I;

Press method: press the Shift key and press the following keys one by one.

IV, Excel custom sort

(I) How to sort multiple columns in excel

1. There is a clothing sales table that requires "City" to be sorted in ascending order and "sales" in descending order. Select one of the cells in the table, hold down the Alt key, press H, S, and U keys one time to open the "Sort" window, select "City" for "Sort by", and select "Value" for "Sort On", and select "A to Z" for "Order"; click "Add level"to add a condition, "Sort by" select "Sales", "Sort On" to keep the default "Value", "Order" select "Largest to smallest", click "OK" , the table is first sorted by "City" in ascending order and then by "sales" in descending order. For example, "Detroit" is ranked first, and sales in "Detroit" are sorted in "descending order"; the operation steps are as shown in Figure 12:

Figure 12

2. If you want to meet three or more conditions, continue to click "Add level" and then set the same method.

(II) Copying, sorting and deleting of sorting conditions

Also press the shortcut keys Alt + H + S + U to open the "sort" window (the press key method is the same as above), select the "Then by", click "copy level", then copy it and arrange automatically it at the bottom and selected, click on the small black triangle that is pointing up, then move the third condition up one bit, then click on the black small triangle, it will move down one bit, two black triangles are used to sort the conditions ; Click "Delete level", then the third condition is deleted; the operation process steps, as shown in Figure 13:

Figure 13

(III) Excel sort by column or by row

1. By default, Excel sort is sorted by column. For example, the sort shown above is sorted by column; if sort by row is required, you need to set the sort direction to "Sort Left To Right", the operation steps, such as Figure 14 shows:

Figure 14

2. Operation instructions: Select cell B2, hold down the Alt key, press H, S and U one time, open "Sort" window, click "Options" to open the "Sort Options" window, "Orientation" select "Sort Left To Right", click "OK" to return to the "Sort" window; "Sort by" select "Row 2" ( Since the first line is the table header), the other keeps the default options, click "OK", then the second row is sorted in ascending order.

3. In the Sort Options window, you can also set whether the case is case sensitive and sorted alphabetically or by stroke. If the letters are case sensitive, check "Case Sensitive"; Excel defaults to alphabetical order. If you want to sort by stroke, select "Stroke Ordering".

Tip: If you want to rank, you need to use the Rank function. For details, please refer to "the 11 examples of how to use the Excel Rank function, including the difference between Rank.EQ and Rank.AVG, and the automatic sorting and ranking and segmentation" One article.