Tips, Tricks, and Answers
The following articles are available for the 'Sorting' topic. Click the article''s title (shown in bold) to see the associated article.
Automatically Sorting as You Enter Information
When entering information into a worksheet, you may want it to always be in a correctly sorted order. Excel allows you to manually sort information, but automatic sorting takes the use of some macros.
Can't Sort Imported Data
Import information from an external database, and you'll no doubt want to use Excel's simple tools to manipulate that data. But what if the tools you are used to aren't available? Here's a couple of ideas about how to get back those tools so they can be used on the data.
Controlling Sorting Order
When you sort information in a worksheet, you have control over the order in which that information is sorted. Here's a quick discussion of the ways that you can affect the sorting order.
Creating a Sort Order
Excel is very flexible in how it can sort your data. You can even create your own custom sort order that is helpful when trying to get non-sequential information sorted in a unique order.
Fixing Odd Sorting Behavior
When you sort data that contains both numbers and text, you may not get exactly the result that you expected. To know what is going on, you have to know how Excel views your data.
Ignoring Selected Words when Sorting
If you use Excel to maintain a list of text strings (such as movie, book, or product titles), you may want the program to ignore certain words when sorting that list. This can't be done automatically, but there are ways to get your list in the order you want.
Importing Custom Lists
Custom lists are handy ways to enter recurring data in a worksheet. Here's how you can import your own custom lists from a range of cells.
Incomplete and Corrupt Sorting
Using the sorting tools, on the toolbar, may result in some unwanted results, such as jumbled data. If this happens to you, the best thing to do (as described in this tip) is to figure out exactly what is being sorted by Excel.
Incorrect Links after Sorting Hyperlinks
When you sort your data you should always check to see if the sort was done correctly. What if sorting messes up hyperlinks in a worksheet, though? That seems to be a unfortunate problem with Excel under some circumstances.
Moving Cell Borders when Sorting
Sort your data and you may be surprised at what Excel does to your formatting. (Some formatting may be moved in the sort and some may not be.) Here's an easy way to make sure that the formatting you apply is moved whenever you sort.
Information in a cell can be entered using line feeds, which results in multiple lines of data in the same cell. If you later want to sort that information, there are a couple of ways you can approach the problem.
Performing Complex Sorts
One way you can easily work with data in a worksheet is to sort it into whatever order you find most helpful. Excel allows you to perform sorting based on the criteria you determine, as discussed in this tip.
Recognizing a Header Row when Sorting
When you sort data in a worksheet, there are a couple ways you can do it. Using the simple way can result in unsatisfactory results if you aren't careful with how your data is organized.
Separating Cells Based on Text Color
If the font color used for the data in your worksheet is critical, you may at sometime want to move cells that use a particular cell color. This tip discusses a few different ways you can move the colorful cells where you want them.
Sorting a Range of Cells
When you sort data in a worksheet, you don't need to sort everything at once. You can sort just a portion of your data by using the techniques in this tip.
Sorting an Entire List
Need to sort all the data in a table? Here's the fastest and easiest way to do it.
Sorting by Colors
Need to sort your data based on the color of the cell or the color of the text within the cell? Excel makes it easy to do the sorting, as discussed in this tip.
Sorting by the Last Digits in Variable Length Data
Excel is great at sorting information in a worksheet. Sometimes it can be a bit confusing as to how to set up the sort based on the data you are working with. Here are a few ways to sort information based on the last four digits in a value.
Sorting Data Containing Merged Cells
When formatting the layout of your worksheet, Excel allows you to easily merge adjacent cells together. This can cause havoc later, however, since you can't sort ranges that contain merged cells. This tip provides some guidance on how you can get around this limitation.
Sorting Data on Protected Worksheets
Protect a worksheet and you limit exactly what can be done with the data in the worksheet. One of the things that could be limited is the ability to sort the data. This tip explains how you can allow that data to be sorted.
Sorting Dates and Times
One of the strong features of Excel is its ability to sort information in a worksheet. When it doesn't sort information as you expect, however, it can be confusing. Here's a look at why your dates and times might not sort the way you want them to.
Sorting Dates by Month
Sorting by dates is easy, and you end up with a list that is in chronological order. However, things become a bit more tricky when you need to sort by month. Here are several approaches you can use.
Sorting Decimal Values
Government and industrial organizations often use a numbering system that relies upon a number both before and after a decimal point. Sorting these numbers properly can be a bother; here's some ideas.
Sorting for a Walking Tour
Want to sort addresses by even and odd numbers? By using a formula and doing a little sorting, Excel can return the addresses in the order you need.
Sorting IP Addresses
If you keep track of IP addresses in an Excel worksheet, you probably already know that it is difficult to sort those addresses correctly. This tip examines several ways you can get perfectly sorted IP addresses every time.
Sorting Letters and Numbers
Sorting information in a worksheet can be confusing when Excel applies sorting rules of which you are unaware. This is especially true when you are sorting a mixture of letters and numbers.
Sorting while Ignoring Leading Characters
Want to ignore some characters at the beginning of each cell when sorting? The easiest way is to simply create other cells that hold only the characters you want to use in the sorting.
Sorting with Graphics
If you use graphics in a worksheet that are associated with certain cells (perhaps images of parts or icons for worksheet entries), you’ll be glad to know that when you sort your worksheet data you can have Excel reorder the graphics as well. This tip highlights the factors you need to consider when you want to do this type of sorting.
Sorting ZIP Codes
Sorting ZIP Codes can be painless, provided all the codes are formatted the same. Here’s how to do the sorting if you have different types of ZIP Codes all in the same list.
Storing Sorting Criteria
Need to do the same sorting operation over and over again? Excel doesn’t provide a way to save your sorting criteria, but there are a couple of workarounds you can try.
Too Many Formats when Sorting
Sorting is one of the basic operations done in a worksheet. If your sorting won't work and you instead get an error message, it could be because of the number of custom formats in your workbook. This tip provides information to help get rid of this problem.
Understanding Ascending and Descending Sorts
When you sort information, Excel follows a set pattern of how your data is organized. This tip illuminates the burning question of what ordering Excel uses.