by Allen Wyatt
(last updated May 24, 2021)
Jenny has a large worksheet that she often needs to sort. Several times the data has gotten "jumbled" after a sort. Jenny wonders if there is a way other than manually selecting all the cells to be sorted to ensure that all the data is selected before Excel actually performs the sort.
The biggest thing you can do to prevent jumbled sorts is to remember how Excel "detects" the data you want to sort. Starting with the currently selected cell, Excel looks in all directions (up, down, left, and right) until it finds empty rows or columns. Once it finds the empty row or column, it figures it has found a boundary for the data you want sorted. Thus, if your data that you want sorted actually contains empty rows or columns, sorting will invariably mess up your data because Excel won't catch everything.
The first way around this is to select the rows and columns—all the data—that you want sorted before performing the sort. The better solution, though, is to make sure there are no empty rows or columns in your data—just delete them or place something in the row or column so Excel recognizes it as part of your data.
If your data is jumbled by the column headings moving around after sorting, then you need to make sure the headings are differentiated from the data in the table in some way. I like to make sure that they are bold and each cell is underlined. This has typically done the trick so that Excel automatically recognizes them as headings and not as data. Plus, if I include a heading in an otherwise empty column, that column is no longer empty and Excel recognizes it as part of the data to be sorted. (This helps alleviate the problem of blank columns discussed earlier.)
Another way to help prevent jumbled sorts is to define your data as a table. You do this by selecting the rows and columns that make up your data and then pressing Ctrl+T. Excel displays the Create Table dialog box, where you can verify that all of your data is selected. Click OK in the dialog box, and the table is created. When you choose to sort the table, all of the data—including any blank rows or columns in the table—is included in the sort.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6285) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
One of the strong features of Excel is its ability to sort information in a worksheet. When it doesn't sort information ...Discover More
Sorting information in a worksheet can be confusing when Excel applies sorting rules of which you are unaware. This is ...Discover More
Want to ignore some characters at the beginning of each cell when sorting? The easiest way is to simply create other ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.