How to Sort a Spreadsheet with Conditional Formatting in Microsoft Excel 2007
Problems Sorting Spreadsheets with Conditional Formatting
After applying conditional formatting rules to a Microsoft Excel spreadsheet, you may find that you don’t like the way the data is sorted. In fact, the visual results of the formatting may be the very thing that gives you an idea for a new way to sort the information. The problem is, if you’re not careful, the act of sorting the information can destroy all the hard work that went into developing the formats in the first place.
In this example, we’ll return to the table containing sales information that we’ve been looking at throughout this series. We’ve made a few changes to it, and the current version is shown in the screenshot below. (Click the image for a larger view.)
Now, we’d like to sort this table by Total Yearly Sales with the highest values appearing first. In the following instructions, we’ll show how to do that without messing up the rest of the data or changing the conditional formatting rules.
Step 1: Select only the rows that you want to sort. In this example, that would be rows 2-15. You can select these rows by clicking in the cell A2 and dragging to G15.
Step 2: On the Home tab of the Excel ribbon, click on Sort & Filter in the Editing section. Then, select Custom Sort.
Step 3: In the Sort window, you have several options from which to choose. First, if your data selection contains a header, put a check in the box next to My data has headers. Note that you don’t have to have the header row (and shouldn’t!) selected for this option to work – Excel will automatically look for headers in the worksheet. If you’re not using headers, the default titles of Column A, Column B, and so on, will be used as names for the columns.
Next, pick which column you want to Sort by. In this example, choose Total Yearly Sales. Also choose to Sort On Values and to Order from Largest to Smallest.
Step 4: Click the OK button when done. The changes will be applied to your worksheet as shown in the image below.
This post is part of the series: Microsoft Excel 2007: Conditional Formatting
This series of articles explores the many different ways that conditional formatting can be used to analyze data in Excel spreadsheets.
