Preventing Jumbled Sorts

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, 2016, 2019, Excel in Microsoft 365, and 2021.