Excel OfficeExcel Tutorials for beginners, Intermediates and experts.
To verify that multiple cells have the same value with a case-sensitive formula, you can use a simple array formula based on the EXACT and AND functions. See example below: Formula {=AND(EXACT(range,value))} Explanation In the example shown, the formula in G5 is: =AND(EXACT(B5:F5,B5)) This is an array formula and must be entered with control +… read more »
To confirm two ranges of the same size contain the same values, you can use a simple array formula based on the AND function. See illustration below: Formula {=AND(range1=range2)} Explanation In the example shown, the formula in C9 is: {=AND(B5:D12=F5:H12)} Note: this is an array formula and must be entered with control + shift + enter. How… read more »
If you need to compare two text strings in Excel to determine if they’re equal, you can use the EXACT function. Formula =EXACT(text1, text2) Explanation For example, if you want to compare A2 with B2, use: =EXACT(A2,B2) If the two strings are identical, EXACT will return TRUE. If not, EXACT will return FALSE. You can… read more »
Using the example below, this tutorial shows how to create Data validation must begin with in Excel. Formula =EXACT(LEFT(A1,3),”XX-“) Explanation To allow only values that begin with certain text, you can use data validation with a custom formula based on the EXACT and LEFT functions. In the example shown, the data validation applied to C5:C9 is: =EXACT(LEFT(C5,3),”MX-“)… read more »
Using the example below, this tutorial shows how to create Excel Data validation to allow uppercase only. Formula =AND(EXACT(A1,UPPER(A1)),ISTEXT(A1)) Explanation To allow a user to enter only uppercase TEXT, you can use data validation with a custom formula based on the UPPER, EXACT, and AND functions. In the example shown, the data validation applied to C5:C7 is:… read more »
This tutorial shows how to Highlight column differences in Excel using the example below; Formula =$A1<>$B1 Explanation If you want to highlight the differences between two columns of data with conditional formatting you can do so with a simple formula that uses the” not equal to” operator (e.g. <>) and mixed references. For example, if you… read more »
This tutorial shows how to Highlight cells that end with in Excel using the example below; Formula =COUNTIF(A1,”*text”) Explanation Note: Excel contains many built-in rules for highlighting values with conditional formatting, including a rule to highlight cells that end with specific text. However, if you want more flexibility, you can use your own formula, as explained… read more »
Case-sensitive and case-insensitive ways to Compare Text in Excel. This example shows two ways to compare text in Excel. One is case-sensitive and one is case-insensitive. 1. Use the EXACT function (case-sensitive). 2. Use the formula =A1=B1 (case-insensitive).
By default, the VLOOKUP function performs a case-insensitive lookup. However, you can use the INDEX, MATCH and the EXACT function in Excel to perform a case-sensitive lookup. 1. For example, the simple VLOOKUP function below returns the salary of Mia Clark. However, we want to lookup the salary of MIA Reed (see cell G2). 2. The EXACT function in Excel… read more »