Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Register  \/ 
x

or

Excel sort: sort data by text, date, number or color

It is a common and simple task to sort data in Excel, which can help reorder your data based on the type of sorting that you choose. Normally, with the built-in Sort feature, you can sort numbers, text strings, dates and times in one or more columns; You can also sort data by a custom list you created, or by cell formatting (such as font color, background color or icon) quickly and easily.

In addition to these simple sorting, there's much more useful and complex sorting in your daily work you may need. This tutorial will introduce some different kinds of sorting for solving your problem in Excel.

Table of contents:

1. Accessing the sorting options in Excel

2. Basic usages of sorting

3. Advanced usages of sorting

4. Sort data automatically when entering or changing data

5. Other cases of sorting


Accessing the sorting options in Excel

To apply the Sort feature, Excel provides several ways for you to access the sorting options.

1.1 Sorting buttons in the ribbon

The fastest way to apply the sort feature is using the sorting buttons in the ribbon.

Click any cell in a column with values to be sorted, and then click Data tab, in the Sort & Filter section, click Sort A to Z or Sort Z to A button to sort the data in ascending or descending alphabetical order. See screenshot:

Note: If there are blank rows in the data range, after applying these buttons, only the data range of the selected cell which above or below the blank rows can be sorted successfully.


1.2 Sorting dialog box

In the Sort & Filter group of the Data tab, there is another Sort button, see screenshot:

After clicking this Sort button, a Sort dialog box will pop out as below screenshot shown:

Then, in the Sort dialog box, you can choose corresponding rules for sorting data as you need.

Tips: You can also use shortcut keys to open this Sort dialog box, please press Alt + A + S + S keys consecutively in the keyboard.


1.3 Sorting options in the Filter menu

If you have applied filters to your data range, you can also find sorting options along with the filter options. Clicking on the filter icon in any column, you can see the sorting options in the expanded list as below screenshot shown:

Note: These sorting options will change based on the data in the column: If there is text in the column, it will show Sort A to Z, Sort Z to A; If there are numbers in the column, it will show Sort Smallest to Largest, Sort Largest to Smallest; If there are dates in the column, it will show Sort Oldest to Newest, Sort Newest to Oldest.


1.4 Right-click sorting options

In Excel, you can also use right-click sorting options to sort data quickly and easily, right click any cell in a column with values to be sorted, and in the context menu, you can see there are six sorting options available, please select the one you need, see screenshot:


Basic usages of sorting

This Excel Sort feature can help you to do some simple sorting, such as to sort numbers, text strings, dates in ascending or descending order, sort cells based on font or background color. This section will talk about some basic usages of this sort feature.


2.1 Sort data by texts, numbers or dates

To sort a range of data based on texts, numbers or dates in ascending or descending order, please do with the following steps:

1. Select the data range that you want to sort, and then click Data > Sort, see screenshot:

2. In the Sort dialog box, do the following operations:

  • In the Column section, select the column name that you want to sort based on;
  • In the Sort On section, select Cell Values option;
  • In the Order section, specify the sort order. (To sort text strings, please select A to Z or Z to A; To sort number list, select Smallest to Largest or Largest to Smallest; To sort date cells, select Oldest to Newest or Newest to Oldest.)
  • Tips: If there are headers in your data, make sure My data has headers is checked. In case your data doesn’t have headers, please uncheck it.

3. Then, click OK button, the selected data will be sorted based on the column you specified at once.


2.2 Sort data by cell color, font color, cell icon

If you want to sort the data range based on the cell color, font color or conditional formatting icon, the Sort feature can solve this task quickly.

Supposing, you have a data range which is formatted with some cell colors as below screenshot shown, if you need to rearrange the data based on the cell color, for example, you would like to put the light red rows on top, followed by light yellow and light blue rows, to sort the rows by cell color, please do as this:

1. Select the data range you want to sort based on the cell color, and then click Data > Sort to go to the Sort dialog box.

2. In the Sort dialog box, specify the following operations:

2.1) Set the first cell color on top like this:

  • In the Column section, select Name or other columns where you have the colored cells. In this example, I have colored cells in all columns, you can choose any column name;
  • Under the Sort On section, please select Cell Color option;
  • In the Order section, choose one cell color you want to put on top or bottom;

2.2) Then, click the Add Level button to add the second and other rule levels, repeat above steps for setting the second and other cell colors.

  • Tips: If there are headers in your data, make sure My data has headers is checked. In case your data doesn’t have headers, please uncheck it.

3. After finishing the settings, click OK button, and the data range has been sorted by the cell color you specified, see screenshot:

Tips: According to the above steps, you can also sort the data based on font color or cell icon by choosing the Font Color or Conditional Formatting Icon in the Sort dialog box.


2.3 Sort data by multiple columns

If you have a large dataset as below screenshot shown, now, you want to perform a multi-level data sorting for reading the data more clearly, for instance, sorting by the Region column first, and then State column, at last Sales column. How could you do this sorting in Excel?

To sort data by several columns, please do with the below steps:

1. Select the range of data you want to sort, and then click Data > Sort to go to the Sort dialog box.

2. In the Sort dialog box, click Add Level button twice as there are three columns you want to use for sorting. Then you can see two Then by rule levels have been added into the list box:

Tips: If there are headers in your data, make sure My data has headers is checked. In case your data doesn’t have headers, please uncheck it.

3. From the Sort by and Then by drop-down list, select the column names that you want to sort based on, and then select Cell Values from the Sort On section separately for each selected column, finally, choose the sort order as you need.

4. Then, click OK, and you will get the sorting result as below screenshot shown:


2.4 Sort data based on a custom list

Instead of sorting data in alphabetical or numerical order, this Sort feature also provides a custom list sorting for you. For example, you would like to sort the below data range by Status - Not Started, In Progress, Completed, you defined. Here, I will introduce how to deal with this type of sorting.

1. Select the data range you want to sort, and then, click Data > Sort to go to the Sort dialog box.

2. In the Sort dialog box, select the column you want to sort from the Column section, and then choose Cell Values from the Sort On section, in the Order section, click Custom List option, see screenshot:

Tips: If there are headers in your data, make sure My data has headers is checked. In case your data doesn’t have headers, please uncheck it.

3. And a Custom Lists box will pop up, please do the following operations:

  • Click NEW LIST in the Custom lists box;
  • Then, enter the text strings in the order you need to sort based on into the List entries box; (When entering the texts, please press Enter key to separate the entries.)
  • At last, click Add button, the new list is added into the Custom lists box at once.

4. Then, click OK button to return to the Sort dialog box. The new custom list of items is now displayed in the Order drop-down list.

5. And then, click OK button to close the dialog box, as a result, the data range has been sorted by the custom list you defined, see screenshot:


2.5 Sort data from left to right (horizontally sort)

Normally, you always sort the data table vertically from top to bottom, but, sometimes, you may want to sort the data based on the row values (sort from left to right). For example, in the below data range, I want to sort it based on the values in the Name row.

In this case, the Sort feature has an in-built functionality that allows you to sort from left to right. Please do with the below steps:

1. Select your data range (exclude headers) that you want to sort, and then click Data > Sort to go to the Sort dialog box.

2. In the popped-out Sort dialog box, click Options button, then, in the Sort Options dialog box, select Sort left to right option, see screenshot:

3. Click OK to go back to the Sort dialog, now, in the Row section, specify row number you want to sort data based on, and then select Cell Values in the Sort On section, finally, choose the sort order in the Order section you need, see screenshot:

4. Then, click OK, and your data is sorted as below screenshot shown:


2.6 Sort data in case-sensitive order

Generally, data sorting is not performed in case-sensitive order as below first screenshot shown. But what if you want to make the sorting case sensitive as below second screenshot shown, how could you solve this task in Excel?

To perform a case sensitive sorting in Excel, please do as this:

1. Select the data range you want to sort, and then click Data > Sort to go to the Sort dialog box.

2. In the Sort dialog box, in the Column section, specify the column name you want to sort data based on, and then select Cell Values in the Sort On section, finally, choose the sort order in the Order section you need, see screenshot:

Tips: If there are headers in your data, make sure My data has headers is checked. In case your data doesn’t have headers, please uncheck it.

3. Go on clicking the Options button in this dialog box, and in the Sort Options prompt box, check Case sensitive option, see screenshot:

4. Click OK > OK to close the dialogs, the data will be sorted in case-sensitive order with lower cases first then the upper cases when the text is the same. See screenshot:


Advanced usages of sorting

In your daily work, you may come across other more complex, practical, specific sorting problems, this section will talk about multiple kinds of sorting tasks in excel.

3.1 Sort data based on frequency

Assuming that you have a list of data in a column, and now, you want to sort this column in descending order of the data’s occurrence frequencies as below screenshot shown. Here, I will introduce two methods to solve this task.

 Sort data based on frequency with a helper column

In Excel, there is no direct way for sorting data based on occurrence count, here, you can create a helper formula to get the occurrence frequency of each text, and then sort by the helper column to get the sorting result you need.

1. Type the below formula into a blank cell adjacent to the original data, B2, for instance, and then drag the fill handle down to the cells for applying this formula, see screenshot:

=COUNTIF($A$2:$A$16,A2)

Note: In the above formula, A2:A16 is the list with data that you want to sort by frequency, and A2 is the first data of this list.

2. Then, keep selecting the formula cells, and then, click Data > Sort A to Z or Sort Z to A as you need, in the pop-up Sort Warning prompt box, select Expand the selection, see screenshots:

3. And then, click the Sort button, now the original column has been sorted by the frequency as following screenshots shown:

Tips:

1. After getting the result, you can delete the helper column as you need.

2. If there are text strings that appear the same number of times, the same text may not be sorted together. In this case, you should go to the Sort dialog box, sort by the helper column first, and then sort by the text as below screenshot shown:


 Sort data based on frequency with a handy feature

If you have Kutools for Excel, with its Advanced Sort feature, you can sort the data based on the occurrence frequency without any helper column quickly and easily.

After installing Kutools for Excel, please do as this:

1. Select the data list you want to sort, and then, click Kutools Plus > Sort > Advanced Sort, see screenshot:

2. In the Advanced Sort dialog box, select the column you want to sort from the Column section, and then choose Frequency in the Sort On drop-down list, finally, specify the sort order in the Order section, see screenshot:

Tips: If there are headers in your data, make sure My data has headers is checked. In case your data doesn’t have headers, please uncheck it.

3. Then, click OK button, the data list has been sorted by occurrence count at once, see screenshot:


3.2 Sort data based on character length

If you have a list of data which contain text strings with different length, now, you may need to sort the data by character length to make the column look neat and tidy. This section will talk about how to sort data by the number of characters.

 Sort data based on character length with a helper column

To sort column by length of characters, you should use the LEN function to calculate the number of characters for each cell, and then apply the Sort function to sort the data list, please do with the below steps:

1. Enter this formula =LEN(A2) into a blank cell adjacent to the original data, B2, for instance, and then drag the fill handle down to the cells for applying this formula, see screenshot:

2. Then, keep selecting the formula cells, and then, click Data > Sort A to Z or Sort Z to A as you need, in the pop-up Sort Warning prompt box, select Expand the selection, see screenshot:

3. Then, click Sort button, the strings have been sorted by the character length. You can delete the helper Column B as you need. See screenshot:


 Sort data based on character length with an easy option

To sort data by the number of characters quickly and easily, here, I will recommend a handy tool – Kutools for Excel, with its Advanced Sort feature, you can deal with this task with ease.

After installing Kutools for Excel, please do as this:

1. Select the data list you want to sort, and then click Kutools Plus > Sort > Advanced Sort.

2. In the Advanced Sort dialog box, select the column you want to sort in the Column section, and choose Text length from the Sort On drop-down list, finally, specify the sort order you need in the Order section. See screenshot:

Tips: If there are headers in your data, make sure My data has headers is checked. In case your data doesn’t have headers, please uncheck it.

3. Then, click OK button, the text strings in the list have been sorted by character length as below screenshot shown:


3.3 Sort full names by last names

When sorting a list of full names, Excel will use the first character of the name to sort, but what if you want to sort data by the last names? Here, I will bring some tricks for you to sort full names by last names in Excel easily.

 Sort full names by last names with a helper column

Sorting full names based on the last names, you should extract the last names into a new column, and then apply the Sort feature to sort the full names based on the separated last names alphabetically. Please do with the following steps:

1. Enter or copy the below formula into a blank cell adjacent to the original data, B2, for instance, and then drag the fill handle down to the cells for applying this formula, see screenshot:

=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

2. Go on selecting the formula cells, and then, click Data > Sort A to Z or Sort Z to A as you need, in the pop-up Sort Warning prompt box, select Expand the selection, see screenshot:

3. Then, click Sort button, and you will get the full names that are sorted based on the last names, once done, you can delete the helper column as you need. See screenshot:


 Sort full names by last names with a quick method

If you are not familiar with the formula, to sort the full names by last names without any formula, the Advanced Sort feature of Kutools for Excel can do you a favor.

After installing Kutools for Excel, please do as this:

1. Select the full name cells that you want to sort, and click Kutools Plus > Sort > Advanced Sort.

2. In the Advanced Sort dialog box, select the column you want to sort in the Column section, and choose Last name from the Sort On drop-down list, finally, specify the sort order as you need in the Order section. See screenshot:

Tips: If there are headers in your data, make sure My data has headers is checked. In case your data doesn’t have headers, please uncheck it.

3. Then, click OK button, the full name column has been sorted by last names at once.


3.4 Sort email addresses by domain

If you have a list of email addresses to be sorted by domain rather than just by the first letter of the address, how could you do in Excel? This section will talk about some quick methods for sorting email addresses by domains.

 Sort email addresses by domain with a helper column

Here, you can create a formula to extract the email domains into another column, and then sort the original email addresses based on the new domain column.

1. Type the following formula into a blank cell beside your email address, in this example, I will enter this formula into cell C2, then drag the fill handle to the range that you want to apply this formula. All the email domains will then be extracted from the addresses, see screenshot:

=RIGHT(B2,LEN(B2)-FIND("@",B2))

2. Keep the formula cells selected, and then click Data > Sort A to Z or Sort Z to A as you need, in the Sort Warning dialog box, check Expand the selection, see screenshot:

3. And then, click Sort button to sort the data by email domains in ascending order or descending order as you specified.

4. After sorting, you can delete the helper column as you need.


 Sort email addresses by domain with several clicks

If you have Kutools for Excel, with its Advanced Sort function, the email addresses can be sorted by the domain names alphabetically with just several clicks.

After installing Kutools for Excel, please do as this:

1. Select the whole data range that you want to sort, and then click Kutools Plus > Sort > Advanced Sort.

2. In the Advanced Sort dialog box, select the column you want to sort in the Column section, and choose Mail domain from the Sort On drop-down list, finally, specify the sort order you need in the Order section. See screenshot:

Tips: If there are headers in your data, make sure My data has headers is checked. In case your data doesn’t have headers, please uncheck it.

3. Click OK, and the data range has been sorted by email domains as below screenshot shown:


3.5 Sort one column to match another

If you have two columns with data that are exactly the same or almost the same, but in different orders, now, you may need to sort the two columns so that the same values are aligned on the same rows in the two columns. This section, I will introduce two cases for this type of sorting.

 Sort two columns with exactly the same items to match

For example, I have two columns that contain the same items but in different orders, here, I want to sort the second column to match the first column as below screenshot shown.

1. Type the below formula into a blank cell beside your original data, then, drag the fill handle for applying this formula to all the cells in the list, and this will return the position of each value in column B against in column A, see screenshot:

=MATCH(B2,$A$2:$A$10,0)

2. Then, select the column B and the new helper column, and click Data > Sort to go to the Sort dialog box, in the Sort dialog box, do the following operations:

  • Select the helper column that you want to sort data based on from the Column section;
  • Then, choose Cell Values in the Sort On section;
  • At last, select Smallest to Largest option in the Order section.

3. And then, click OK button. Now, you will get the two columns matched exactly like below screenshot shown. You can delete the formula column as you need.


 Sort two columns with not exactly the same items to match

Sometimes, the items in two columns may not be exactly the same. For instance, I want to sort the data in the second column to match those in the first column, so that make the same values align on the same rows as below screenshot shown.

1. Insert a new blank column between the two columns.

2. Enter the below formula into cell B2, and then, drag the fill handle to fill this formula to cells you need. And now, you can see the data in column C has been sorted to match the data in column A.

=IF(ISNA(MATCH(A2,$C$2:$C$8,0)),"",INDEX($C$2:$C$8,MATCH(A2,$C$2:$C$8,0)))


3.6 Sort and extract unique values from a list of data

If you have a list of values which contain some duplicates, now, you just want to extract the unique values and sort them alphabetically as below screenshot shown, here, I will introduce some formulas for dealing with this type of sorting.

1. Please enter the below formula into a blank cell – C2, and then press Ctrl + Shift + Enter keys to get the first result, see screenshot:

=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($A$2:$A$12,"<"&$A$2:$A$12)-SUM(COUNTIF($A$2:$A$12,"="&C$1:C1)),0)),"")

Note: In the formula, A2:A12 is the data list that you want to extract unique values from, C1 is the cell above the formula you put. Please change them to your need.

2. Then drag the fill handle to extract the values until blank cells appear, all unique values will be extracted and sorted in an ascending order, see screenshot:

Tips:

1. If you want the extracted unique values to be sorted in a descending order, please apply the below formula: (Remember to press Ctrl + Shift + Enter keys)

=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($A$2:$A$12,">"&$A$2:$A$12)-SUM(COUNTIF($A$2:$A$12,"="&C$1:C1)),0)),"")

2. If the data list contains space cell, numeric values, the above formula will not work, in this case, you should use the following formulas: (Remember to press Ctrl + Shift + Enter keys)

=IFERROR(SMALL(IF((COUNTIF($C$1:C1,$A$2:$A$12)=0)*ISNUMBER($A$2:$A$12),$A$2:$A$12,"A"),1),INDEX($A$2:$A$12,MATCH(SMALL(IF(ISTEXT($A$2:$A$12)*(COUNTIF(C1:$C$1,$A$2:$A$12)=0),COUNTIF($A$2:$A$12,"<"&$A$2:$A$12),""),1),IF(ISTEXT($A$2:$A$12),COUNTIF($A$2:$A$12,"<"&$A$2:$A$12),""),0)))


3.7 Sort multiple rows or columns independently at once

It is easy for us to sort a range of data based on one row or column, but sometimes you may want to alphabetize each row or column in the range independently, how could you achieve this task in Excel?

 Sort multiple rows individually

Suppose you have a range of data that should be re-arranged alphabetically in each row like the below screenshot shown, to solve this problem, here are two quick tricks for you.

Sort multiple rows independently at once with formula

1. Copy the row labels to another location where you want to get the sorted result.

2. And then, copy or enter the below formula into a blank cell – H2, and then press Ctrl + Shift + Enter keys together to get the first result, see screenshot:

=INDEX($B2:$E2, MATCH(COLUMNS($B2:B2), COUNTIF($B2:$E2, "<="&$B2:$E2), 0))

3. Then, select the formula cell H2, and drag the fill handle rightwards to copy the formula to other cells of the first row, in this example, drag to cell K2, see screenshot:

4. Go on selecting the formula cells in the first row (H2:K2), and drag the fill handle downwards to copy the formula to other rows. Now, you can see the values in each row have been sorted individually in an ascending order.


Sort multiple rows independently at once with VBA code

The following VBA code also can help you to sort the data in each row alphabetically with ease. Please do with the following steps:

1. Select the data that you want to sort in each row.

2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

3. Then, click Insert > Module, and paste the following code in the Module Window.

VBA code: Sort multiple rows independently at once

Sub SortIndividualR()
'Updateby Extendoffice
    Dim xRg As Range, yRg As Range
    If TypeName(Selection) <> "Range" Then Exit Sub
    Set xRg = Selection
    If xRg.Count = 1 Then
        MsgBox "Select multiple cells!", vbExclamation, "Kutools for Excel"
        Exit Sub
    End If
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    Application.ScreenUpdating = False
    For Each yRg In xRg.Rows
        yRg.Sort Key1:=yRg.Cells(1, 1), _
        Order1:=xlAscending, _
        Header:=xlNo, _
        Orientation:=xlSortRows
    Next yRg
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    Application.ScreenUpdating = True
End Sub

4. Then, press F5 key to run this code, the data in each row has been sorted in an ascending order immediately, see screenshot:


 Sort multiple columns individually

To sort the data in each column individually, the following two methods can do you a favor.

Sort multiple columns independently at once with formula

1. Copy the column labels to another location where you want to get the sorted result.

2. And then, enter the below formula into a blank cell – F3, and press Ctrl + Shift + Enter keys together to get the first result, then, drag the fill handle downwards to copy this formula to other rows, see screenshot:

=INDEX(A$3:A$6,MATCH(ROWS(A$3:A3),COUNTIF(A$3:A$6,"<="&A$3:A$6),0))

3. Go on selecting the formula cells in the first row (F3:F6), and drag the fill handle rightwards to copy the formula to other columns. Now, the values in each column have been sorted individually in an ascending order as below screenshot shown:


Sort multiple columns independently at once with VBA code

To sort the data in multiple columns independently, the following VBA code also can do you a favor, please do as this:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Then, click Insert > Module, and paste the following code in the Module Window.

VBA code: Sort multiple columns independently at once

Sub SortIndividualJR()
'Updateby Extendoffice
    Dim xRg As Range
    Dim yRg As Range
    Dim ws As Worksheet
    Set ws = ActiveSheet
    On Error Resume Next
    Set xRg = Application.InputBox(Prompt:="Range Selection:", _
                                    Title:="Kutools for excel", Type:=8)
    Application.ScreenUpdating = False
    For Each yRg In xRg
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=yRg, Order:=xlAscending
            .SetRange ws.Range(yRg, yRg.End(xlDown))
            .Header = xlNo
            .MatchCase = False
            .Apply
        End With
    Next yRg
    Application.ScreenUpdating = True
End Sub

3. Then, press F5 key to run this code, and a prompt box will pop out, please select the data range that you want to sort, see screenshot:

4. And then, click OK, each column has been sorted individually quickly.


3.8 Sort data randomly in Excel

It is common for us to sort the data alphabetically in an ascending or descending order, but have you ever tried to sort data in a selected range randomly? This section, I will talk about how to shuffle a range of cells in a random order.

 Sort a column of data randomly with a helper column

Normally, you can use a RAND function to get random numbers, and then sort the data based on this random list, please do as this:

1. Enter this formula: =RAND() into a blank cell beside your data, and drag the fill handle down to fill a list of random numbers as below screenshot shown:

2. Keep selecting the formula cells, and then, click Data > Sort A to Z or Sort Z to A as you need, in the pop-up Sort Warning prompt box, select Expand the selection, see screenshot:

3. And then, click Sort button, the data list has been shuffled at once, see screenshot:


 Sort cells, rows or columns randomly with an amazing feature

Kutools for Excel supports a powerful feature – Sort Range Randomly, with this function, you can randomize data in a range of cells, in each column / row of a selection, or randomize entire rows or columns at once.

After installing Kutools for Excel, please do as this:

1. Select the data range you want to sort randomly, and then, click Kutools Plus > Sort > Sort Range Randomly. See screenshot:

2. In the Sort/Select Range Randomly dialog box, under the Sort tab, choose one of the following options you need:

  • Entire rows: Shuffle the entire rows in the selected range randomly.
  • Entire columns: Shuffle the entire columns in the selected range randomly.
  • Cells in each row: Shuffle cells in each row individually.
  • Cells in each column: Shuffle cells in each column individually.
  • All cells in the range: Randomize all cells in the selected range.

3. Then, click Ok button, the data is randomized immediately.


3.9 Sort a list of dates based on month , day, or month and day

When sorting dates in Excel, the Sort feature will sort the list of dates by year, month, and day by default, but, in certain case, you may need to arrange the dates by month or day, or month and day while ignoring the year. How to solve this problem in Excel?

 Sort a list of dates by month or day with a helper column

To sort the dates bases on only month or day, you can extract the month or day numbers from the dates, and then sort the dates by the extracted month or day numbers column.

1. Please enter the below formula into a blank cell beside your data, and then drag the fill handle down to copy this formula to other cells, see screenshot:

=MONTH(B2)        (extract month number)
=DAY(B2)             
(extract day number)

2. After returning the month or day numbers, keep selecting the formula cells, and then click Data > Sort A to Z or Sort Z to A as you need, in the pop-up Sort Warning prompt box, select Expand the selection, see screenshot:

3. Then, click Sort button, the dates are now sorted by month, ignoring the years and days. See screenshot:


 Sort a list of dates by month or day with several clicks

If you have Kutools for Excel, with its Advanced Sort feature, you can sort a list of dates by only month or day with several clicks.

After installing Kutools for Excel, please do with the following steps:

1. Select the data range that you want to sort, and then click Kutools Plus > Sort > Advanced Sort.

2. In the Advanced Sort dialog box, select the column you want to sort in the Column section, and choose Month or Day from the Sort On drop-down list, finally, specify the sort order you need in the Order section.

Tips: If there are headers in your data, make sure My data has headers is checked. In case your data doesn’t have headers, please uncheck it.

3. Then, click OK button, the data has been sorted based on the month or day as you specified while ignoring the year, see screenshot:


 Sort a list of dates by month and day with a helper column

Now, if you need to sort a list of dates by month and day only without year, the TEXT function can help to convert a date to a text string in the specified format, and then apply the Sort feature as you need.

1. Enter the below formula into a blank cell beside your data, and then copy this formula down to the bottom of your data, the month and day has been extracted as text string as below screenshot shown:

=TEXT(B2,"MMDD")

2. Keep the formula cells selecting, and then click Data > Sort A to Z or Sort Z to A as you need, in the pop-up Sort Warning prompt box, select Expand the selection, see screenshot:

3. Then, click Sort button, and now, your data is sorted by month and day only.


3.10 Sort a list of dates based on days of week

To sort a list of dates based on days of week, which means sorting dates from Monday to Sunday or Sunday to Saturday. In this section, I will introduce two methods for finishing this task in Excel.

 Sort a list of dates by days of week with a helper column

Sorting dates by days of week, you will also need a helper column to return a number corresponding to the days of the week, and then sorting the dates by the helper column.

1. Please enter or copy the below formula into a blank cell, and then drag the fill handle down to copy this formula to other cells, see screenshot:

=WEEKDAY(B2)           (Start from Sunday (1) to Saturday(7))
=WEEKDAY(B2,2)       
(Start from Monday (1) to Sunday (7))

2. Keep the formula cells selecting, and then, click Data > Sort A to Z or Sort Z to A as you need, in the pop-up Sort Warning prompt box, select Expand the selection, see screenshot:

3. And then, click the Sort button to get the sorting result you need, see screenshot:


 Sort a list of dates by days of week with an easy option

With the help of Kutools for Excel’s Advanced Sort feature, you can solve this problem as quickly as possible without any helper formula.

After installing Kutools for Excel, please do as this:

1. Select the data range you will sort, and then, click Kutools Plus > Sort > Advanced Sort.

2. In the Advanced Sort dialog box, select the column you want to sort in the Column section, and choose Day of the week from the Sort On drop-down list, finally, specify the sort order you need in the Order section.

Tips: If there are headers in your data, make sure My data has headers is checked. In case your data doesn’t have headers, please uncheck it.

3. Then, click OK button, data has been sorted by days of week as you need. See screenshot:


3.11 Sort a list of dates based on quarter

This section will talk about how to sort a list of dates by quarter ignoring the year, two tricks will be provided for you.

 Sort a list of dates by quarter with a helper column

Just like the above solutions, you should create a formula helper column to extract the quarter number from the given dates, and then sort the dates based on this new helper column.

1. Please enter or copy the below formula into a blank cell, and then drag the fill handle down to copy this formula to other cells, see screenshot:

=ROUNDUP(MONTH(B2)/3,0)

2. Keep selecting the formula cells, and then, click Data > Sort A to Z or Sort Z to A as you need, in the pop-up Sort Warning prompt box, select Expand the selection, see screenshot:

3. And then, click the Sort button, the data range has been sorted by the quarter as below screenshot shown:


 Sort a list of dates by quarter with a handy feature

If you have Kutools for Excel installed, the Advanced Sort feature can help you to solve this task with several clicks.

1. Select the data range you want to sort, and then, click Kutools Plus > Sort > Advanced Sort.

2. In the Advanced Sort dialog box, select the column you want to sort in the Column section, and choose Quarter from the Sort On drop-down list, finally, specify the sort order you need in the Order section.

Tips: If there are headers in your data, make sure My data has headers is checked. In case your data doesn’t have headers, please uncheck it.

3. Finally, click OK button, and the selected range is sorted by quarter as you need.


3.12 Sort data based on month names or weekday names

Supposing you have a list of month names as text, when sorting the month names, they will be arranged alphabetically instead of sorting by month order from January to December. If you need to sort the month names from Jan to Dec, a custom sort within the Sort feature may do you a favor.

1. Select the data range you want to sort based on month names, and then click Data > Sort to go to the Sort dialog box.

2. In the Sort dialog box, in the Column section, select the column name contains the month names, in the Sort on section, choose Cell Values, finally, in the Order section, select Custom List, see screenshot:

Tips: If there are headers in your data, make sure My data has headers is checked. In case your data doesn’t have headers, please uncheck it.

3. Then, in the popped out Custom List dialog box, select full month names (January, February, March, …) or short names (Jan, Feb, Mar…) based on how the months are listed in your worksheet, see screenshot:

4. And then, click OK > OK to close the dialogs, and now, your data has been sorted by month name in chronological order as below screenshot shown:

Tips: To sort by the weekday names, select the full names (Sunday, Monday, Tuesday, …) or short names (Sun, Mon, Tue…) in the Custom Lists dialog box as you need.


3.13 Sort data based on odd or even numbers

It may be easy for us to sort numbers in an ascending or descending order in Excel, but, have you ever tried to sort the numbers from odd to even or even to odd in a list? This section, I will introduce some methods for doing this task.

 Sort data by odd or even numbers with a helper column

Sorting the list of numbers by odd or even numbers, you should create a formula to identify the odd or even numbers, and then apply the Sort function.

1. Next to the list of numbers, please enter this formula =ISODD(A2) in a blank cell, and then drag to copy the formula to other cells, now, you can see TRUEs and FALSEs are displayed, the TRUE indicates odd numbers and FALSE indicates even numbers.

2. Keep selecting the formula cells, and then click Data > Sort A to Z or Sort Z to A as you need, in the pop-up Sort Warning prompt box, select Expand the selection, see screenshot:

3. Then click Sort button, all even numbers have been sorted together and then followed by the odd numbers or vice versa. See screenshot:


 Sort data by odd or even numbers with a useful feature

With the help of Kutools for Excel’s Advanced Sort feature, you can quickly sort the numbers from odd numbers to even numbers or vice versa as well.

After installing Kutools for Excel, please do as this:

1. Select the data range you want to sort, and then, click Kutools Plus > Sort > Advanced Sort.

2. In the Advanced Sort dialog box, click the column that you want to sort under Column section, and then choose Odd and even number from the Sort On section, then, specify the sort order (A to Z sort from even to odd numbers, and Z to A sort from odd to even numbers), see screenshot:

Tips: If there are headers in your data, make sure My data has headers is checked. In case your data doesn’t have headers, please uncheck it.

3. And then click OK button, you will get the following results:


3.14 Sort data based on absolute values

If both positive and negative numbers exist in the column, when sorting, the numbers will be arranged in an ascending or descending order by default in Excel. But, in certain case, you may want to ignore the negative sign when sorting data, that means sort the numbers by absolute values. How to achieve this task in Excel?

 Sort data by absolute values with a helper column

To sort numbers by absolute values, normally, you should create a formula to convert all the values to absolute values first, and then apply the Sort function to sort the numbers.

1. In an adjacent blank cell, B2 for instance, enter this formula =ABS(A2), and then, drag the fill handle to copy this formula to other cells. Now, you can see that all the values have been converted to absolute values:

2. Keep the formula cells selecting, and then, click Data > Sort A to Z or Sort Z to A as you need, in the pop-up Sort Warning prompt box, select Expand the selection, see screenshot:

3. Then click Sort button, and all the numbers have been sorted by the absolute values as below screenshot shown:


 Sort data by absolute values with a handy option

If you are tired with creating helper column, here, I will recommend Kutools for Excel, with its Advanced Sort feature, you can sort the numbers by absolute values directly and simply.

After installing Kutools for Excel, please do with the following steps:

1. Select the data range you want to sort, and then, click Kutools Plus > Sort > Advanced Sort.

2. In the Advanced Sort dialog box, click the column that you want to sort under Column section, and then choose Absolute value from the Sort On section, then, specify the sort order, see screenshot:

Tips: If there are headers in your data, make sure My data has headers is checked. In case your data doesn’t have headers, please uncheck it.

3. Then, click OK, all the numbers will be sorted from smallest to largest or largest to smallest values ignoring the negative sign.


Sort data automatically when entering or changing data

The Sort feature in Excel is not dynamic, you will have to re-sort the data after every change or whenever new data are added. This section, I will discuss how to sort your data automatically every time a new value is added in your data range.


4.1 Auto sort numeric values in a column with formulas

To sort a list of numbers in ascending or descending order automatically, you can use formulas based on the LERGE, SMALL and ROW functions.

1. Please enter or copy the below formula into a blank cell beside your source data, and then, drag the fill handle down to the cells you want to apply this formula, and the numbers will then be arranged from smallest to largest values, see screenshot:

=IFERROR(SMALL($A$2:$A$100,ROWS(B$2:B2)),"")

Note: In this formula, A2:A100 is the number list that you want to sort automatically, including some empty cells for new entries, B2 is the cell where you enter the formula.

2. Now, when changing the source data or entering new data, the sorted list will be refreshed automatically as below demo shown:

Tips: To automatically sort the numbers in descending order, please apply the below formula:

=IFERROR(LARGE($A$2:$A$100,ROWS(B$2:B2)),"")


4.2 Auto sort text values in a column with formulas

If you want to sort the text values in a column automatically, the above formula will not work, here, another formula may help you, please do as this:

1. Enter or copy the below formula into a blank cell beside your data column, and then press Ctrl + Shift + Enter keys together to get the first text, and then select the formula cell, and drag the fill handle down to the cells that you want to sort, see screenshot:

=IFERROR(INDEX($A$2:$A$100,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$100,">="&$A$2:$A$100),0)),"")

2. From now on, when entering value or changing the original data in column A, the text strings in column B will be sorted in ascending order automatically, see below demo:

Tips: To automatically sort the text strings in descending order, please apply the below formula (remember to press Ctrl + Shift + Enter keys):

=IFERROR(INDEX($A$2:$A$100,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$100,">="&$A$2:$A$100),0)),"")


4.3 Auto sort the mix of numeric and text values in a column with VBA code

If there are both numeric and text values in a column, to sort the list of data automatically, the below VBA code may do you a favor.

1. Right click the sheet tab where you want to sort data automatically, and then, select View Code from the context menu, in the pop-out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module window, see screenshot:

VBA code: auto sort when data is entered or changed in ascending order:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    On Error Resume Next
    If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
                                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Note: In the above code, the entered data will be sorted automatically in column A. A1 is the header, and A2 is the first cell of the data list.

2. Then, save and close the code window. Now, when you enter new data or change original data in column A, the data will be sorted in ascending order automatically. See below demo:

Tips: If you want to sort the list of data in descending order, please apply the following code:

VBA code: auto sort when data is entered or changed in descending order:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    On Error Resume Next
    If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Range("A1").Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlYes, _
                                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Other cases of sorting

You may suffer from other various sorting demands in your daily work. In this section, I will introduce some other sorting types, such as to sort data within a cell, sort data in a column or row with a reverse order, and so on.


5.1 Sort data within a cell

To sort text string within a single cell in alphabetical order, for example, to sort the text “HDAW” as “ADHW”; Or to sort multiple words separated by comma in a cell, such as to sort “word, outlook, excel, access” as “access, excel, outlook, word”. This section will talk about how to solve this type of sorting in Excel.

 Sort string value within a cell in alphabetical order

Sorting string value within a cell in alphabetical order, you should create a User Defined Function. Please do as this:

1. Hold down ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Sort text value within cell

Function SortCellContents(xRange As Range)
'Updateby Extendoffice
Dim xArr
Dim xF1, xF2 As Integer
Dim xStrValue As String
Dim xStrT As String
If xRange.Count <> 1 Then
    Exit Function
End If
xStrValue = xRange.Value
ReDim xArr(1 To Len(xStrValue))
For xF1 = 1 To UBound(xArr)
    xArr(xF1) = Mid(xStrValue, xF1, 1)
Next
For xF1 = 1 To UBound(xArr)
    For xF2 = xF1 To UBound(xArr)
        If Asc(xArr(xF2)) < Asc(xArr(xF1)) Then
            xStrT = xArr(xF2)
            xArr(xF2) = xArr(xF1)
            xArr(xF1) = xStrT
        End If
    Next xF2
Next xF1
SortCellContents = Join(xArr, "")
End Function

3. Then save and close this code, go back to your worksheet. And then, enter this formula =SortCellContents(A2) into a blank cell where to return the result, and drag the fill handle down to copy the formula to other cells, and then all the text values in the cells will be sorted in alphabetical order, see screenshot:


 Sort text strings separated by a delimiter within a cell in alphabetical order

If there are multiple words that are separated by specific delimiters in a cell, to sort them in alphabetical order within the cell, the below User Defined Function can help you.

1. Hold down ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Sort text strings separated by comma within cell

Function SortCellWithSeparator(CellAddress As Range, DelimiterChar As String, IncludeSpaces As Boolean) As String
'Updateby Extendoffice
Dim xRg As Range
Dim xString As String
Dim xF1, xF2 As Integer
Dim xSArr
Dim xStrT As String
Dim xStrValue As String
Set xRg = CellAddress
xStrValue = WorksheetFunction.Substitute(xRg.Value, " ", "")
xSArr = Split(xStrValue, DelimiterChar)
    For xF1 = 0 To UBound(xSArr)
        For xF2 = xF1 + 1 To UBound(xSArr)
                If xSArr(xF2) < xSArr(xF1) Then
                    xStrT = xSArr(xF2)
                    xSArr(xF2) = xSArr(xF1)
                    xSArr(xF1) = xStrT
                End If
        Next xF2
    Next xF1
xStrValue = ""
For xF1 = 0 To UBound(xSArr)
    xStrValue = xStrValue & xSArr(xF1) & DelimiterChar
Next xF1
SortCellWithSeparator = xStrValue
SortCellWithSeparator = Left(SortCellWithSeparator, Len(SortCellWithSeparator) - 1)
If IncludeSpaces = True Then SortCellWithSeparator = WorksheetFunction.Substitute(SortCellWithSeparator, ",", ", ")
End Function

3. Then save and close this code, return to your worksheet, enter this formula =SortCellWithSeparator(A2,",",TRUE) into a blank cell where to return the result, and then, drag the fill handle down to copy the formula to other cells, all the text strings in the cells will be sorted in alphabetical order, see screenshot:

Note: If your text strings are separated by other delimiters, you just need to change the comma in this formula to your own separator.


5.2 Reverse / flip the order of the data in a column or row

Sometimes, you may need to reverse the order of data upside down in a vertical data range or left to right in a horizontal data range. This section will introduce three methods for you to solve this task in Excel.

 Reverse / flip the order of the data in a column or row with formulas

The following formulas can help to flip the order of the data in a column or row, please do as this:

Flip the order of the data in a column

Please enter or copy the following formula into a blank cell where you want to put the result, and then drag the fill handle down to the cells to return the cell values in reverse order, see screenshot:

=OFFSET($A$10,-(ROW(A1)-1),0)

Note: In the above formula, A1 is the first cell and A10 is the last cell in the column.


Flip the order of the data in a row

Apply the below formula to flip the order of data horizontally in a row:

=OFFSET($A$1,,COUNTA(1:1)-COLUMN(A1),)

Note: In the above formula, A1 is the first cell in the row, and 1:1 is the row number that your data located. If the data is in row 10, you should change it to 10:10.

Then, drag the fill handle right to the cells until all values are extracted, and you will get all data been reversed horizontally, see screenshot:


 Reverse / flip the order of the data in multiple columns or rows with VBA code

The above formulas only work well for a single column or row, if there are multiple columns or rows with data to be reversed, the following VBA codes can help you.

Flip the order of the data in a range of cells vertically

1. First, you should backup your original data, and then, hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Flip a range of cells in reverse order vertically

Sub Flipvertically()
'updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For j = 1 To UBound(Arr, 2)
    k = UBound(Arr, 1)
    For i = 1 To UBound(Arr, 1) / 2
        xTemp = Arr(i, j)
        Arr(i, j) = Arr(k, j)
        Arr(k, j) = xTemp
        k = k - 1
    Next
Next
WorkRng.Formula = Arr
End Sub

3. Then, press F5 key to run this code, and a prompt box will be popped out asking you to select the data range that you want to reverse vertically, see screenshot:

4. Then, click OK button, the data range will be reversed vertically as below screenshots shown:


Flip the order of the data in a range of cells horizontally

To reverse the data range in horizontal order, please apply the below VBA code:

VBA code: Flip a range of cells in reverse order vertically

Sub Fliphorizontally()
'updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For i = 1 To UBound(Arr, 1)
    k = UBound(Arr, 2)
    For j = 1 To UBound(Arr, 2) / 2
        xTemp = Arr(i, j)
        Arr(i, j) = Arr(i, k)
        Arr(i, k) = xTemp
        k = k - 1
    Next
Next
WorkRng.Formula = Arr
End Sub

After running this code, the data in rows have been reversed horizontally, see screenshots:


 Reverse / flip the order of the data in columns or rows with just only one click

If you have Kutools for Excel, with its Flip Vertical Range and Flip Horizontal Range features, you can reverse the range of cells vertically and horizontally with only one click.

After installing Kutools for Excel, please do as this:

Flip the order of the data in a range of cells vertically

1. Select the data range that you want to reverse, and then, click Kutools > Range > Flip Vertical Range > All / Only flip values, see screenshot:

Tips: If you choose All option, all the cell formatting will be reversed at the same time; If you choose Only flip values, only the cell values will be reversed.

2. And then, the data range will be reversed vertically at once.


Flip the order of the data in a range of cells horizontally

1. Select the data range, and then click Kutools > Range > Flip Horizontal Range > All / Only flip values, see screenshot:

Tips: If you choose All option, all the cell formatting will be reversed at the same time; If you choose Only flip values, only the cell values will be reversed.

2. Then, all cell values in the selection will be reversed horizontally immediately as below screenshots shown:


5.3 Sort worksheet tabs in alphabetical order or tab color in Excel

Supposing, there are a large number of worksheets in your workbook, if you need to sort the sheet tabs in alphabetical order, or sort the sheet tabs based on the tab color, the normal method - drag and drop sheet tabs will waste much time. In this section I will discuss some tricks for arranging the sheet tabs in ascending or descending order.

 Sort worksheet tabs in alphabetical order with VBA code

The following VBA code can help to sort the sheet tabs alphabetically in an ascending or descending order, please do as this:

1. Hold down ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and then paste the following macro in the Module Window.

VBA: Sort all sheets in alphabetical order

Sub SortWorkBook()
'Updateby Extendoffice
Dim xResult As VbMsgBoxResult
xTitleId = "KutoolsforExcel"
xResult = MsgBox("Click Yes to sort sheets in ascending order;" & Chr(10) & "Click No will sort in descending order", vbYesNoCancel + vbQuestion + vbDefaultButton1, xTitleId)
For i = 1 To Application.Sheets.Count
    For j = 1 To Application.Sheets.Count - 1
        If xResult = vbYes Then
            If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
                Sheets(j).Move after:=Sheets(j + 1)
            End If
            ElseIf xResult = vbNo Then
                If UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) Then
                    Application.Sheets(j).Move after:=Application.Sheets(j + 1)
            End If
        End If
    Next
Next
End Sub

3. Then, press the F5 key to run this macro, in the following prompt box, click Yes, all worksheets will be sorted in ascending alphabetical order; Click No, all worksheets will be sorted in descending alphabetical order as you need.


 Sort worksheet tabs in alphabetical / alphanumeric order with a powerful feature

Kutools for Excel provides a powerful feature – Sort Sheets, with this feature, you can sort the worksheets in alphabetical or alphanumeric order, sort the sheets based on the tab color or reverse the sheet tabs as you need.

After installing Kutools for Excel, please do with the following steps:

1. Open the workbook that you want to sort the sheet tabs, and then click Kutools Plus > Worksheet > Sort Sheets, see screenshot:

2. In the Sort Sheets dialog box, select one sorting type that you need on the right pane, such as Alpha Sort, Alpha Numeric Sort. See screenshot:

3. Then, click Ok button, all worksheets will be sorted based on the sorting type you specified. See screenshots:


 Sort worksheet tabs based on tab color with VBA code

It is common for us to sort the worksheet tabs alphabetically in Excel, but have you ever tried to sort the sheet tabs based on the tab color? The below VBA code may help to solve this problem, please do as this:

1. Hold down ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and then paste the following macro in the Module Window.

VBA: Sort all sheets based on tab color

Sub SortWorkBookByColor()
'Updateby20140624
Dim xArray1() As Long
Dim xArray2() As String
Dim n As Integer
Application.ScreenUpdating = False
If Val(Application.Version) >= 10 Then
    For i = 1 To Application.ActiveWorkbook.Worksheets.Count
        If Application.ActiveWorkbook.Worksheets(i).Visible = -1 Then
            n = n + 1
            ReDim Preserve xArray1(1 To n)
            ReDim Preserve xArray2(1 To n)
            xArray1(n) = Application.ActiveWorkbook.Worksheets(i).Tab.Color
            xArray2(n) = Application.ActiveWorkbook.Worksheets(i).Name
        End If
    Next
    For i = 1 To n
        For j = i To n
            If xArray1(j) < xArray1(i) Then
                temp = xArray2(i)
                xArray2(i) = xArray2(j)
                xArray2(j) = temp
                temp = xArray1(i)
                xArray1(i) = xArray1(j)
                xArray1(j) = temp
            End If
        Next
    Next
    For i = n To 1 Step -1
        Application.ActiveWorkbook.Worksheets(CStr(xArray2(i))).Move after:=Application.ActiveWorkbook.Worksheets(Application.ActiveWorkbook.Worksheets.Count)
    Next
End If
Application.ScreenUpdating = True
End Sub

3. Then, press F5 key to execute this code, the sheets with the same tab color will be sorted together. See screenshots:


 Sort worksheet tabs based on tab color with an amazing feature

With Kutools for Excel’s Sort Sheets feature, you can also sort the worksheet tabs by color quickly and easily.

After installing Kutools for Excel, please do as this:

1. Click Kutools Plus > Worksheet > Sort Sheets to go the Sort Sheets dialog box, in the Sort Sheets dialog box, click Color Sort button on the right pane, see screenshot:

2. Then, click Ok button, the sheets with the same tab color will be arranged together as below screenshots shown:



  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.