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

Data Analysis

  • How to create a Histogram in Excel
  • Excel Pie Chart
  • How To Filter Data in Excel
  • Subtotal function in Excel
  • How to count table rows in Excel

References

  • How to get first row number in range in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • How to get relative column numbers in a range in Excel
  • How to use Excel LOOKUP Function
  • How to get relative row numbers in a range in Excel

Data Validations

  • Excel Data validation with conditional list
  • Excel Data validation whole percentage only
  • Excel Data validation must not contain
  • Excel Data validation specific characters only
  • Excel Data validation only dates between

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

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

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

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

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

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

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

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

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

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

  • How to use Excel TRUE Function
  • Invoice status with nested if in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • Not Equal To ‘<>‘ operator in Excel
  • How to return blank in place of #DIV/0! error in Excel

Date Time

  • Convert Excel time to decimal seconds
  • How to calculate project start date based on end date in Excel
  • Get days between dates in Excel
  • Convert Excel time to Unix time in Excel
  • Get project end date in Excel

Grouping

  • Map text to numbers in Excel
  • Group numbers with VLOOKUP in Excel
  • Running count group by n size in Excel
  • Group arbitrary text values in Excel
  • Group numbers at uneven intervals in Excel

General

  • How to calculate percent of students absent in Excel
  • How to calculate total from percentage in Excel
  • Currency vs Accounting Format in Excel
  • How to calculate decrease by percentage in Excel
  • Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning