How to Sort a Spreadsheet with Conditional Formatting in Microsoft Excel 2007

How to Sort a Spreadsheet with Conditional Formatting in Microsoft Excel 2007
Page content

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.

Select Rows to Sort

Step 2: On the Home tab of the Excel ribbon, click on Sort & Filter in the Editing section. Then, select Custom Sort.

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.

Sorting Choices

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.

Sorted Sales Table

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.

  1. Introduction to Conditional Formatting in Microsoft Excel 2007
  2. Adding Conditional Formatting Rules in Excel 2007
  3. Sorting Spreadsheets with Conditional Formatting in Excel 2007