Welcome to Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Excel Office

Excel Data Analysis is a powerful tool that is used to visualize and gain insights of records in a spreadsheet.

Get column index in Excel Table

To get the index of a column in an Excel Table, you can use the MATCH function. See example below: Formula =MATCH(name,Table[#Headers],0) Explanation In the example shown, the formula in I4 is: =MATCH(H4,Table1[#Headers],0) When the formula is copied down, it returns an index for each column listed in column H. Getting an index like this… read more »

How to calculate average last N values in a table in Excel

To calculate the average for the last N values n an Excel table (i.e. last 3 rows, last 5 rows, etc.) you can use the AVERAGE function together with the INDEX and ROWS functions. See example below: Formula =AVERAGE(INDEX(table[column],ROWS(table)-(N-1)):INDEX(table[column],ROWS(table))) Explanation In the example shown, the formula in F5 is: =AVERAGE(INDEX(Table1[Sales],ROWS(Table1)-(F4-1)):INDEX(Table1[Sales],ROWS(Table1))) How this formula works This… read more »

Get column name from index in Excel Table

To get the name of a column in an Excel Table from its numeric index, you can use the INDEX function with a structured reference. See example below: Formula =INDEX(Table[#Headers],index) Explanation In the example shown, the formula in I4 is: =INDEX(Table1[#Headers],H5) When the formula is copied down, it returns an name for each column, based… read more »

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 »

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 »

Sidebar