Comments on: Relative and absolute cell references in Excel conditional formatting

Comments page 2. Total comments: 50

1. 

   Ahmad says:
   2015-01-18 at 1:24 am

   How to create a condition like this: I want to count the number of cells of a range (let's say B1:B20) whose values are 10% of another cell, let's say B32.

   1. 

      Svetlana Cheusheva says:
      2015-01-20 at 5:22 pm

      Hi Ahmad,

      You can use the following array formula (remember to press Ctrl+Shift+Enter to get it to work correctly):

      =SUM(--(B1:B20=B32*0.1)*(B1:B20<>0))

      Please note that the formula counts cells in B1:B20 that are exactly 10% of B32. If you want, say 10% or less, then change the condition to B1:B20<=B32*0.1

      1. 

         Mujgan says:
         2019-01-23 at 10:47 am

         1What is the code used to reference the cell in the eleventh column and fortieth row?

2. 

   Asher says:
   2014-10-29 at 7:04 pm

   Hi Svetlana, quick question for you if you have a moment.

   I have a worksheet with conditional formatting down an entire column, delineated simply as $K:$K. However, as part of my team's process, we are constantly inserting new rows into the top of the range, and it's causing my Conditional Formatting rules to duplicate, with one set continuing the $K:$K applies, but the duplicates to apply only to the inserted range.

   Is there a way I can prevent that, either at setup or with a process change to how we insert our rows?

   Thanks so much!

   1. 

      steve says:
      2019-09-11 at 12:07 pm

      Did you ever find a solution to this problem?