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

Data Analysis

  • How to Create Thermometer Chart in Excel
  • Randomize/ Shuffle List in Excel
  • Managing Conditional Formatting Rules in Excel
  • How To Insert and Customize Sparklines in Excel
  • Example of COUNTIFS with variable table column in Excel

References

  • Find closest match in Excel
  • How to use Excel LOOKUP Function
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Lookup entire row in Excel
  • How to use Excel MATCH Function

Data Validations

  • Excel Data validation don’t exceed total
  • Excel Data validation with conditional list
  • Excel Data validation specific characters only
  • Excel Data validation allow weekday only
  • Excel Data validation must contain specific text

Tag: IFERROR function

IFERROR function: Description, Usage, Syntax, Examples and Explanation

by

What is IFERROR function in Excel? IFERROR function is one of the Logical functions in Microsoft Excel that returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IFERROR function to trap and handle errors in a formula. Syntax of IFERROR function IFERROR(value, value_if_error) The …

Continue Reading

How to calculate next scheduled event in Excel

by

To get the next scheduled event from a list of events with dates, you can use an array formula based on the MIN and TODAY functions to find the next date, and INDEX and MATCH  to display the event on that date. Formula {=MIN(IF((range>=TODAY()),range))} Note: this is an array formula and must be entered with Control + …

Continue Reading

Create dynamic workbook reference to another workbook in Excel

by

To build a dynamic worksheet reference – a reference to another workbook that is created with a formula based on variables that may change – you can use a formula based on the INDIRECT function. See example below: Formula =INDIRECT(“‘[“&workbook&”]”&sheet&”‘!”&ref) Explanation In the example shown, the formula in E6 is: =INDIRECT(“‘[“&B6&”]”&C6&”‘!”&D6) How this formula works …

Continue Reading

How to Sum Range with Errors in Excel

by

This example shows you how to create an array formula that sums a range with errors. You can also use the AGGREGATE function to sum a range with errors. 1. We use the IFERROR function to check for an error. Explanation: the IFERROR function returns 0, if an error is found. If not, it returns the value of the cell. …

Continue Reading

Extract multiple matches into separate columns in Excel

by

This tutorial shows how to Extract multiple matches into separate columns in Excel using the example below; Explanation To extract multiple matches to separate cells, in separate columns, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in F5 is: {=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($E$5:E5))),””)} This is an array formula and …

Continue Reading

Extract multiple matches into separate rows in Excel

by

This tutorial shows how to calculate Extract multiple matches into separate rows in Excel using the example below; Explanation To extract multiple matches to separate cells, in separate rows, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in E5 is: {=IFERROR(INDEX(names,SMALL (IF(groups=E$4,ROW(names)-MIN(ROW (names))+1),ROWS($E$5:E5))),””)} This is an …

Continue Reading

How to return blank in place of #DIV/0! error in Excel

by

This example illustrates the IFERROR function and how to return blank in place of #DIV/0! error in Excel. 1. For example, Excel displays the #DIV/0! error when a formula tries to divide a number by 0. 2. Use the IFERROR function. If a cell contains an error, an empty string (“”) is displayed.

Continue Reading

Sum top n values with criteria in Excel

by

This tutorial shows how to Sum top n values with criteria in Excel using the example below; Formula =SUMPRODUCT(LARGE((range=criteria)*(values),{1,2,3,N})) Explanation To sum the top n values in a range matching criteria, you can use a formula based on the LARGE function, wrapped inside the SUMPRODUCT function. In the generic form of the formula (above), range represents a range of …

Continue Reading

nth smallest value with criteria in Excel

by

This tutorial shows how to calculate nth smallest value with criteria in Excel using the example below; To get the 2nd smallest value, 3rd smallest value, 4th smallest value, and so on, where each value matches supplied criteria, you can use an array formula that uses the SMALL and IF functions. Formula {=SMALL(IF(criteria,values),n)} Explanation In …

Continue Reading

Multiple chained VLOOKUPs in Excel

by

This tutorial shows how to  work  Multiple chained VLOOKUPs in Excel using the example below; Formula =IFERROR(VLOOKUP 1,IFERROR(VLOOKUP 2,VLOOKUP 3)) Explanation If you need to perform multiple lookups sequentially, based on whether the earlier lookups succeed or not, you can chain one or more VLOOKUPs together with IFERROR. In the example shown, the formula in L5 …

Continue Reading

VLOOKUP without #N/A error in Excel

by

This tutorial shows how to calculate VLOOKUP without #N/A error in Excel using the example below; Formula =IFERROR(VLOOKUP(value,table,2,FALSE),””) Explanation To hide the #N/A error that VLOOKUP throws when it can’t find a value, you can use the IFERROR function to catch the error and return any value you like. How the formula works When VLOOKUP …

Continue Reading

Basic error trapping example in Excel

by

To catch errors that a formula might trigger in a worksheet, you can use the IFERROR function to display a custom message, or nothing at all. See example below: Formula =IFERROR(formula,value_if_error) Explanation In the example shown, the formula in E5 is: =IFERROR(C5/D5,””) How this formula works In this example, the IFERROR function is used to …

Continue Reading

How to get first word in Excel

by

If you need to extract the first word from some text you can use a formula that uses the FIND and LEFT functions. Formula =LEFT(a1,FIND(” “,a1)-1) Explanation From the example, the formula looks like this: =LEFT(B4,FIND(” “,B4)-1) How this formula works FIND returns the position (as a number) of the first occurrence of a space character in …

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

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

  • IF with wildcards in Excel
  • Excel If, Nested If, And/Or Criteria Examples
  • How to use Excel AND Function
  • How to use Excel TRUE Function
  • Extract multiple matches into separate rows in Excel

Date Time

  • Calculate expiration date in Excel
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Display Date is workday in Excel
  • Series of dates by day
  • Calculate number of hours between two times in Excel

Grouping

  • How to randomly assign people to groups in Excel
  • Categorize text with keywords in Excel
  • If cell contains one of many things in Excel
  • Group times into 3 hour buckets in Excel
  • Group numbers at uneven intervals in Excel

General

  • How to calculate percent sold in Excel
  • Freeze and Unfreeze Panes in Excel
  • List worksheet index numbers in Excel
  • Flash Fill in Excel
  • Check if multiple cells have same value in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning