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

Lookup and Reference Examples

  • Excel Advanced Lookup using Index and Match Functions
  • Approximate match with multiple criteria in Excel
  • Create hyperlink with VLOOKUP in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • How to use Excel TRANSPOSE Function

Data Analysis Examples

  • How to combine 2 or more chart types in a single chart in Excel
  • How To Create Pareto Chart in Excel
  • How To Insert and Customize Sparklines in Excel
  • Get column name from index in Excel Table
  • Understanding Anova in Excel

Data Validation Examples

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

Category: Excel Functions

Complete List of Excel Web Functions References and Examples

by

Web Function was one of the functions Microsoft released in Excel 2013. Here’s a full list of Built-In Excel Web Functions in Excel and their Descriptions below: Click each of the function link to see detailed example. Web Functions ENCODEURL Returns a URL-encoded string (New in Excel 2013) FILTERXML Returns data from XML content, using a specified …

Continue Reading

Complete List of Excel Statistical Functions References and Examples

by

Excel provides a variety of statistical functions that might be useful for you know in calculations. Statistical functions perform calculations ranging from basic mean, median & mode to the more complex statistical distribution and probability tests. Here’s a full list of Built-In Statistical Functions in Excel and their Descriptions below: Click each of the function link to …

Continue Reading

Complete List of Excel Lookup and Reference Functions, References and Examples

by

Excel comes with multiple Lookup and Reference functions that let you find matching values. This page provides a complete Excel Lookup and Reference functions list. Each of the function links will take you to a dedicated page, where you will find a description of the function, with examples of use. The Lookup and Reference Functions …

Continue Reading

Complete List of Excel Logical Functions, References and Examples

by

Logic Functions in Excel check the data and return the result «TRUE» if the condition is true, and «FALSE» if not. Here’s a full list of Built-In Logical Functions in Excel and their Descriptions below: Click each of the function link to see detailed example. Boolean Operator Functions AND Tests a number of user-defined conditions and …

Continue Reading

How to Remove Leading Spaces in Excel

by

The TRIM function in Excel removes leading spaces, extra spaces and trailing spaces. Use the SUBSTITUTE function to remove all spaces or non-breaking spaces. 1. The TRIM function below removes 2 leading spaces, 3 extra spaces and 2 trailing spaces. Note: the TRIM function does not remove single spaces between words. 2. To get the length of a string, use …

Continue Reading

Find function vs Search function in Excel

by

The FIND function and the SEARCH function are very similar to each other. This example shows the difference. 1. To find the position of a substring in a string, use the FIND function. FIND is case-sensitive. 2. To find the position of a substring in a string, use the SEARCH function. SEARCH is case-insensitive. Note: string “excel” found at …

Continue Reading

SUBSTITUTE function vs REPLACE function in Excel

by

This example shows the difference between the SUBSTITUTE function and the REPLACE function. 1a. If you know the text to be replaced, use the SUBSTITUTE function. 1b. The SUBSTITUTE function has a 4th optional argument. You can use this argument to indicate which occurrence you want to substitute. 2. If you know the position of the text to …

Continue Reading

Two ways to Compare Text in Excel

by

Case-sensitive and case-insensitive ways to Compare Text in Excel. This example shows two ways to compare text in Excel. One is case-sensitive and one is case-insensitive. 1. Use the EXACT function (case-sensitive). 2. Use the formula =A1=B1 (case-insensitive).

Continue Reading

OR function Examples in Excel

by

The OR function in Excel returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false. Combine the OR function with other functions and become an Excel expert. 1. For example, take a look at the OR function in cell D2 below. Explanation: the OR function returns TRUE if the first score is …

Continue Reading

How to create Roll the Dice in Excel

by

This example teaches you how to simulate the roll of two dice in Excel. Note: the instructions below do not teach you how to format the worksheet. We assume that you know how to change font sizes, font styles, insert rows and columns, add borders, change background colors, etc. 1. At the moment, each cell contains …

Continue Reading

How to use IFS function in Excel

by

Use the IFS function in Excel 2016 when you have multiple conditions to meet. The IFS function returns a value corresponding to the first TRUE condition. Note: if you don’t have Excel 2016, you can nest the IF function. 1a. If the value in cell A1 equals 1, the IFS function returns Bad. 1b. If the value in cell A1 …

Continue Reading

How to Check If A Cell Contains Specific Text in Excel

by

To check if a cell contains specific text, you can use the ISNUMBER and the SEARCH function in Excel. The ISNUMBER functions is used to check for a numeric value and the  SEARCH function returns the location of a substring in a string. There’s no CONTAINS function in Excel. 1. To find the position of a substring in a text …

Continue Reading

SWITCH function example in Excel

by

Excel SWITCH function compares an expression to a list of values and returns the corresponding result. This example teaches you how to use the SWITCH function in Excel 2016 instead of the IFS function. 1a. For example, the IFS function below finds the correct states. Explanation: cell A2 contains the string 85-UT. The RIGHT function extracts the 2 rightmost …

Continue Reading

Complete List of Excel Text Functions References and Examples

by

Excel has many functions to offer when it comes to manipulating text strings. The Excel TEXT function returns a number in a specified number format, as text. You can use the TEXT function to embed formatted numbers inside text. Here’s a full list of Built-In Text Functions in Excel and their Descriptions below: Click each of the function link to see detailed example. …

Continue Reading

How to get number of days, weeks, months or years between two dates in Excel

by

To get the number of days, weeks or years between two dates in Excel, use the DATEDIF function. The DATEDIF function has three arguments. 1. Fill in “d” for the third argument to get the number of days between two dates. Note: =A2-A1 produces the exact same result! 2. Fill in “m” for the third argument to …

Continue Reading

How to enter Today’s Date or Static Date and Time in Excel

by

To enter today’s date in Excel, use the TODAY function. To enter the current date and time, use the NOW function. To enter the current date and time as a static value, use keyboard shortcuts. Today and Now 1. To enter today’s date in Excel, use the TODAY function. Note: the TODAY function takes no arguments. This date will …

Continue Reading

How to Calculate Age in Excel

by

To calculate the age of a person in Excel, use the DATEDIF function and the TODAY function. The DATEDIF function has three arguments. 1. Enter the date of birth into cell A2. 2. Enter the TODAY function into cell B2 to return today’s date. 3. The DATEDIF function below calculates the age of a person. Note: fill …

Continue Reading

How to get Weekdays, Working days between Two Dates in Excel

by

Learn how to get the day of the week of a date in Excel and how to get the number of weekdays/working days between two dates. Weekday Function 1. The WEEKDAY function in Excel returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week of a date. Apparently, 12/18/2017 falls on a Monday. 2. You can …

Continue Reading

How to calculate number of Days until Birthday

by

To calculate the number of days until your birthday in Excel, execute the following steps. 1. Enter the date of birth into cell A2. 2. Enter the TODAY function into cell B2 to return today’s date. 3. The most difficult part in order to get the number of days until your birthday is to find your next birthday. The formula …

Continue Reading

Find Last Day of the Month in Excel

by

To get the date of the last day of the month in Excel, use the EOMONTH (End of Month) function. 1. For example, get the date of the last day of the current month. Note: the EOMONTH function returns the serial number of the date. Apply a Date format to display the date. 2. For example, get the date of …

Continue Reading

How to get Holiday Date from Year in Excel

by

This example teaches you how to get the date of a holiday for any year (2019, 2020, etc). Before you start: the CHOOSE function returns a value from a list of values, based on a position number. For example, =CHOOSE(3,”Car”,”Train”,”Boat”,”Plane”) returns Boat. The WEEKDAY function returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week of …

Continue Reading

How to calculate Quarter of Date in Excel

by

An easy formula that returns the quarter for a given date. There’s no built-in function in Excel that can do this. 1. Enter the formula shown below. Explanation: ROUNDUP(x,0) always rounds x up to the nearest integer. The MONTH function returns the month number of a date. In this example, the formula reduces to =ROUNDUP(5/3,0), =ROUNDUP(1.666667,0), 2. May is …

Continue Reading

How to Separate Text Strings in Excel

by

This example teaches you how to separate strings in Excel. Asides from using ‘Convert Text to Columns Wizard’ in data tab, you can as well follow the steps below to Separate Text Strings in Excel . The problem we are dealing with is that we need to tell Excel where we want to separate the string. In case of …

Continue Reading

How to calculate Number of Instances in Excel

by

This example describes how to count the number of instances of text (or a number) in a cell. 1. Use the LEN function to get the length of the string (25 characters, including spaces). 2. The SUBSTITUTE function replaces existing text with new text in a string. LEN(SUBSTITUTE(A1,B1,””)) equals 13 (the length of the string without …

Continue Reading

Split Cell Content Using Text to Columns in Excel

by

To separate the contents of one Excel cell into separate columns, you can use the ‘Convert Text to Columns Wizard’. For example, when you want to separate a list of full names into last and first names. 1. Select the range with full names. 2. On the Data tab, in the Data Tools group, click Text to Columns. …

Continue Reading

Count Number of Words in Excel

by

This chapter shows how to count the number of words in a cell. 1a. The TRIM function returns a string with leading spaces, extra spaces and trailing spaces removed. 1b. To get the length of the string with normal spaces, we combine the LEN and TRIM function. 2a. The SUBSTITUTE function replaces existing text with new text in a …

Continue Reading

How to Calculate Tax Rates in Excel

by

This example teaches you how to calculate the tax on an income using the VLOOKUP function in Excel. The following tax rates apply to individuals who are residents of Australia. Taxable income Tax on this income 0 – $18,200 Nil $18,201 – $37,000 19c for each $1 over $18,200 $37,001 – $87,000 $3,572 plus 32.5c for each $1 over $37,000 …

Continue Reading

How to calculate Day of the Year in Excel

by

An easy formula that returns the day of the year for a given date. There’s no built-in function in Excel that can do this. 1. To get the year of a date, use the YEAR function. 2. Use the DATE function below to return January 1, 2016. The DATE function accepts three arguments: year, month and day. 3. The …

Continue Reading

Excel Functions by Categories

by

This page provides a complete Excel Functions list, grouped by category. Each of the function links will take you to a dedicated page, where you will find a description of the function, with examples of use and details of common errors. Text Functions Logical Functions Information Functions Date and Time Functions Lookup and Reference Functions …

Continue Reading

Complete List of Excel Cube Functions References and Examples

by

Excel Cube functions were introduced in Microsoft Excel 2007. The Cube functions  perform calculations and extract data from a cube, which is stored on an external SQL server. These functions are only supported with a connection to Microsoft SQL Server 2005 Analysis Services or later data source. Data cubes are multidimensional sets of data that …

Continue Reading

Complete List of Excel Engineering Functions References and Examples

by

Excel Engineering Functions perform the most commonly used engineering calculations, many of which relate to Bessel Functions, Complex Numbers or converting between different bases. Here’s a full list of Built-In Engineering Functions in Excel and their Descriptions below: Click each of the function link to see detailed example. Converting Between Units of Measurement CONVERT Converts a …

Continue Reading

Complete List of Excel Financial Functions References and Examples

by

The Excel Financial Functions perform many of the common financial calculations, such as the calculation of yield, interest rates, duration, valuation and depreciation. Here’s a full list of Built-In Financial Functions in Excel and their Descriptions below: Click each of the function link to see detailed example. Single Cash Flow Functions FVSCHEDULE Calculates the future value …

Continue Reading

Complete List of Excel Database Functions References and Examples

by

Excel Database Functions are designed to help you to work within a database (i.e. a large number of organised data records), stored in Excel. The database functions perform basic calculations, such as sum, average, count, etc, but they also use criteria arguments, that allow you to only perform the calculation for a specified subset of …

Continue Reading

Complete List of Excel Math and Trig Functions, References and Examples

by

Excel Math Functions perform many of the common mathematical calculations, including basic arithmetic, conditional sums & products, exponents & logarithms, and the trigonometric ratios. Here’s a full list of Built-In Math and Trig Functions in Excel and their Descriptions below: Click each of the function link to see detailed example. Basic Numeric Information ABS Returns the …

Continue Reading

Complete List of Excel Information Functions References and Examples

by

Information Functions is a built-in function in Excel that returns info about the current environment, including platform. The Information Functions can be used as a worksheet function in Excel. As a worksheet function, the INFO function can be entered as part of a formula in a cell of a worksheet. Here’s a full list of …

Continue Reading

Complete List of Excel Date and Time Functions, References and Examples

by

Before using the Date and Time Excel Functions, it is advised that you ensure you have a clear understanding of the way Excel stores Dates and Times Here’s a full list of Built-In  Date and Time Excel Functions in Excel and their Descriptions below: Click each of the function link to see detailed example. Creating Dates …

Continue Reading

Function Keys f1 – f12 in excel — Examples

by

Function keys allow you to do things with your keyboard instead of your mouse to increase your speed. The function keys ranges from F1 | F2 | F3 | F4 | F5 | F6 | F7 | F8 | F9 | F10 | F11 | F12. Examples and Uses  of  Excel F1 – F12 Function keys F1 Opens Excel Help. …

Continue Reading

Excel Advanced Lookup using Index and Match Functions

by

Instead of using VLOOKUP, Use INDEX and MATCH and become an Excel pro. . To perform advanced lookups, you’ll need INDEX and MATCH. Match The MATCH function returns the position of a value in a given range. For example, the MATCH function below looks up the value 53 in the range B3:B9. Explanation: 53 (first argument) found at position 5 …

Continue Reading

Excel Two-Way Lookup Example

by

This example teaches you how to lookup a value in a two-dimensional range. We use the INDEX and the MATCH function in Excel. Below you can find the sales of different ice cream flavors in each month. 1. To find the position of Feb in the range A2:A13, use the MATCH function. The result is 2. 2. To find …

Continue Reading

Perform case-sensitive Lookup in Excel

by

By default, the VLOOKUP function performs a case-insensitive lookup. However, you can use the INDEX, MATCH and the EXACT function in Excel to perform a case-sensitive lookup. 1. For example, the simple VLOOKUP function below returns the salary of Mia Clark. However, we want to lookup the salary of MIA Reed (see cell G2). 2. The EXACT function in Excel …

Continue Reading

Posts navigation

  • Previous
  • 1
  • …
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • Next

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
  • How to use double quotes inside a formula in Excel
  • REPLACE, REPLACEB functions: Description, Usage, Syntax, Examples and Explanation
  • Remove text by matching in a cell in Excel
  • Remove first character in a cell in Excel
  • How to count specific characters in a range in Excel
  • Add days exclude certain days of week in Excel
  • Steps to create Dynamic calendar grid in Excel
  • SECOND function: Description, Usage, Syntax, Examples and Explanation
  • DATEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • Get project end date in Excel
  • IRR function: Description, Usage, Syntax, Examples and Explanation
  • COUPNUM function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annuity for interest rate in excel
  • Calculate original loan amount in Excel
  • Calculate payment periods for loan in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles