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

Data Analysis

  • How to do a t-Test in Excel?
  • How To Insert and Customize Sparklines in Excel
  • How to Use Solver Tool in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • How to count table rows in Excel

References

  • How to get relative row numbers in a range in Excel
  • How to use Excel INDIRECT Function
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to get address of last cell in range in Excel
  • How to get first column number in range in Excel

Data Validations

  • Excel Data validation allow weekday only
  • Prevent invalid data entering in specific cells
  • Excel Data validation unique values only
  • Excel Data validation allow uppercase only
  • How To Create Drop-down List in Excel

Tag: Errors

Vlookup Examples in Excel

by

The VLOOKUP function is one of the most popular functions in Excel. This chapter contains many easy to follow VLOOKUP examples for Exact Match, Approximate Match, Right Lookup, First Match, Case-insensitive, Multiple Criteria, #N/A error  and  Multiple Lookup Tables. Exact Match Most of the time you are looking for an exact match when you use the VLOOKUP function in Excel. …

Continue Reading

Common Errors in Excel

by

It could be challenging sometimes working in excel and you find ##### error, #NAME? error , #VALUE! error , #DIV/0! error and #REF! error on your worksheet. This chapter teaches you how to deal with some common formula errors in Excel. ##### error When your cell contains this error code, the column isn’t wide enough to display the value. 1. Click …

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

Count cells that contain errors in Excel

by

This tutorial shows how to Count cells that contain errors in Excel using the example below; Formula =SUMPRODUCT(–ISERR(range)) Explanation To count cells that contain errors, you can use the ISERR function, wrapped in the SUMPRODUCT function.  In the example shown, E5 cell contains this formula: =SUMPRODUCT(–ISERROR(B5:B9)) How this formula works SUMPRODUCT accepts one or more arrays, multiplies …

Continue Reading

Floating Point Errors in Excel

by

Floating point numbers are numbers that follow after a decimal point. Excel stores and calculates floating point numbers. On the other hand, excel cannot store numbers with very large floating points, so for the function to work correctly, the floating point numbers will need to be rounded to 5 decimal places. i.e The result of a …

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

IsError function examples in Excel

by

This chapter illustrates the ISERROR function in Excel. 1. For example, Excel displays the #DIV/0! error when a formula tries to divide a number by 0. The ISERROR function checks whether a value is an error and returns TRUE or FALSE. 2. Use the IF and the ISERROR function. If a cell contains an error, the value 5 is …

Continue Reading

How To Use AGGREGATE function to sum a range with errors in Excel

by

Excel functions such as SUM, COUNT, LARGE and MAX don’t work if a range includes errors. However, you can easily use the AGGREGATE function to fix this. 1. For example, Excel returns an error if you use the SUM function to sum a range with errors. 2. Use the AGGREGATE function to sum a range with errors. Explanation: …

Continue Reading

Count Errors in Excel

by

IF function and ISERROR function are used to check for an error in Excel. This example shows you how to create an array formula that counts the number of errors in a range. 1. We use the IF function and the ISERROR function to check for an error in the record below: Explanation: the IF function returns 1, if an …

Continue Reading

How to create your own Search Box in Excel

by

This example teaches you how to create your own search box in Excel. You Can Create Your Own Search Box With Formulas To List All Searched Results This is what the spreadsheet looks like. If you enter a search query into cell B2, Excel searches through column E and the results appear in column B. To create this …

Continue Reading

Circular Reference Problem Solved

by

You’ve entered a formula, but it’s not working. Instead, you’ve got this message about a “circular reference.” Circular Reference occurs your formula is trying to calculate itself. A formula in a cell that directly or indirectly refers to its own cell is called a circular reference. 1. For example, the formula in cell A3 below directly refers …

Continue Reading

Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’

by

Formula auditing in Excel allows you find and correct errors in formulas. Also, displays the relationship between formulas and cells using Trace Precedents, Remove Arrows, Trace Dependents, Show Formulas, Error Checking and Evaluate Formula. The example below helps you master Formula Auditing quickly and easily. Trace Precedents You have to pay $96.00. To show arrows that indicate which cells are used …

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 boolean logic in Excel
  • IF, AND, OR and NOT Functions Examples in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function

Date Time

  • Convert date to text in Excel
  • DATE function: Description, Usage, Syntax, Examples and Explanation
  • How to determine year is a leap year in Excel
  • Add decimal minutes to time in Excel
  • EOMONTH function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Calculate conditional mode with criteria in Excel
  • How to randomly assign people to groups in Excel
  • Group arbitrary text values in Excel
  • How to randomly assign data to groups in Excel
  • Group numbers at uneven intervals in Excel

General

  • Currency vs Accounting Format in Excel
  • Mark Workbook as Final in Excel
  • Print Excel Sheet In Landscape Or Portrait
  • How to calculate percentage of total in Excel
  • Count cells less than in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning