6

Why Excel Conditional Formatting does not work the same as formula? I have a couple thousand rows in a file. Column A has AAA value, column B has BBB value.

       Column A   Column B                                                                
row1   1000        10
row2   2000        20
row3   3000        30

If I want to write a formula A1*B1 it will give a result 10000. When I drag it, it will automatically change source cells so it would give me A2*B2 = 40000.

So why conditional formatting doesn't do exactly the same when you copy formatting to different cell/row?

What I want to do is to set conditional formatting for each row separately:

x1.row1 > x2.row1 - make it green.

for second row: IF(x1.row2 > x2.row2) - make it green,

for third row it should be: IF(x1.row3 > x2.row3) make it green, and so on,

But Excel for every next row, compares always to first cell i.e. x1.row1 > x2.row2, x1.row1 > x2.row3 and so on.

Is there a way to apply formatting for each row separately, without creating multiple conditions?

1
  • 1
    Mark, It can be done. Conditional formatting interface is not very userfriendly, but quite powerfull. Can you give a few screenshots of what you've done and I will help you (sorry to write an answer instead of a comment, but my reputation is not high enough to comment) For a first start, you can try to remove the $ in the conditional formatting formulas to see if it works for you. Jun 23, 2017 at 12:16

3 Answers 3

6

From my understanding, you want to do a conditional formating of every cell in column x1 if it's value is higher than it's corresponding cell in column x2. To do it :

First, setup your conditional formating on the first cell

  1. Click on your first cell
  2. Go in Conditional formating -> New rule
  3. In the Conditional Formatting Rules Manager, click New Rule...
  4. Click Use a formula to determine which cells to format
  5. Enter the condition for the formatting under Format values where this formula is true :
    • You can enter any formula you want
    • Just keep in mind that if the result of the formula is true, the conditional formatting will be applied and if not it will not be applied
    • In your formula, use or remove all the dollar signs $ you want so that the formula would usually update when the cell is copied if you were copying cells in Excel.
    • See this article absolute vs relative cell reference in Excel
    • If you are not sure if your formula returns TRUE or FALSE or if the result is always as you want it, you can try it in column x3 to see if it works.
    • So, in your example, your conditional formatting formula would be =$A1>$B1 or =A1>B1. It is important that there is not $ in front of the 1.
  6. Then set all the format you want in your conditional formatting using the Format button.
  7. Accept your conditional formatting (Ok button) and go back to the Conditional formatting rules manager window. You will see your new rule.

Apply your rule to the area you want

There are 2 ways to do it :

  1. In the Conditional formatting rules manager window, under the Applies to column, change the range you want your conditional formatting to be applied to the whole range you want.

    • If your conditional formatting formula was well done, it will apply and update to the whole range even if the interface does not give you any clue about it.
  2. Directly in the worksheet (Closing the Conditional formatting rules manager), copy the format of your first cell to all the rows you want. There are many ways to do so...

    • Using the Format Painter
    • Copying you first cell by dragging it and then clicking the contextual menu ->Fill formatting Only

    • See here for more information about it.

And you are all set!

1

You can use a formula in Conditional Formatting and it will change for each Row:

=$A1>$B1
  • Select the Data that you need to Format
  • Conditional Formatting
  • New Rule
  • Use a Formula to determine which cells to format
  • Write the above formula in the Box
  • Format as you like
  • Ok

Change A1 and B1 to correspond the First Cell in each row you want to compare but keep the $ for fixed column

0

Here are some ideas for if you follow the above advice (which is good advice) but there is still a problem.

Sometimes, it is because a $ is very important. Sometimes, the '=' sign is forgotten when you type the formula. Sometimes, it is because, when stating a range of cells, it goes funny when you try to edit its suggestion - maybe the colon necessary for a range of cells is removed by its 'intelligence' when you start typing. Sometimes, near the top of the dialog is a dropdown box where 'Current Selection' is usually the right thing to have selected. If I physically select it, it can help.

1
  • 1
    I believe you mean this to be good, but without some clear examples, not many people will get it :-)
    – Dominique
    Feb 21 at 9:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .