Pin Me

Sorting Spreadsheets with Conditional Formatting in Excel 2007

written by: •edited by: Tricia Goss•updated: 12/29/2009

In order to make sure that your conditional formatting rules remain intact, it’s best to use a customized sorting method whenever you want to change the order of your data. Read on to find out how to apply this technique.

  • slide 1 of 1

    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.)

    Original Sales Table 

    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 

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