Log In
Register
Home
December 8, 2017
Offline
| List 1 | List 2 |
| Highlight cells That meet certain criteria | Highlight 3 smallest values with criteria |
| Highlight cells based on the value of another cell | Conditional formatting dates overlap |
| Highlight values That Exist in List1 , but not List 2 | Conditional formatting gantt chart |
| Highlight values That Exist in List 1 and List 2 | Conditional formatting gantt chart weekends |
| Highlight Weekend dates | Conditional formatting highlight target percentage |
| Highlight Dates between two Dates | Find duplicate values in two columns |
| Highlight Dates on Due Dates | Highlight approximate match lookup conditional formatting |
| Highlight Data based on Percentile Rank | Highlight blank cells |
| Highlight statistical outliers | Highlight bottom values |
| Highlight cells that begin with | |
| Highlight cells that contain | |
| Highlight cells that contain one of many | |
| Highlight cells that end with | |
| Highlight cells that equal | |
| Highlight column differences | |
| Highlight data by quartile | |
| Highlight Dates between two Dates | |
| Highlight dates greater than | |
| Highlight dates in same month and year | |
| Highlight dates in Next N days | |
| Highlight Weekend Dates | |
| Highlight duplicate columns | |
| Highlight duplicate rows | |
| Highlight duplicate values |
VIP
Trusted Members
December 7, 2016
Offline
Hi Mitul,
I am confused. What is your question?
Br,
Anders
December 8, 2017
Offline
Hi; Anders;
Good Morning
This is Mitul.
How are you ?
Thank you for sending me correct GETPIVOTDATA function and article written by Mynda, which is very nice.
Now in Highlight conditional formatting that exist in list 1 and list 2 above I like to learn what conditional formula to write to highlight 2 items
Thank you very much.
Have a great day.
VIP
Trusted Members
December 7, 2016
Offline
Hello Mitul,
I hope you're good.
The easiest way to compare two columns and highlight the cells where you have same data is to select the range of data you want to compare, in Conditional Formatting choose Highlight Cells Rules and pick Duplicate Values.
If you want to use a formula, use COUNTIF.
Let's say you have your data which you want to compare in columns A2:A11 and C2:C11, select the range of data in column A and go to Conditional Formatting --> New Rule --> Use a formula to determine which cells to format. Type in following formula:
=COUNTIF($C$2:$C$11,$A2)>0
Add the needed format to how the cell/-s are to be highlighted and save. Do the same for the next column, select the range of data in column C and create a new rule, now the formula is:
=COUNTIF($A$2:$AC$11,$C2)>0
If you want to highlight those cells that have individual values that do not exist in the other column, just change the greater than symbol (>) to equal symbol (=) and you are good to go. Below picture shows both of those Conditional Formattings. In column A I highlight in red those cells which have individual values, no duplicates exists in column C. In column C I highlight in green those cells where we have duplicates in column A.
But the easiest way is to use Duplicate Values formatting rule, you can after all change so it also highlights the individual cells, where there are no duplicate values.
I hope this answers your question.
Br,
Anders
1 Guest(s)
