Show
How to sort in Excel list by row or column. Tips show how to avoid painful mistakes when sorting your spreadsheets. How to sort multiple columns, rows, or sort in custom order. Videos, and Excel workbook. Avoid Sorting Trouble: Check Your Data1) 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. Sort Two or More ColumnsIf you want to sort 2 or more columns in an Excel table, use the Sort dialog box, where you can set up a multi-level sort. In this example, the table contains personal data, and it will be sorted by 3 columns:
Follow these steps to safely sort the data set by the 3 columns:
Note: If the dropdown is showing Column letters instead of headings, add a check mark to My data has headers.
The data will be sorted in the order that you specified. In the screen shot below:
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. In this example, we'll sort a column with weekday names, using the Excel Ribbon command. Watch the steps for doing a custom sort in the Sort Custom Order video, or follow the written instructions, below the video. 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. In this example, we'll sort a column with weekday names, using the Excel Ribbon comman 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. Sorting a RowInstead of sorting your data by columns, you can sort the data by row. In this example, we'll sort a table of monthly sales, so the month with the largest sales total is at the left. To do this, we'll use a right-click popup menu. You can see the steps in this short Sort by Row video, and read the detailed instructions below. Sorting a RowInstead of sorting your data by columns, you can sort the data by row. In this example, we'll sort a table of monthly sales, so the month with the largest sales total is at the left. To do this, we'll use a right-click popup menu. To sort by a row, follow these steps:
The data is sorted by the values in the selected row. Sort Data by Cell IconWatch this short video to see the steps for adding cell icons, and sorting by the selected cell's icon. There are written steps below the video. When you create a named Excel table, or apply an AutoFilter to a list, each heading cell gets a drop down arrow. Click that arrow, and you'll see a variety of sorting and filtering options for the data. If you add conditional formatting icons to one of the columns, you can also sort by those icons. In the screen shot below, Traffic light icons are being added to the Quantity column. Sort by Selected Cell IconAfter adding icons, the quickest way to sort by a specific icon is:
The list is sorted, to move all items with the selected icon to the top of the list. Other items are not sorted, and the items that were moved to the top of the list are left in their original order, within that group. 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 file is in xlsx file format, and is zipped. It does not contain any macros. More TutorialsSort Data With Macros Sort Data - Excel 2003 Basics Excel Add-in Advanced Sorting Pivot Table Sorting Sort a Row in Excel Numbers Don't Sort Correctly |