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
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
15Based 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:
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
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
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
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
Community Support and Knowledge-base on business, career and organisational prospects and issues - Register and Log In to CiteHR and post your query, download formats and be part of a fostered community of professionals.
Advanced Excel 2003.pdf
152120-very-use ful-hr-manageme nt-software-bui lt-excel-psa-hr -manager-mainta in-payrrol-exce l-soft.zip
Competancy--exc el---mts___smts _289.xls