How to sort rows by upper lower case in Excel?
When working with lists in Excel that contain a mix of uppercase, proper case, and lowercase text, you may encounter situations where you need to organize your data based on text case—for example, placing all uppercase entries first, followed by proper case entries, and then lowercase ones, or the reverse order. Although Excel’s native sorting options do not provide a direct way to sort by letter case, there are effective workarounds that can help you achieve this. Sorting in a case-sensitive manner is especially useful when handling data that follows specific naming conventions, codes, or identifiers where case may indicate different classifications or levels. Below is a demonstration of such a dataset:
Table of Contents
- Sort rows by upper lower case with a helper column
- VBA macro for case-sensitive sort without helper column
Sort rows by upper lower case with a helper column
While Excel does not natively offer a case-sensitive sort function, you can use a helper column formula to categorize each cell’s casing, and then sort the entire table based on that classification. This method is practical if you prefer to use formulas and Excel’s built-in tools, and you can easily adjust the formula to suit more complex text structures.
1. Enter the following formula into a blank cell next to your data—suppose your data begins in cell A2, enter this formula in cell B2:
=IF(EXACT(A2,UPPER(A2)),"Upper Case",IF(EXACT(A2,LOWER(A2)),"Lower Case",IF(EXACT(A2,PROPER(A2)),"Proper Case","Other")))This formula checks each value in cell A2 and classifies it as “Upper Case”, “Lower Case”, “Proper Case”, or “Other” if it does not fit any of the three types. If you are working with more complex classification needs, you can modify this formula as needed. Refer to the screenshot below for how the formula should appear:
2. Use the fill handle to copy this formula down the column for all rows you wish to classify. The results will display text cases for each item in your list. Please ensure your references point to the correct cells when filling down:
3. After adding the case classification in column B, select the entire data range, including the helper column (e.g., A1:B11). Click Data > Sort, then in the Sort dialog box:
- Choose the helper column (“Case” or B) under Column
- Select Values under Sort On
- Choose the desired sort order (“A to Z” or “Z to A”) depending on whether you want lower case first, or upper case first
Note: If your table has headers, check the My data has headers option to ensure accurate sorting.
4. Click OK in the dialog to sort. Your list will now be grouped by text case—lower case will follow proper case and then upper case (if sorted “A to Z”); reverse the order to place upper case first. The sorted result is demonstrated below:
Tips and Reminders:
1. After sorting your data, you may delete the helper column to keep your sheet tidy.
2. To reverse the sort order (for example, to have uppercase first), select “Z to A” instead of “A to Z” in the Sort dialog.
3. For long lists, double-check the formula references when dragging to prevent errors.
4. Be mindful that the helper column method does not sort within each group by alphabetical order unless you include a second sorting level in the dialog.
VBA macro for case-sensitive sort without a helper column
If you need to perform an actual case-sensitive sort instead of categorical grouping, and want to avoid using helper columns, Excel’s built-in sort is not sufficient because it does not distinguish different text cases during sorting. In such situations, you can use a VBA macro to sort data directly with true case sensitivity. This is particularly useful for large datasets, automated processes, or recurring needs, as the macro executes the sorting instantly and can be reused.
This method is ideal when you want more control over sorting logic, or needto automate the process for multiple sheets or ranges.
Precautions: Macros will overwrite the sort order of your selection. Always back up your data before running new VBA codes, and ensure macros are enabled in your Excel instance.
Follow these steps:
1. Go to Developer Tools > Visual Basic. In the Visual Basic for Applications window, click Insert > Module. Paste the following code into the new module:
Sub CaseSensitiveSort()
Dim WorkRng As Range
Dim ws As Worksheet
Dim col As Integer
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select Range to Sort:", xTitleId, WorkRng.Address, Type:=8)
col = Application.InputBox("Enter the Column Number to Sort By (e.g.,1 for first column):", xTitleId, 1, Type:=1)
Set ws = WorkRng.Worksheet
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=WorkRng.Columns(col), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange WorkRng
.Header = xlYes
.MatchCase = True
.Apply
End With
End SubThis macro allows you to select the range you wish to sort, specify which column to use for sorting (by column number), and then applies a true case-sensitive sort. The .MatchCase = True argument ensures that upper, lower, and proper cases are sorted distinctly.
2. To execute the macro, return to the main Excel window, press the F5 key, or click Run to run the VBA. You will be prompted to select the range and specify the column number to sort by. Once you confirm, the macro will sort the data with case sensitivity.
Troubleshooting and Tips:
- If you encounter any errors, double-check the range selection and column number input.
- Selecting the wrong column number may lead to unintended sort results.
- The macro will only work within the selected worksheet; if sorting across multiple sheets, repeat the process.
This solution provides a more robust sort for complex or automated case-sensitive sorting needs, making it suitable for advanced users or those seeking to avoid the manual steps of helper columns.
Related articles:
How to sort by file extension in Excel?
How to sort a list of column by character length in Excel?
How to sort data by the most frequent value in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- 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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in