Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to count table rows in Excel
  • How to Create Gantt Chart in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • How To Create Pareto Chart in Excel
  • Reverse List in Excel

References

  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to get address of named range in Excel
  • How to get address of first cell in range in Excel
  • Count rows that contain specific values in Excel
  • How to use Excel COLUMN Function

Data Validations

  • Excel Data validation unique values only
  • Excel Data validation only dates between
  • Excel Data validation no punctuation
  • Excel Data validation number multiple 100
  • Data validation must not exist in list

Tag: ROW function

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

by

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. …

Continue Reading

How to get address of last cell in range in Excel

by

To get the address of the first cell in a named range, you can use the ADDRESS function together with ROW and COLUMN functions. Formula =ADDRESS(ROW(range)+ROWS(range)-1,COLUMN(range)+COLUMNS(range)-1) Explanation In the example shown, the formula in G6 is: =ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data)+COLUMNS(data)-1) How this formula works The ADDRESS function builds an address based on a row and column number. Working …

Continue Reading

How to get address of first cell in range in Excel

by

To get the address of the first cell in a named range, you can use the ADDRESS function together with the ROW and COLUMN functions. See example below; Note: The ADDRESS function builds an address based on a row and column number. Formula =ADDRESS(ROW(range),COLUMN(range)) Explanation In the example shown, the formula in G5 is: =ADDRESS(ROW(data),COLUMN(data)) …

Continue Reading

How to get first row number in range in Excel

by

You can get the first row (i.e. the starting row number) in a range with a formula based on the ROW function. Formula =MIN(ROW(range)) Explanation In the example shown, the formula in cell F5 is: =MIN(ROW(data)) where data is a named range for B5:D10 How this formula works When given a single cell reference, the …

Continue Reading

How to get address of named range in Excel

by

To get the full address of a named range with an Excel formula, you can use the ADDRESS function together with the ROW and COLUMN functions. Formula =ADDRESS(ROW(nr),COLUMN(nr))&”: “&ADDRESS(ROW(nr)+ROWS(nr)-1, COLUMN(nr)+COLUMNS(nr)-1) Explanation In the example shown, the formula in G5 is: =ADDRESS(ROW(data),COLUMN(data),4) &”:”&ADDRESS(ROW(data)+ROWS (data)-1,COLUMN(data)+COLUMNS(data)-1,4) where “data” is the named range B5:D10 How this formula works The core of …

Continue Reading

How to get relative row numbers in a range in Excel

by

To get a full set of relative row numbers in a range, you can use an array formula based on the ROW function. See example below; Formula {=ROW(range)-ROW(range.firstcell)+1} Note: this is an array formula that must be entered with Control + Shift + Enter. If you’re entering this on the worksheet (and not inside another formula), make …

Continue Reading

Last row number in range

by

You can get the last row in a range with a formula based on the ROW function. See example below; Formula =MIN(ROW(range))+ROWS(range)-1 Explanation In the example shown, the formula in cell F5 is: =MIN(ROW(data))+ROWS(data)-1 where data is a named range for B5:D10 How this formula works When given a single cell reference, the ROW function …

Continue Reading

How to strip numeric characters from cell in Excel

by

To remove numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, new in Excel 2016. Formula {=TEXTJOIN(“”,TRUE,IF(ISERR(MID(A1, ROW(INDIRECT(“1:100”)),1)+0), MID(A1,ROW(INDIRECT(“1:100″)),1),””))} Explanation In the example shown, the formula in C5 is: =TEXTJOIN(“”,TRUE,IF(ISERR(MID(B5, ROW(INDIRECT(“1:100”)),1)+0), MID(B5,ROW(INDIRECT(“1:100″)),1),””)) Note: this is an array formula and must be entered with control + shift + …

Continue Reading

How to strip non-numeric characters in Excel

by

To remove non-numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, new in Excel 2016. Formula {=TEXTJOIN(“”,TRUE,IFERROR(MID(A1,ROW (INDIRECT(“1:100″)),1)+0,””))} Note: TEXTJOIN will return the numbers as text, for example “100,”500″, etc. If you want a true numeric result, add zero, or wrap the entire formula in the VALUE function. TEXTJOIN was added …

Continue Reading

List sheet names with formula in Excel

by

To list worksheets in an Excel workbook, you can use a 2-step approach: (1) define a named range called “sheetnames” with an old macro command and (2) use an INDEX formula to retrieve sheet names using the named range. Formula =GET.WORKBOOK(1)&T(NOW()) Note: because this formula relies on a macro command, you’ll need to save as a macro-enabled workbook …

Continue Reading

Count day of week between dates in Excel

by

This tutorial shows how to Count day of week between dates in Excel using example below. To count the number of Mondays, Fridays, Sundays, etc. between two dates you can use an array formula that uses several functions: SUMPRODUCT, WEEKDAY, ROW, and INDIRECT.  Formula =SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(date1&”:”&date2)))=dow)) Explanation In the example shown, the formula in cell E6 …

Continue Reading

Get work hours between dates custom schedule in Excel

by

To calculate work hours between two dates with a custom schedule, you can use a formula based on the WEEKDAY and SUMPRODUCT functions, with help from ROW, INDIRECT, and MID. Formula =SUMPRODUCT(MID(schedule,WEEKDAY (ROW(INDIRECT(start&”:”&end))),1)*ISNA (MATCH(ROW(INDIRECT(start&”:”&end)),holidays,0))) Explanation In the example shown, the formula in F8 is: =SUMPRODUCT(MID(D6,WEEKDAY(ROW (INDIRECT(B6&”:”&C6))),1)*ISNA(MATCH (ROW(INDIRECT(B6&”:”&C6)),holidays,0))) Which returns 36 hours, based on a custom …

Continue Reading

How to convert text string to array in Excel

by

To convert a string to an array that contains one item for each letter, you can use an array formula based on the MID, ROW, LEN and INDIRECT functions. This can sometimes be useful inside other formulas that manipulate text at the character level.  Formula {=MID(string,ROW(INDIRECT(“1:”&LEN(string))),1)} Note: this is an array formula and must be …

Continue Reading

Excel Data validation specific characters only

by

Set criteria to accept specific characters only To use data validation to allow a list of specific characters only, you can use a rather complicated array formula based on the COUNT, MATCH, and LEN functions. Formula =COUNT(MATCH(MID(A1,ROW(INDIRECT (“1:”&LEN(A1))),1),allowed&””,0))=LEN(A1) Explanation In the example shown, data validation is applied with this formula: =COUNT(MATCH(MID(B5,ROW(INDIRECT (“1:”&LEN(B5))),1),allowed&””,0))=LEN(B5) where “allowed” is …

Continue Reading

Highlight every other row in Excel

by

This tutorial shows how to Highlight every other row in Excel using the example below; Formula =ISEVEN(ROW()) Explanation If you want to highlight every other row in a table (sometimes called zebra striping) with conditional formatting you can do so with a simple formula that uses either the ISEVEN or ISODD function. For example, if …

Continue Reading

Shade alternating groups of n rows in Excel

by

This tutorial shows how to Shade alternating groups of n rows in Excel using the example below; Formula =ISEVEN(CEILING(ROW()-offset,n)/n) Explanation To highlight rows in groups of “n” (i.e. shade every 3 rows, every 5 rows, etc.) you can apply conditional formatting with a formula based on the ROW, CEILING and ISEVEN functions. In the example …

Continue Reading

Get nth match with INDEX / MATCH in Excel

by

This tutorial shows how to Get nth match with INDEX / MATCH in Excel using the example below; Formula {=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth))} Explanation To retrieve multiple matching values from a set of data with a formula, you can use the IF and SMALL functions to figure out the row number of each match and feed that value back to …

Continue Reading

Extract all partial matches in Excel

by

This tutorial shows how to Extract all partial matches in Excel using the example below; Formula =IF(F5>ct,””,INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5))) Explanation To extract all matches based on a partial match, you can use use an array formula based on the INDEX and AGGREGATE functions, with support from ISNUMBER and SEARCH. In the example shown, the formula in G5 …

Continue Reading

Get last match in Excel

by

This tutorial shows how to Get last match in Excel using the example below; Formula {=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1))} Explanation To get the position of the last match (i.e. last occurrence) of a lookup value, you can use an array formula based on the IF, ROW, INDEX, MATCH, and MAX functions. In the example shown, the formula in …

Continue Reading

Get location of value in 2D array in Excel

by

This tutorial shows how to Get location of value in 2D array in Excel using the example below; Formula =SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 Explanation To locate the position of a value in a 2D array, you can use the SUMPRODUCT function. In the example shown, the formulas used to locate the row and column numbers of the max value …

Continue Reading

Get nth match in Excel

by

This tutorial shows how to Get nth match in Excel  using the example below; Formula =SMALL(IF(logical,ROW(list)-MIN(ROW(list))+1),n) Explanation To get the position of the nth match (for example, the 2nd matching value, the 3rd matching value, etc.), you can use a formula based on the SMALL function. In the example shown, the formula in G5 is: …

Continue Reading

Count unique text values in a range in Excel

by

This tutorial shows how to Count unique text values in a range in Excel using the example below; Formula =SUMPRODUCT(–(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0)) Explanation If you need to count unique text values in a range, you can use a formula that uses several functions: FREQUENCY , MATCH, ROW and SUMPRODUCT. It’s also possible to use COUNTIF, as explained …

Continue Reading

Count unique text values with criteria

by

This tutorial shows how to Count unique text values with criteria using the example below; Formula {=SUM(–(FREQUENCY(IF(criteria,MATCH(values,values,0)),ROW(values)-ROW(valuesfirstcell)+1)>0))} Explanation To count unique values in a range with a criteria, you can use an array formula based on the FREQUENCY function. Assume you have a list of employee names together with hours worked on “Project X”, and you want …

Continue Reading

Sum bottom n values in Excel

by

This tutorial shows how to Sum bottom n values in Excel. You can use a combination of SUMPRODUCT function and SMALL function to get the sum bottom  n values in the example below; Formula =SUMPRODUCT(SMALL(range,{1,2,n})) Explanation If you need to sum or add the bottom values in a range, you can do so with a formula that uses the …

Continue Reading

Average the last 3 numeric values in Excel

by

This tutorial shows how to work  Average the last 3 numeric values in Excel using the example below; Formula {=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),{1,2,3}),ROW(data), data))} Explanation To average the last 3 numeric values in a range, you can use an array formula based on a combination of functions to feed the last n numeric values into the AVERAGE function. In the example …

Continue Reading

How to use Excel ROW Function

by

This Excel tutorial explains how to use the ROW function with syntax and examples. Excel ROW Function Description Microsoft Excel ROW function returns the row number of a cell reference. The ROW function is a built-in function in Excel that is categorized as a Lookup/Reference Function. The ROW function can be entered as part of a formula …

Continue Reading

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Invoice status with nested if in Excel
  • How to use Excel NOT Function
  • IF, AND, OR and NOT Functions Examples in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • Excel If, Nested If, And/Or Criteria Examples

Date Time

  • How to calculate next anniversary date or birthday in Excel
  • Get age from birthday in Excel
  • How to get Weekdays, Working days between Two Dates in Excel
  • Add decimal minutes to time in Excel
  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • How to randomly assign data to groups in Excel
  • Group numbers with VLOOKUP in Excel
  • If cell contains one of many things in Excel
  • Group times into unequal buckets in Excel
  • Map text to numbers in Excel

General

  • How to increase by percentage in Excel
  • Index and match on multiple columns in Excel
  • How to calculate project complete percentage in Excel
  • How to calculate percentage of total in Excel
  • How to set or clear a print area in Excel Worksheet
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning