Conditional Formatting & Sorting

Question

• Hi,

I am kind of an Excel novice...

I have applied multiple conditional formatting to ranges of cells within the same column.

When I then sort the work book the conditional formatting no longer wokrs properly.

Excel does not seem to remember the formatting applied to the original cell and is always storing the formating on each cell as a range. Not a specific formatting for the individual cell.

I need a way to apply multiple conditional formating on ranges of cells in a single column and then no matter how the sheet is sorting Excel will still maintain the formating.

Saturday, May 05, 2012 8:34 PM

• Conditional formatting in Excel with Icons won't do what you would like.  Ideally you could set the Icon Values to a formula that would allow more flexibility, but this doesn't work.  If you are willing to use fill instead of icons to indicate your highlights, you could do this:

Insert two hidden columns at G and H   (you already have some hidden columns)
Put the first number in column G (90 for Sales A, 80 for Sales B, 70 for Sales C)
Put the second number in column H (85 for Sales A, 70 for Sales B, 65 for Sales C)

Next build three rules covering the range \$B\$2:\$B:10
First select the range \$B\$2:\$B:10
Select fill color of red  (this will be the default when the conditions don't apply)
Select a New Rule "Use a formula to determine which cells to format"
Format values where this formula is true:  =AND(B2>=H2, B2<G2)
Select fill of yellow
Select a New Rule "Use a formula to determine which cells to format"
Format values where this formula is true:  =B2>=G2
Select fill of green

The conditional formatting will follow the sorting, but you don't get the icons.

-Rick

Tuesday, May 15, 2012 4:43 PM

All replies

• It is possible that your conditional formatting is sensitive to the position of the cell/value.

For example it is possible to create a conditional formatting rule that compares the value in a cell to the value in the cell below it.  If the data is sorted, the rules may not yield the same result.

gsnu201202

Sunday, May 06, 2012 9:29 PM
• Hi Gary,

Thanks for the reply.  Yes exactly that is my issue.  But I do not know how to solve it....  I am not an Excel programmer.  So I would really appreciate your help.....

I am trying to find a way to stop it..

Here is what I am trying to accomplish...........

Within the same column I need to set multiple conditional formats and be able to sort the rows and the conditional formatting to hold.

Here is my step by step approach...

1. I sort the rows in the order I want to have them in.

2. I then select my first set of cells and set up my conditional format across a range of cells. ex:   =\$H\$1:\$H\$55

3. I then select my second set of cells and set up my conditional format across a range of cells. Ex: =\$H\$55:\$H\$100

4. I then select my third set of cells and set up my conditional format across a range of cells. Ex: =\$H\$101:\$H\$200

So the formatting is across a range of cells.

Then when I sort the rows several different ways and the formatting does not stay with the cells........

It gets confused and messed up.....

I need to understand how to be able to accomplish this and still have this flexability.

1. Be able to set up the formatting easily without a lot of effort.

2. Have the conditional formating hold even after sorting.

Again Gary thanks for the reply.  I was getting a little discouraged with over 100 views and no replies....

Thanks!!!!!!!!

Monday, May 07, 2012 12:00 AM
• The rule for conditional formatting does not change with sorting.But obviously the result may change because the rule is now applied on new value.

If you need fix formatting then do not use cf.Simply format from Home tab.

Monday, May 07, 2012 8:06 AM
• Hi thanks for the reply...........

I am trying to figure out if there is a way to apply the conditional formatting rule so it won't change when I sort the rows....

I am really trying to use the icon sets for presentation value. Can that be accomplished by formatting from the home tab?

Monday, May 07, 2012 11:54 AM
• Nice Demand.I have uploaded one sample file detailing the steps.See if it helps.

http://www.sendspace.com/file/eozp63

This is an work around only and not ultimate solution.

Monday, May 07, 2012 12:13 PM
• wow..........

Very cool idea.... But it did not work............

The picture colors did not sort...........

My goal is to have the conditional formatting work even after sorting.

Using your example the picture did not sort at all so the picture end up not designating the level correctly.

Unless I am doing some thing wrong which is totally possible.

Thanks...........

Tuesday, May 08, 2012 3:17 AM
• Hmmm.....

The CF rule does not change with sorting.But as you sort cells then the result will vary.I thought you need to fix the iconset irrespective of cell value and pic will do that.

ANy way can u share a file (SkyDrive/Sendpace) with one sheet before sorting and one one sheet after sorting.

Tuesday, May 08, 2012 4:38 AM
• Sample file before and after sort is at the link below...

http://www.sendspace.com/file/7em4hv

Thursday, May 10, 2012 9:30 AM
• Hi all,

I appreciate the replies I have gotten. But my issue remains unsolved.

I have posted an example for the issue I am trying to solve...............

Does anyone have a way to solve this?  It would be very helpful to me......

Thanks Again.....

Monday, May 14, 2012 8:48 AM
• Hi,

In this issue, we should use Custom Sort to do.

First, do as what you did using Conditional Formatting to these records.

Then do as the following steps:

Step 1: Select only the rows that you want to sort. In the example, that would be rows 2-15. You can select these rows by clicking in the cell A2 and dragging to G10.

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.

Jaynet Zhang

TechNet Community Support

Tuesday, May 15, 2012 1:43 AM
• Jaynet,

That is exactly what I do.  But as you can see from your example the formatting does not hold to the cell........

Foer Sales A 98 is green and 89 is yellow.....  When sorted all sales A are green.....  This is not what I want to do.. I would like the conditional formatting to work even after sorting.  So the color associated with the cell remains even after sorted.....

Can you help with a solution ???

Tuesday, May 15, 2012 10:55 AM
• Conditional formatting in Excel with Icons won't do what you would like.  Ideally you could set the Icon Values to a formula that would allow more flexibility, but this doesn't work.  If you are willing to use fill instead of icons to indicate your highlights, you could do this:

Insert two hidden columns at G and H   (you already have some hidden columns)
Put the first number in column G (90 for Sales A, 80 for Sales B, 70 for Sales C)
Put the second number in column H (85 for Sales A, 70 for Sales B, 65 for Sales C)

Next build three rules covering the range \$B\$2:\$B:10
First select the range \$B\$2:\$B:10
Select fill color of red  (this will be the default when the conditions don't apply)
Select a New Rule "Use a formula to determine which cells to format"
Format values where this formula is true:  =AND(B2>=H2, B2<G2)
Select fill of yellow
Select a New Rule "Use a formula to determine which cells to format"
Format values where this formula is true:  =B2>=G2
Select fill of green

The conditional formatting will follow the sorting, but you don't get the icons.

-Rick

Tuesday, May 15, 2012 4:43 PM