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 (Download Requires Membership)
File Type: xls conditional_format.xls (18.0 KB, 408 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

If you are knowledgeable about any fact, resource or experience related to this topic - please add your views using the reply box below. For articles and copyrighted material please only cite the original source link. Each contribution will make this page a resource useful for everyone.

Please Login To Add Reply →






About Us Advertise Contact Us Testimonials
Privacy Policy Disclaimer Terms Of Service

All rights reserved @ 2021 CiteHR™

All Material Copyright And Trademarks Posted Held By Respective Owners.
Panel Selection For Threads Are Automated - Members Notified Via CiteMailer Server