Data validation rules are triggered when a user adds or changes a cell value.
The UPPER function changes text values to uppercase, and the EXACT function performs a case-sensitive comparison.
The AND function takes...

This formula uses the EXACT formula to compare a range of cells to a single value:
=EXACT(B5:F5,B5)
Because we give EXACT a range of values in the first argument, we get back an array result containing TRUE FALSE...

By default, Excel is not case-sensitive. For example, with "APPLE" in A1, and "apple" in A2, the following formula will return TRUE:
=A1=A2 // returns TRUE
To compare text strings in a case-sensitive way, you can...

Data validation rules are triggered when a user adds or changes a cell value.
In this formula, the LEFT function is used to extract the first 3 characters of the input in C5.
Next, the EXACT function is used to...

The EXACT function takes two arguments, text1 and text2. When text1 and text2 match exactly (respecting case), EXACT returns TRUE. Otherwise, EXACT returns FALSE:
=EXACT("ayako","ayako") //...

Since MATCH alone isn't case sensitive, we need a way to get Excel to compare case. The EXACT function is the perfect function for this, but the way we use it is a little unusual, because we need to compare one cell to...

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each of the 20...

At the core, this is an INDEX and MATCH formula, with the EXACT function used inside MATCH to perform a case-sensitive match.
Working from the inside-out, EXACT is configured to compare the value in E5 against names...

The AND function is designed to evaluate multiple logical expressions, and returns TRUE only when all expressions are TRUE.
In this case the we simply compare one range with another with a single logical expression...

By itself, the XLOOKUP function is not case-sensitive. A lookup value of "RED" will match "red", "RED", or "Red". We can work around this limitation by constructing a suitable lookup array for XLOOKUP with a logical...

SUMPRODUCT is designed to work with arrays, which it multiplies, then sums.
In this case, we are two arrays with SUMPRODUCT: B3:B8 and C3:C8. The trick is to run a test on the values in column B, then convert the...

The MATCH function has a limit of 255 characters for the lookup value. If you try to use longer text, MATCH will return a #VALUE error.
To workaround this limit you can use boolean logic and the LEFT, MID, and EXACT...

Note: Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells that equal a specific value. However, for more flexibility, you can use your own...

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each cell in B4:G12...