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.
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.
Determining Sorting Criteria
If you need to know how a range of data is sorted, the task is not as easy as you might at first think. This tip examines why such determinations are difficult and how you can go about trying to figure out the criteria.
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.
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.
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? Excel doesn't include this capability, but you can still do the sorting if you rely upon a macro to do the work.
Sorting by Columns
When you think of sorting Excel data, it is likely that you think of sorting rows. Excel also allows you to sort by column, however, as described in this tip.
Sorting by Fill Color
Sorting data in a worksheet is easy, unless you want to sort by the color used to fill a range of cells. There are ways to do the sorting, however, as illustrated in this tip.
Sorting by Five Columns
Excel allows you to sort but up to three columns, but you may want to sort by more than that. This tip provides ways you can get the desired sorted data.
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 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 Huge Lists
Got a huge amount of data you need to sort in a worksheet, but Excel doesn't seem to be sorting it correctly? Here's some great information on why this may be happening and what you can do about it.
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 or Filtering by Conditional Format Results
Conditional formatting is a great feature in Excel. Unfortunately, you can't sort or filter by the results of that formatting. Here's why.
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.
As yo get more and more worksheets into a workbook, you'll find yourself moving them around into different sequences. You may even want to put the worksheets into alphabetic order. This tip provides a macro that does the sorting for you.
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.