Avoid Sorting Trouble: Check Your DataIn the sections below, there are 3 key steps, to help you avoid problems when sorting your data in Excel. -- 1) Make a backup -- 2) Check your data -- 3) Fix blank rows and columns 1) Make a Backup of Your DataBe sure to make a backup copy of your Microsoft Excel file, before you start sorting the data. Then, you can go back to the saved version, if anything goes wrong. Tip: For a quick and easy backup copy, get my free Excel Backup tool. This tool makes a backup copy in the current folder, and does NOT affect the active workbook. The backup tool is in xlam format, so it's easy to install on your computer - just like any other Excel file. 2) Check Your DataBefore you sort data in Excel, be sure there are no blank rows or columns within the data range on the worksheet. The steps below show how to do that. Why is it important to check your data?
|
How to Check For Blank Rows or ColumnsTo help prevent sorting problems, follow these steps before you sort Excel data:
|
3) Fix Blank Rows and Columns
Then, after you fix any blank columns or rows:
|
Quick Sort With Sort ButtonsIn Excel, you can quickly sort your data by using the A-Z (ascending order) and Z-A (descending order) buttons on the Ribbon's Data tab. Follow these steps to sort with the Quick Sort command buttons:
Check the ResultsImmediately after sorting, and before you do anything else:
If anything looks incorrect, or out of order:
|
Sort Buttons on Quick Access ToolbarIf you sort frequently, you can add the Sort buttons to your Quick Access Toolbar (QAT). That makes it even easier to do a quick sort in Excel. There are "how to" steps on the Quick Access Toolbar page. that show how to add buttons. Then, to use those Sort icons, follow the steps in the Quick Sort with A-Z Buttons section, but use the QAT buttons instead. |
Add Sorting LevelsNext, follow these steps to add the first sorting level:
Note: If the dropdown is showing Column letters instead of headings, add a check mark to My data has headers. |
Select Sort On Option
![]()
|
Add More LevelsIf you are sorting on multiple columns, you can add a level for each column that you want to sort.
In this example we are sorting by Gender, then by State (second level), and then by BirthYr (third level). In the screen shot below, all 3 fields have been added in the Sort dialog box.
Apply the SortAfter you have selected all the Sort levels that you need, and their options, it's time to apply the sort settings.
|
Check the SortAfter you click OK, the data will be sorted in the order that you specified.
For this example, in the screen shot below, everything was sorted as expected:
|
Sort in a Custom OrderIn the Sort dialog box, or on the Excel Ribbon, you can select a sort order, such as A to Z, or Largest to Smallest. In addition to these standard options, you can sort in a custom order, such as month order, or weekday order.
Sort by Weekday NamesIn this example, we'll sort a column with weekday names, using the Excel Ribbon command. To sort in a custom order, follow these steps: The Day column is sorted in weekday order, instead of alphabetical
order, so Sunday appears at the top of the list. |
Create a Custom ListIn Excel, you can create custom lists, like the built-in lists of weekdays and months. For example, you could create a custom list of districts, department names, or reporting categories, and then use the custom lists to sort the items in a list or in a pivot table. Open the Custom Lists DialogTo open the Custom Lists dialog box, follow these steps, or use the keyboard shortcut in the next section:
Keyboard Shortcut to Open Custom Lists DialogThis is more of a "longcut" than a shortcut, but here's how you can open the Custom Lists dialog box with keystrokes, if you prefer to keep your hands on the keyboard:
Create a New Custom ListThe entries for the custom list can be imported from a worksheet list, or typed in the Custom Lists dialog box. In this example, the list of cities is typed. To create a new custom list, follow these steps:
Use the Custom ListYou can use the custom lists when sorting, and you can also use them with the AutoFill feature. To quickly create a list, based on a custom list:
|
Sort With Heading Drop Down ListAfter you have added cell icons, you can also sort by icon from the drop down list in the heading.
|
VLOOKUP Sorting ProblemA VLOOKUP formula may return the correct results at first, but then shows incorrect results if the list of items is sorted. This can occur if the reference to the Lookup value includes a sheet name. For example: =VLOOKUP('Order Form'!B5, Products!$B$2:$C$6,2,FALSE) NOTE: This problem can occur with other functions too, such as an INDEX/MATCH lookup formula. Watch this video to see the steps for fixing the problem, and download the VLOOKUP Sorting problem sample file to follow along. The written instructions are on the How to Use Excel VLOOKUP - Examples page. |
Sort Sample WorkbookTo try the sorting techniques, get the Sort sample workbook. The practice workbook is in xlsx file format, and is zipped. It does not contain any macros. |
More Tutorials |
Last updated: December 6, 2023 1:57 PM