With Conditional formatting, if a document is to be prepared with two columns A & B with numbers in column A and rank positions in column B, a formula needs to be set for conditional formatting in column B. When the number in cell B is greater than that of A, the color assigned is red, if it is not greater, it is assigned the color green, dropping down the rank position. Formatting fails to be applied on all columns of the document since it isn't a function but a cell format. Once relative referencing is used, the $ sign needs to be removed from the original formula of =$B2>$A$2. Before applying this formula, the entire range of the Excel spreadsheet needs to be selected or the option of format painter needs to be used.
[Conditional formating] Apply on more cells
I'm not so familiar with conditional formatting, so I would like to ask something:
I'm making a doc where Column A has number values, and column B also has numbers, of rank positions. Conditional formating of cell in column B would be: if the number is greater than the number in cell A, color it Red, and if it is less than the number in cell A, color it green (rank position dropped down). Now, I managed that, but since the document has lots of rows, how do I apply the formating on the other cells in the column? If I make copy/paste to them, formatting doesn't apply on the whole column since it is not a function, but a cell format. Do I have to do the formatting every day for every next column?
The "secret" is to make the formula with "relative referencing", hence, if your formula looks like this:
then remove the $ sign before the row number, so it will look like this:
With this, you actually don't need to copy the conditional formatting. You can select an entire range before applying it, and insert the formula as if you selected only the first cell at the top. Excel will know to apply the correct formulas to all the other cells in the range (assuming the range is a column).
Or you can apply this formula to the first row, and copy it with the format painter.
You can have a look at the video named "Highlight the entire row based upon a cell value (with a formula)" at the following address:
It gives an example of using a similar formula with such "relative referencing".
Thanks to sheryljohnson
for this tip on the forum.
Published by aakai1056
- Latest update by Celia Gatward