![]() Because this formula is evaluated by a conditional format rule, instead, when the formula is checked to see if it applies, the C1 effectively refers to whichever row is currently being evaluated to see if the highlight should be applied. Highlight duplicates (in column C): =COUNTIF(C:C, C1) > 1Įxplanation: The C1 here doesn't refer to the first row in C. So to indicate a duplicate, we need to find 2 or more cells matching ours. That's our cell, which is in the range, and thus will be compared to itself. The > 1 is used because we know there's at least one cell identical to ours. The last step is making our formula return a boolean, by making it a logical expression: COUNTIF(.) > 1. Then back to the start, COUNTIF() will test every cell in the range against ours, and return the count. ![]() In this case, the current cell's content. INDIRECT(), will take a cell reference and return its content. The third parameter takes care for the formatting, and 4 returns the formatting INDIRECT() likes. Remember, while we are inside the cell's context, we don't know it's address OR content, and we need the content in order to compare with). Then we use: ADDRESS(row, column, ) to translate the numeric row and column to a cell reference (like B3. If no parameter is provided, the current cell will be returned (this is 1-based, for example, B3 will return 3 for ROW(), and 2 for COLUMN()). ROW() and COLUMN() will return the row number and column number of the given cell respectively. If this was placed inside the cell, docs will have cried about circular dependency, but in this case, the formula is evaluated as if it was in the cell, without changing it. The second block, INDIRECT(ADDRESS(ROW(), COLUMN(), 4)), will return current cell's content. We are using a fixed range (with $ signs) so that we always view the full range. If no special operators are provided, it will compare every cell in the range with the given cell, and return the number of cells found to be matching the rule (in this case, the comparison). In the textbox insert the given formula, adjusting the range to match step (3).ĬOUNTIF(range, criterion), will compare every cell in range to the criterion, which is processed similarly to formulas.In Format cells if, select Custom formula is on the dropdown.Under Apply to range, select the range to which the rule should be applied.On the menu: Format > Conditional formatting.Select the whole range on which the duplicate marking is wanted. ![]() The first parameter ( $A$1:$C$50) should be replaced with the range on which you would like to highlight duplicates! Please note that in this example I will be using the range A1:C50. While zolley's answer is perfectly right for the question, here's a more general solution for any range, plus explanation: =COUNTIF($A$1:$C$50, INDIRECT(ADDRESS(ROW(), COLUMN(), 4))) > 1 ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |