I have a spreadsheet with two different values. For example:
Column A Column B Column C
100 100
200 150
80 100
I want if data in column A matches with the data in column B, automatic color formatting should be done with a different color. If they do not match, a different color should appear in column C.
From India, Pune
Acknowledge(0)
Amend(0)
Column A Column B Column C
100 100
200 150
80 100
I want if data in column A matches with the data in column B, automatic color formatting should be done with a different color. If they do not match, a different color should appear in column C.
From India, Pune
Acknowledge(0)Amend(0)
Subrata, you can try the following to format the column based on conditions.
Conditional Formatting Based on Column Values
Based on your query, I think you are trying to color the format of column C (from cell C1:C3) based on the following conditions:
1. If the cell value of Column A matches the cell value of Column B (here I have marked the cell in yellow color in the respective cell in column C).
2. If the cell value in Column A does not match with column B (here I have marked the cell in red color in the respective cell in column C).
3. Moreover, I have also considered that if there is no value in cell A and B column, i.e., a blank cell, then the difference will appear as blank in column C, and the respective cell will remain with no color.
Steps to Apply Conditional Formatting
To do the conditional format as mentioned above, please follow the steps below:
- Go to Home Menu → Select conditional format → Select "Use a formula to determine which cells to format."
- Put the format as per the following:
`=AND($A1=$B1,NOT(ISBLANK($A1)),NOT(ISBLANK($B1))) `
- Click the "Format" button and select the "Fill" color. Here I have selected "yellow" color. Press "Ok." In the "Applies to" area, put the formula as `=$D$1:$D$3`.
- Press New Rule. In the formula box, put the formula as: `=$A1<>$B1`. Select Format and repeat the above step. Here I have selected "red" color. Press "Ok." In the "Applies to" area, put the formula as `=$D$1:$D$3` and click on the "Apply" button.
Calculating the Difference in Column C
For calculating the difference of Column A & B, put the formula in Cell C1 as follows:
`=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1-B1)`
The above formula will calculate the difference only if there are no blank cells; otherwise, cell values in Column C will remain blank, and no format will be applied.
To understand the above, please find attached herewith the Excel file. For any queries, feel free to write me: [Email Removed For Privacy Reasons] or visit my blog site: Advanced Excel Tips - Manage your MIS Report.
From India, Kolkata
Acknowledge(1)Amend(0)
15Conditional Formatting Based on Column Values
Based on your query, I think you are trying to color the format of column C (from cell C1:C3) based on the following conditions:
1. If the cell value of Column A matches the cell value of Column B (here I have marked the cell in yellow color in the respective cell in column C).
2. If the cell value in Column A does not match with column B (here I have marked the cell in red color in the respective cell in column C).
3. Moreover, I have also considered that if there is no value in cell A and B column, i.e., a blank cell, then the difference will appear as blank in column C, and the respective cell will remain with no color.
Steps to Apply Conditional Formatting
To do the conditional format as mentioned above, please follow the steps below:
- Go to Home Menu → Select conditional format → Select "Use a formula to determine which cells to format."
- Put the format as per the following:
`=AND($A1=$B1,NOT(ISBLANK($A1)),NOT(ISBLANK($B1))) `
- Click the "Format" button and select the "Fill" color. Here I have selected "yellow" color. Press "Ok." In the "Applies to" area, put the formula as `=$D$1:$D$3`.
- Press New Rule. In the formula box, put the formula as: `=$A1<>$B1`. Select Format and repeat the above step. Here I have selected "red" color. Press "Ok." In the "Applies to" area, put the formula as `=$D$1:$D$3` and click on the "Apply" button.
Calculating the Difference in Column C
For calculating the difference of Column A & B, put the formula in Cell C1 as follows:
`=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1-B1)`
The above formula will calculate the difference only if there are no blank cells; otherwise, cell values in Column C will remain blank, and no format will be applied.
To understand the above, please find attached herewith the Excel file. For any queries, feel free to write me: [Email Removed For Privacy Reasons] or visit my blog site: Advanced Excel Tips - Manage your MIS Report.
From India, Kolkata
Acknowledge(1)Amend(0)
Subrata, you can try the following to format the column based on conditions.
Conditional Formatting Based on Column Values
Based on your query, I think you are trying to color the format of column C (from cell C1:C3) based on the following conditions:
1. If the cell value of Column A matches the cell value of Column B (here I have marked the cell in yellow color in the respective cell in column C).
2. If the cell value in Column A does not match with Column B (here I have marked the cell in red color in the respective cell in column C).
3. Moreover, I have also considered that if there is no value in cell A and B column, meaning a blank cell, then the difference will appear as blank in column C, and the respective cell will remain with no color.
Steps to Apply Conditional Formatting
To do the conditional format as mentioned above, please follow the steps below:
- Go to Home Menu → Select conditional format → Select "Use a formula to determine which cells to format." Put the format as per the following:
`=AND($A1=$B1, NOT(ISBLANK($A1)), NOT(ISBLANK($B1)))`
- Click the "Format" button and select the "Fill" color. Here I have selected "yellow" color. Press "Ok." In the applies to area, put the formula as `=$D$1:$D$3`.
- Press New Rule. In the formula area, put the formula as: `=$A1<>$B1`. Select Format and repeat the above step. Here I have selected "red" color. Press "OK." In the applies to area, put the formula as `=$D$1:$D$3` and click on the "Apply" button.
Calculating the Difference Between Columns A & B
For calculating the difference of Column A & B, put the formula in Cell C1 as follows:
`=IF(OR(ISBLANK(A1), ISBLANK(B1)), "", A1-B1)`
The above formula will calculate the difference only if there are no blank cells; otherwise, cell values in Column C will remain blank, and no format will be applied.
To understand the above, please find attached herewith the Excel file. For any queries, feel free to write to me or visit my blog site: Advanced Excel Tips - Manage your MIS Report.
Regards
From India, Kolkata
Acknowledge(1)Amend(0)
Conditional Formatting Based on Column Values
Based on your query, I think you are trying to color the format of column C (from cell C1:C3) based on the following conditions:
1. If the cell value of Column A matches the cell value of Column B (here I have marked the cell in yellow color in the respective cell in column C).
2. If the cell value in Column A does not match with Column B (here I have marked the cell in red color in the respective cell in column C).
3. Moreover, I have also considered that if there is no value in cell A and B column, meaning a blank cell, then the difference will appear as blank in column C, and the respective cell will remain with no color.
Steps to Apply Conditional Formatting
To do the conditional format as mentioned above, please follow the steps below:
- Go to Home Menu → Select conditional format → Select "Use a formula to determine which cells to format." Put the format as per the following:
`=AND($A1=$B1, NOT(ISBLANK($A1)), NOT(ISBLANK($B1)))`
- Click the "Format" button and select the "Fill" color. Here I have selected "yellow" color. Press "Ok." In the applies to area, put the formula as `=$D$1:$D$3`.
- Press New Rule. In the formula area, put the formula as: `=$A1<>$B1`. Select Format and repeat the above step. Here I have selected "red" color. Press "OK." In the applies to area, put the formula as `=$D$1:$D$3` and click on the "Apply" button.
Calculating the Difference Between Columns A & B
For calculating the difference of Column A & B, put the formula in Cell C1 as follows:
`=IF(OR(ISBLANK(A1), ISBLANK(B1)), "", A1-B1)`
The above formula will calculate the difference only if there are no blank cells; otherwise, cell values in Column C will remain blank, and no format will be applied.
To understand the above, please find attached herewith the Excel file. For any queries, feel free to write to me or visit my blog site: Advanced Excel Tips - Manage your MIS Report.
Regards
From India, Kolkata
Acknowledge(1)Amend(0)CiteHR is an AI-augmented HR knowledge and collaboration platform, enabling HR professionals to solve real-world challenges, validate decisions, and stay ahead through collective intelligence and machine-enhanced guidance. Join Our Platform.
Amazing excel sheets very very informative.zip
Advanced Excel 2003.pdf
Competancy--exc el---mts___smts _289.xls