Take the 2-minute tour ×

If I have 3 layers of conditional formatting in a cell (B2) is there a way of transferring the format of cell (B2) to another cell (A2) without copying across the value in the original cell (B2).

I.e. in b2 I have conditional formatting dependent on what is selected from a drop down list of 4 options). I want the format to be copied across the the cell before this (A2) without the values being copied across. Essentially A2 has the title and B2 has the value but I would like the same formatting across both.

I have manged to do it by setting A2 as ="Title "&B2 and applying conditional formatting to this. The spacing means the text from B2 doesn't show. This works but is really ugly.

Is there a better way givern there ae too many conditions for a standard IF function.

(if possible the solution should be windows 2003 compatible)

share|improve this question
Ctrl+Drag Cell if my memory does'n fail me, and if i understand you right. Also there are paste special and $ sign. –  Ishikawa Yoshi Aug 16 '12 at 15:21
add comment

1 Answer

This is quite possible. If you are evaluating data in A1 against B1 you will need to write your conditional format with hard column references. For example $A1 = $B1

Notice the $ sign in front of the column reference. This makes them an absolute reference instead of a relative reference that changes when the cell is copied.

When that is done, copy the cell with the conditional formatting and then paste that information as a paste special formatting and the conditional formats will be copied. It will also copy all the other cell formatting (bold, font and font size, color, etc.)

This will work in all versions of Excel from at least Excel 2003 and later

share|improve this answer
Do you know if in eXcel or openoffice it exists something similar to what I asked here? To copy only the conditional format value as result and not as formula? –  Hastur Jun 11 at 22:07
add comment

Your Answer


