How to change color if two cells are not equal in Excel?
There are two lists in a sheet which you need to compare and then change the row color if the two adjacent cells are not equal as below screenshot shown, have you any good methods to handle this job in Excel?
1. Select two list you compare if equal to each other, and click Home > Conditional Formatting > New Rule.
2. In the New Formatting Rule dialog, choose Use a formula to determine which cells to format, and type this formula =$A1<>$B1 into the Format values where this formula true. Then click Format to open Format Cells dialog, and select one color under Fill tab. See screenshot:
3. Click OK > OK. Then the cells will be colored if the two cells are not equal to each other.
If you want to select and color cells if not equal to each other, you can apply Kutools for Excel’s Select Same & Different Cells utility.
|Kutools for Excel, with more than 300 handy functions, makes your jobs more easier.|
After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)
1. Select the two lists separately by holding Ctrl key. See screenshot:
2. Click Kutools > Select > Select Same & Different Cells, in the popping dialog, check Each row and Different cells options, and check Fill backcolor or Fill font color options as you need, select one color you want. See screenshot:
3. Click Ok, a dialog pops out to remind you how many different cells have been selected and highlighted. Click OK to close the reminder dialog. See screenshot:
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 7 months agoFrom the instructions the first method will not work. The formula doesn't auto-increment. If it does work I can't seem to figure it out with the steps provided. Thank you.
- To post as a guest, your comment is unpublished.· 1 years agoHello,First time, I leave my comments here. Can I ask how the Excel file will be showed on other's laptop if I use the products on my laptop with Excel installed Kutools and then send to them?Thank youK
- To post as a guest, your comment is unpublished.· 1 years agoHi, Khanh Mai, if your Excel file has been edited by Kutools for Excel, and when you sent the file to others, the file shown as what it shown in your laptop.
- To post as a guest, your comment is unpublished.· 1 years agowhy this formula not working in my excel sheet (=$A1<>$B1)
- To post as a guest, your comment is unpublished.· 1 years agoHi, could you check the formula you type is correct？ Maybe there are some spaces？
- To post as a guest, your comment is unpublished.· 2 years agoThank you!
Don't forget, if you want to drag your settings to other cells and have them
compare line by line by themselves, you need to remove the $ signs in the formula.
- To post as a guest, your comment is unpublished.· 3 years agoTe quiero tío.