As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.
Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How do I highlight a cell in Excel where the value is different to the cell five cells below it - and apply this to a whole row?
Posted on 2016-09-15
I've been trying to get to grips with conditional formatting but I've not managed to get Excel to do what I want.
I have a row of data - row 2 - and I want to compare each value in row 2 with the data in the same column in in row 7.
If the data is different I want the cell to be highlighted in a colour.
I'm on Excel 2016.
I have a row of data - row 2 - and I want to compare each value in row 2 with the data in the same column in in row 7.
If the data is different I want the cell to be highlighted in a colour.
I'm on Excel 2016.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
7
4
11 Comments
Active today
Select the rows to which you want to apply conditional formatting.
Create a new conditional rule using the "Use a formula to determine cells to format" option.
In the "Format Values where this formula is true:", you can use a formula like:
=OR($A2<>$A7, $B2<>$B7....)
The comparison should be repeated for each column you want to compare.
Another option is to use:
= CONCATENATE($A2:$I2)<>CONC
($I should be your end column letter.)
Create a new conditional rule using the "Use a formula to determine cells to format" option.
In the "Format Values where this formula is true:", you can use a formula like:
=OR($A2<>$A7, $B2<>$B7....)
The comparison should be repeated for each column you want to compare.
Another option is to use:
= CONCATENATE($A2:$I2)<>CONC
($I should be your end column letter.)
Active 1 day ago
I tried the first option - which obviously takes along time to type in all the columns - I have about 100 - but it just highlights all the cells of both rows, even though I can see only about 3-4 are different.
![[Live Webinar] The Cloud Skills Gap](https://filedb.experts-exchange.com/files/public/2017/5/29/3249c5dc-214e-4f8f-afab-ef354630e19f.png)
Active 1 day ago
What it is doing is highlighting the whole row if any values are different instead of just highlighting the cells that are different.
Active today
Thought you wanted to know which rows had different values. My mistake. To highlight just the cells with differences (cells in row 2 that are different than a cell in the same column 5 rows below it), use:
=AND($A7<>"", A2<>A7)
This formula assumes that column A will always have a value ($A7 <> ""), and prevents comparing a row against one that is completely blank. If that is not the case, and you do not have a column that will always have a value, let me know and I can provide a different formula.
=AND($A7<>"", A2<>A7)
This formula assumes that column A will always have a value ($A7 <> ""), and prevents comparing a row against one that is completely blank. If that is not the case, and you do not have a column that will always have a value, let me know and I can provide a different formula.
Active 1 day ago
I may be missing something here. I highlighted A2 and A7 and entered the formula above - the values were different and it correctly highlighted both cells.
I then highlighted B2, C2 and D2, and B7, C7 and D7 and entered the following (none of them had blank values):
=AND($B2<>$B7, $C2<>$C7,$D2<>$D7)
Although all the values matched, it highlighted B2, C2 and D2.
I then highlighted B2 and B7 - as I said the values match - and entered this formula.
=AND($B7<>"", B2<>B7)
This worked - there is no highlighting.
I then highlighted C2, D2 and E2 and C7, D7 and E7 and entered the following formula:
=AND($C7<>"", C2<>C7,$D7<>"",D2<>D7,$E7<
Again, this highlighted C2, D2 and E2 even though the values matched.
So while I can see that this formula works if I apply it one cell at a time, I can't go through 100 of cells in a row and apply the formatting individually to each one. I need something which just says compare these two rows and highlight the cells that are different. Is this possible?
I then highlighted B2, C2 and D2, and B7, C7 and D7 and entered the following (none of them had blank values):
=AND($B2<>$B7, $C2<>$C7,$D2<>$D7)
Although all the values matched, it highlighted B2, C2 and D2.
I then highlighted B2 and B7 - as I said the values match - and entered this formula.
=AND($B7<>"", B2<>B7)
This worked - there is no highlighting.
I then highlighted C2, D2 and E2 and C7, D7 and E7 and entered the following formula:
=AND($C7<>"", C2<>C7,$D7<>"",D2<>D7,$E7<
Again, this highlighted C2, D2 and E2 even though the values matched.
So while I can see that this formula works if I apply it one cell at a time, I can't go through 100 of cells in a row and apply the formatting individually to each one. I need something which just says compare these two rows and highlight the cells that are different. Is this possible?
Active today
I'm listing the steps you need to follow to make this work. I believe you are missing the first step.
1.) Highlighting the entire area on your spreadsheet where you want to compare cells. If you are doing columns A to H and rows 2 to 20, then select the area from A2 to H20.
2.) Then select Conditional Formatting and then New Rule.
3.) Select "Use a formula to determine which cells to format".
4.) Enter the formula you want to use. In this case, try:
=AND($A7<>"", A2<>A7)
5.) Select your formatting.
6.) Click OK to exit the dialog box.
The use of the $ in the formula anchors that portion of the formula to column A, while the rest of the formula is not anchored. So if there is a difference between cells B2 & B7, the conditional formatting rule treats the formula as if it was =AND($A7<>"", B2<>B7).
Oh, and you are not being rude. Conditional formatting beyond some of the simple stuff can be very confusing until you work through it. Sometimes it helps to break out what you are attempting into small steps, and adding on more complex parts until you get the desired result.
1.) Highlighting the entire area on your spreadsheet where you want to compare cells. If you are doing columns A to H and rows 2 to 20, then select the area from A2 to H20.
2.) Then select Conditional Formatting and then New Rule.
3.) Select "Use a formula to determine which cells to format".
4.) Enter the formula you want to use. In this case, try:
=AND($A7<>"", A2<>A7)
5.) Select your formatting.
6.) Click OK to exit the dialog box.
The use of the $ in the formula anchors that portion of the formula to column A, while the rest of the formula is not anchored. So if there is a difference between cells B2 & B7, the conditional formatting rule treats the formula as if it was =AND($A7<>"", B2<>B7).
Oh, and you are not being rude. Conditional formatting beyond some of the simple stuff can be very confusing until you work through it. Sometimes it helps to break out what you are attempting into small steps, and adding on more complex parts until you get the desired result.
Active 1 day ago
EXCELLENT!
Thank you for your patience - I've attached screenshots to show it working.
Thanks!
Thank you for your patience - I've attached screenshots to show it working.
Thanks!
Featured Post
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11).
The larger templa…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month5 days, 12 hours left to enroll
627 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.