Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • Convert text string to valid reference in Excel using Indirect function
  • Get nth match with INDEX / MATCH in Excel
  • Count rows with at least n matching values
  • Last row number in range

Data Analysis Examples

  • Filter Data Based on Date in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • Managing Conditional Formatting Rules in Excel
  • Get column index in Excel Table

Data Validation Examples

  • Excel Data validation must contain specific text
  • Excel Data validation must begin with
  • Excel Data validation specific characters only
  • Excel Data validation with conditional list
  • Excel Data validation number multiple 100

Category: Information Functions

Excel Information Functions return info about the current environment, including platform,

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

by

What is SHEETS function in Excel? SHEETS function is one of the Information functions in Microsoft Excel that returns the number of sheets in a reference. Syntax of SHEETS function SHEETS(reference) The SHEETS function syntax has the following arguments. Reference (Optional): Reference is a reference for which you want to know the number of sheets it …

Continue Reading

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

by

What is SHEET function in Excel? SHEET function is one of the Information functions in Microsoft Excel that returns the sheet number of the reference sheet. Syntax of SHEET function SHEET(value) The SHEET function syntax has the following arguments. Value(Optional): Value is the name of a sheet or a reference for which you want the sheet …

Continue Reading

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

by

What is NA function in Excel? NA function is one of the Information functions in Microsoft Excel that returns the error value #N/A. #N/A is the error value that means “no value is available.” Use NA to mark empty cells. By entering #N/A in cells where you are missing information, you can avoid the problem …

Continue Reading

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

by

What is CELL function in Excel? CELL function is one of the Information functions in Microsoft Excel that returns info about the formatting, location, or contents of a cell. For example, if you want to verify that a cell contains a numeric value instead of text before you perform a calculation on it, you can …

Continue Reading

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

by

What is N function in Excel? N function is one of the Information functions in Microsoft Excel that returns a value converted to a number. Syntax of N function N(value) The N function syntax has the following arguments: Value: The value you want converted. N converts values listed in the following table. If value is or …

Continue Reading

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

by

What is ISFORMULA function in Excel? ISFORMULA function is one of the Information functions in Microsoft Excel that checks whether there is a reference to a cell that contains a formula, and returns TRUE or FALSE. Syntax of ISFORMULA function ISFORMULA(reference) The ISFORMULA function syntax has the following arguments. Reference: Reference is a reference to the …

Continue Reading

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

by

What is ISEVEN function in Excel? ISEVEN function is one of the Information functions in Microsoft Excel that returns TRUE if number is even, or FALSE if number is odd. Syntax of ISEVEN function ISEVEN(number) The ISEVEN function syntax has the following arguments: Number: The value to test. If number is not an integer, it is truncated. …

Continue Reading

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

by

What is INFO function in Excel? INFO function is one of the Information functions in Microsoft Excel that returns information about the current operating environment. Syntax of INFO function INFO(type_text) The INFO function syntax has the following arguments: Type_text: Text that specifies what type of information you want returned. Type_text Returns “directory” Path of the current …

Continue Reading

ERROR.TYPE function: Description, Usage, Syntax, Examples and Explanation

by

What is ERROR.TYPE function in Excel? ERROR.TYPE function is one of the Information functions in Microsoft Excel that returns a number corresponding to one of the error values in Microsoft Excel or returns the #N/A error if no error exists. You can use ERROR.TYPE in an IF function to test for an error value and …

Continue Reading

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

by

What is TYPE function in Excel? TYPE function is one of the Information functions in Microsoft Excel that returns the type of value. Use TYPE when the behavior of another function depends on the type of value in a particular cell. Syntax of TYPE function TYPE(value) The TYPE function syntax has the following arguments: Value: Can …

Continue Reading

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

How to check worksheet name exists in Excel

by

To test if a worksheet name exists in a workbook, you can use a formula based on the ISREF and INDIRECT functions. Formula =ISREF(INDIRECT(“sheetname”&”!A1″)) Explanation In the example shown, the formula in C5 is: =ISREF(INDIRECT(B5&”!A1″)) How this formula works The ISREF function returns TRUE for a valid worksheet reference and FALSE is not. In this …

Continue Reading

Get workbook name and path without sheet in Excel

by

If you want to get the current workbook’s full name and path without a sheet name, you can use a formula that employs several text functions to strip off the sheet name. The final result will be a text string that looks like this: path[workbook.xlsm] Formula =SUBSTITUTE( LEFT(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))-1),”[“,””) Explanation How the formula works The CELL …

Continue Reading

How to get sheet name only in Excel

by

If you want to get the sheet name only (i.e. the sheet name without the file name or path) you can do so with rather long formula that uses the MID function along with the FIND function. The final result will look something like this: Sheet1 Formula =MID(CELL(“filename”,A1),FIND(“]”, CELL(“filename”,A1))+1,255) Explanation How the formula works The …

Continue Reading

How to retrieve workbook name only in Excel

by

If you want to get the workbook name only (i.e. the file name without path or sheet name) you can do so with a rather long formula that uses the MID function along with the FIND function. Formula =MID(CELL(“filename”,A1),FIND(“[“,CELL (“filename”,A1))+1,FIND(“]”,CELL(“filename”,A1)) -FIND(“[“,CELL(“filename”,A1))-1) Explanation How the formula works The cell function is used to get the full …

Continue Reading

How to get full workbook name and path in Excel

by

If you want to get the current workbook’s full path, name, and sheet with a formula, you can use the CELL function and a reference to any cell in the workbook. CELL will return the name in this format: path[workbook.xlsx]sheetname Note that you must save the worksheet in order to get the a result. Formula …

Continue Reading

How to check cell that contains specific text in Excel

by

To check if a cell contains specific text, you can use the SEARCH function together with the ISNUMBER function. In the generic version, substring is the specific text you are looking for, and text represents text in the cell you are testing. Formula =ISNUMBER(SEARCH(substring,text)) Explanation  In the example shown, the formula in D5 is: =ISNUMBER(SEARCH(C5,B5)) This formula returns TRUE if …

Continue Reading

How to check cell that contains one of many with exclusions in Excel

by

To test a cell for one of many strings, while excluding others, you can use a formula based on the SEARCH, ISNUMBER, and SUMPRODUCT functions. Formula =(SUMPRODUCT(–ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(–ISNUMBER(SEARCH(exclude,A1)))=0) Note: this formula returns either 1 or zero, which are handled like TRUE and FALSE in formulas, conditional formatting, or data validation. Explanation In the example shown the …

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

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
  • CONCAT function: Description, Usage, Syntax, Examples and Explanation
  • CHAR function: Description, Usage, Syntax, Examples and Explanation
  • How to split text with delimiter in Excel
  • Split numbers from units of measure in Excel
  • Add line break based on OS in Excel
  • Display the current date and time in Excel
  • Sum through n months in Excel
  • Calculate date overlap in days in Excel
  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • Get date from day number in Excel
  • ODDLPRICE function: Description, Usage, Syntax, Examples and Explanation
  • ACCRINTM function: Description, Usage, Syntax, Examples and Explanation
  • Example of Future value of annuity in Excel
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • PDURATION function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles