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

Data Analysis

  • How to add Trendline to a chart in Excel
  • Managing Conditional Formatting Rules in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • Working With Tables in Excel
  • Understanding Pivot Tables in Excel

References

  • How to get relative column numbers in a range in Excel
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • How to create dynamic named range with OFFSET in Excel
  • Multi-criteria lookup and transpose in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions

Data Validations

  • Excel Data validation date in specific year
  • Excel Data validation must not contain
  • Excel Data validation date in next 30 days
  • Excel Data validation allow weekday only
  • How To Create Drop-down List in Excel

Tag: ISNA function

IS functions: ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT

by

What is IS function in Excel? IS functions, ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT are under the Information functions in Microsoft Excel. Each of these functions, referred to collectively as the IS functions, checks the specified value and returns TRUE or FALSE depending on the outcome. For example, the ISBLANK function returns the logical value TRUE if the …

Continue Reading

Check if range contains a value not in another range in Excel

by

To test if a range contains any values (i.e. at least one value) not in another range, you can use the SUMPRODUCT function with MATCH and ISNA. The MATCH function receives a single lookup value, and returns a single match if any. In this case, however, we are giving MATCH an array for lookup value, …

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

Count missing values in Excel

by

This tutorial shows how to calculate Count missing values in Excel using the example below; Formula =SUMPRODUCT(–(COUNTIF(list1,list2)=0)) Explanation To count the values in one list that are missing from another list, you can use a formula based on the COUNTIF and SUMPRODUCT functions. In the example shown, the formula in H6 is: =SUMPRODUCT(–(COUNTIF(list1,list2)=0)) Which returns …

Continue Reading

Find missing values in Excel

by

This tutorial shows how to Find missing values in Excel using the example below; Formula =IF(COUNTIF(list,value),”OK”,”Missing”) Explanation If you want to find out what values in one list are missing from another list, you can use a  simple formula based on the COUNTIF function. The COUNTIF function counts cells that meet supplied criteria, returning the …

Continue Reading

Match first error in Excel

by

This tutorial shows how to Match first error in Excel using the example below; Formula {=MATCH(TRUE,ISERROR(range),0)} Explanation If you need to match the first error in a range of cells, you can use an array formula based on the MATCH and ISERROR functions. In the example shown, the formula is: {=MATCH(TRUE,ISERROR(B4:B11),0)} This is an array …

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

Count cells not equal to many things in Excel

by

This tutorial shows how to Count cells not equal to many things in Excel using the example below; Formula =SUMPRODUCT(–(ISNA(MATCH(data,exclude,0)))) Explanation To count cells not equal to any of many things, you can use a formula based on the MATCH, ISNA, and SUMPRODUCT functions. In the example shown, the formula in cell F5 is: =SUMPRODUCT(–(ISNA(MATCH(data,exclude,0)))) …

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

  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • Invoice status with nested if in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • Check multiple cells are equal in Excel
  • How to use Excel XOR Function

Date Time

  • How to calculate next day of week in Excel
  • Find Last Day of the Month in Excel
  • Dynamic date list in Excel
  • Count holidays between two dates in Excel
  • MINUTE function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group numbers at uneven intervals in Excel
  • Group arbitrary text values in Excel
  • Group times into unequal buckets in Excel
  • Running count group by n size in Excel
  • Group times into 3 hour buckets in Excel

General

  • Advanced Number Formats in Excel
  • How to Create Calendar in Excel
  • Excel Ribbon Quick Overview For Beginners
  • Convert column letter to number in Excel
  • How to get amount with percentage in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning