Cite.Co is a repository of information and resources created by industry seniors and experts sharing their real world insights. Join Network
I have a spreadsheet with two different value for example
column a column b column c
100 100
200 150
80 100
i want if column a data match with the col b data automatically colour formatting done with diff value if not match diff colour appearing on the col c

From India, Pune
Subrata, you can try the following to format the column on conditions.

Based on your query, I think you are trying to colour the format of column C(from cell C1:C3) on satisfying following conditions:

1) If cell value of Column A matches with cell value of Column B ( here I have marked the cell in Yellow colour in respective cell in column C)

2) If cell value in Column A does not match with column B ( here I have marked the cell in Red colour in respective cell in column C)

3) More over I have considered here also,that if there is no value in cell A and B column i mean blank cell then the difference will be appear as blank in column C and the respective cell will be remain with no color.

So to do the conditional format as mentioned above please go through below steps:

Go to Home Menu--> Select conditional format-->Select "Use a formula to determine which cells to format"

Put the format as per following:

=AND($A1=$B1,NOT(ISBLANK($A1)),NOT(ISBLANK($B1)))

Click "Format" button and select the "Fill" color. Here I have selected "yellow" color. Press "Ok". In applies to area, put the formula as =$D$1:$D$3

Press New Rule. In formula are put the formula as:=$A1<>$B1. Select Format and repeat the above step. In here I have selected "red" color. Press "OK" . In applies to area, put the formula as =$D$1:$D$3 and click on "Apply" button.

=$D$1:$D$3

For calculating difference of Column A & B put the formula in Cell C1 as follwing:

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1-B1)

The above formula will calculate difference only if there is no cell blank, other wise cell values in Column C will remain blank and no format will be done.

To understand the above please find attached herewith the excel file. For any query feel free to write me: or visit my blog site: Advanced Excel Tips - Manage your MIS Report

From India, Kolkata
Subrata, you can try the following to format the column on conditions.

Based on your query, I think you are trying to colour the format of column C(from cell C1:C3) on satisfying following conditions:

1) If cell value of Column A matches with cell value of Column B ( here I have marked the cell in Yellow colour in respective cell in column C)

2) If cell value in Column A does not match with column B ( here I have marked the cell in Red colour in respective cell in column C)

3) More over I have considered here also,that if there is no value in cell A and B column i mean blank cell then the difference will be appear as blank in column C and the respective cell will be remain with no color.

So to do the conditional format as mentioned above please go through below steps:

Go to Home Menu--> Select conditional format-->Select "Use a formula to determine which cells to format"

Put the format as per following:

=AND($A1=$B1,NOT(ISBLANK($A1)),NOT(ISBLANK($B1)))

Click "Format" button and select the "Fill" color. Here I have selected "yellow" color. Press "Ok". In applies to area, put the formula as =$D$1:$D$3

Press New Rule. In formula are put the formula as:=$A1<>$B1. Select Format and repeat the above step. In here I have selected "red" color. Press "OK" . In applies to area, put the formula as =$D$1:$D$3 and click on "Apply" button.

=$D$1:$D$3

For calculating difference of Column A & B put the formula in Cell C1 as follwing:

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1-B1)

The above formula will calculate difference only if there is no cell blank, other wise cell values in Column C will remain blank and no format will be done.

To understand the above please find attached herewith the excel file. For any query feel free to write me: or visit my blog site: Advanced Excel Tips - Manage your MIS Report

From India, Kolkata

Attached Files
File Type: xls conditional_format.xls (18.0 KB, 390 views)

Hi
I am using conditional formatting for my attendance sheets, i need a single format style to apply for various leaves and absent.
so i am choosing
format only cell that contain->
cell value -> equal to-> SL
for higlight sick leave
but i need to do same steps for all values. there is no copy option for same formatting.
please suggest if i can do it somthing like
cell value -> equal to-> ="SL","CL",EL","A"

From India, New Delhi
This discussion thread is closed. If you want to continue this discussion or have a follow up question, please post it on the network.
Add the url of this thread if you want to cite this discussion.






About Us Advertise Contact Us
Privacy Policy Disclaimer Terms Of Service



All rights reserved @ 2020 Cite.Co™