Cell Ranges & References in Excel

Excel Tutorials for beginners, Intermediates and experts.

Check if multiple cells have same value in Excel

To confirm that a range of cells all have the same value, you can use a formula based on the COUNTIF function. Formula =COUNTIF(range,”<>value”)=0 Explanation In the example shown, the formula in C9 is: =COUNTIF(C5:C8,”<>ok”)=0 Note: this formula is not case-sensitive, you can find a case-sensitive formula here. How this formula works This formula relies on… read more »

How to count total number of cells in a rectangular range in Excel

If you need to count the total number of cells in a rectangular range, you can do so with the ROWS and COLUMNS functions. Formula =ROWS(range)*COLUMNS(range) Note that this formula only works for a single rectangular range of cells. Explanation In the example, the active cell contains this formula: =ROWS(B4:C9)*COLUMNS(B4:C9) How this formula works There… read more »

How to count total columns in range in Excel

If you need to count the number of columns in a range, use the COLUMNS function. COLUMNS counts the number of columns in and returns the result as a number. Formula =COLUMNS(range) Explanation In the example, the active cell contains this formula: =COLUMNS(B4:C9)

How to test a range for numbers in Excel

To test a range for numbers, you can use a formula based on the ISNUMBER and SUMPRODUCT functions. See example below: Formula =SUMPRODUCT(–ISNUMBER(range))>0 Explanation In the example shown, the formula in xxxx is: =SUMPRODUCT(–ISNUMBER(C5:C9))>0 How this formula works Working from the inside out, the ISNUMBER function will return TRUE when given a number and FALSE if… read more »

Check if multiple cells have same value with case sensitive in Excel

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 »

Check if range contains a value not in another range in Excel

To test if a range contains any values (i.e. at least one value) not in another range, you can use the SUMPRODUCT function with MATCH and ISNA. The MATCH function receives a single lookup value, and returns a single match if any. In this case, however, we are giving MATCH an array for lookup value,… read more »

How to add sequential row numbers to a set of data in Excel

To add sequential row numbers to a set of data with a formula, you can use the ROW function. See example below: Formula =ROW()-offset Explanation In the example shown, the formula in B5 is: =ROW()-4 How this formula works When not given a reference, the ROW function returns the row number of the current row…. read more »

To count total rows in a range in Excel

If you need to count the number of rows in a range, use the ROWS function. The ROWS counts the number of rows in the supplied range and returns that number as the result. See illustration below: Formula =ROWS(range) Explanation In the example, the active cell contains this formula: =ROWS(B4:C9)

Check multiple cells are equal in Excel

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 »

How to get last row in mixed data with blanks in Excel

To get the last relative position (i.e. last row, last column) for mixed data that may contain empty cells, you can use the MATCH function as described below. Note: this is an array formula and must be entered with Control+Shift+Enter. Formula {=MATCH(2,1/(range<>””))} Explanation In the example shown, the formula in E5 is: {=MATCH(2,1/(B4:B10<>””))} Last *relative*… read more »

Sidebar