• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Functions

You are here: Home / Excel Functions
Excel Functions

Here you'll find an index of common Excel Functions written in layman's terms and in a language we all understand. No computer speak, just plain English and practical examples of Excel functions used in formulas.

Before we dive into the examples I want to cover some terminology that I’ll be using.

Anatomy of an Excel Function

Excel functions comprise of the function name and arguments, as you can see in the example below:

Excel Function Anatomy

The arguments are the inputs required by the function and are enclosed in parentheses.

Arguments surrounded by square brackets are optional. For example, the VLOOKUP argument; ‘range_lookup’, is optional.

In some cases, if you omit optional arguments Excel will apply a default. This default will be specific to each function.

Excel Functions Library

Click the links below to jump to a section.

  • Array Functions
  • Array Shaping Functions
  • Database Functions
  • Date and Time Functions
  • Engineering Functions
  • Financial Functions
  • Information Functions
  • Logical Functions
  • Lookup and Reference Functions
  • Math Functions
  • Statistical Functions
  • Text Functions

Array Functions

FILTER Filter cells based on criteria.
RANDARRAY Returns an array of random numbers between 0 and 1.
SEQUENCE Returns list of sequential numbers that increment as specified.
SORT Sort cells or arrays in ascending or descending order.
SORTBY Sort a range or arrays based on criteria.
UNIQUE Extract a unique or distinct list from a range or array.

Array Shaping Functions

Note: these functions are only available to Microsoft 365 users.

EXPAND Expands or pads an array to a specified number of rows and columns.
TOROW Returns the array in a single row. Useful for combining data across multiple columns and rows into a single row.
TOCOL Returns the array in a single column. Useful for combining data across multiple columns and rows into a single column.
WRAPROWS Lets you wrap (reshape) a row or column of values into rows, you specify the number of values in each row.
WRAPCOLS Lets you wrap (reshape) a row or column of values into columns, you specify the number of values in each column.
DROP Remove a specified number of contiguous rows or columns from the start or end of an array.
TAKE Extract a specified number of contiguous rows or columns from the start or end of an array.
CHOOSEROWS Extract rows from the specified column or columns.
CHOOSECOLS Extract columns from the specified rows or rows.
VSTACK Combine arrays arranged vertically into a new single array.
HSTACK Combine arrays arranged horizontally into a new single array.

Database Functions

DSUM Sum a range based on criteria. Alternative to SUMIFS.

Date and Time Functions

DATE Build a date with separate year, month and day values.
DATEDIF Find the difference between two dates in days, months or years.
DATEVALUE Convert dates stored as text to a date Excel can use in formulas etc.
DAY Returns the day of the month between 1 and 31 from a date or text.
DAYS Returns the number of whole days between two date serial numbers. It ignores time portions where included.
DAYS360 Returns the number of days between two date serial numbers based on a 360-day year, or twelve 30-day months.
EDATE Returns the same date of the month, n months before or after the specified date.
EOMONTH Returns the last day of the month, n months before or after the specified date.
HOUR Returns the hour integer ranging from 0 to 24, from a time.
ISOWEEKNUM Returns the week number of a date serial number. The first Monday of the year marks the start of week 1.
MINUTE Returns the minute, ranging from 0 to 59, from a time.
MONTH Returns the month number, between 1 and 12, from a date.
NETWORKDAYS Returns the number of whole working days between two date serial numbers, excluding weekends.
NETWORKDAYS.INTL Returns the number of whole working days between two date serial numbers, excluding weekend days of your choice.
NOW Returns the current date and time from your computer clock.
SECOND Returns the seconds, ranging from 0 to 59, from a time.
TIME Build a time value by entering separate hour, minute and second values.
TIMEVALUE Convert times stored as text to a time Excel can use in formulas etc.
TODAY Returns the current date serial number from your computer clock.
WEEKDAY Returns the day number of the week from a date serial number. The default ranges from 1 for Sunday, through to 7 for Saturday.
WEEKNUM Returns an integer representing the week number (from 1 to 54) of the year from a date serial number.
WORKDAY Returns the day number of the week from a date serial number. The default return type ranges from 1 for Sunday to 7 for Saturday.
WORKDAY.INTL Returns a date serial number that is the specified number of working days before or after the start date.
YEAR Returns the year as an integer between 1900 and 9999, from a date.
YEARFRAC Calculates the fraction of the year represented by the number of whole days between two dates.

Engineering Functions

CONVERT Converts inches to centimetres, pounds to grams etc.

Financial Functions

ACCRINT Returns the accrued interest that gets paid periodically.
EFFECT Returns the effective annual interest rate, from the nominal annual interest rate (APR).
FV Calculate the future value of an investment.
NPER Calculates the number of periods to reach an investment goal.
PMT Returns the regular and constant repayments for a loan or mortgage required to reduce the balance to zero, or an amount you specify.
STOCKHISTORY Get historical price data about financial instruments.

Information Functions

CELL Returns information about the formatting, contents or location of a cell.
N Converts a value to a number, but it's clever uses is where it shines.
SHEET Returns the sheet number of a cell reference.
SHEETS Returns the count of sheets in a given reference.

Logical Functions

IF Test for a condition to be met and return a result if true, or false.
IFS Allows for multiple logical tests without the need for nesting. New in Excel 2019.
IFERROR Hide or handle formula errors.
SWITCH Looks up a value in a list of values, and returns the result corresponding to the first matching value. New in Excel 2019.
XOR Performs an 'exclusive or' test on an array, range or one or more expressions. New in Excel 2013.

Lookup and Reference Functions

ADDRESS Return a text value of a cell address.
CHOOSE Return a value from a list based on the position specified.
FILTER Filter cells based on criteria.
GETPIVOTDATA Get values from a PivotTable.
HLOOKUP Look up a value in the top row of a table.
HYPERLINK Insert links to internal or external locations.
IMAGE insert images into cells with a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP file types.
INDEX lookup a range of cells and return a single value, an array of values, a reference to a cell or range of cells.
INDIRECT Convert text into a cell reference.
MATCH Returns the relative position of a value. The lookup range can take the shape of a row or column.
OFFSET Return a range of cells offset from a starting cell.
SORT Sort cells or arrays in ascending or descending order.
SORTBY Sort cells or arrays based on criteria.
UNIQUE Extract a unique or distinct list from a range or array.
VLOOKUP Look up a value in the first column of a table.
XLOOKUP Like VLOOKUP but much better!

Math Functions

AGGREGATE Returns an aggregate (sum, average, min, max, count etc.) of a list or database.
MOD Returns the remainder after a number is divided by a divisor.
MROUND Rounds numbers to the nearest multiple.
RAND Returns an evenly distributed random real number greater than or equal to 0 and less than 1.
RANDARRAY Returns an array of random numbers between 0 and 1.
RANDBETWEEN Returns a random integer number between the numbers you specify.
SEQUENCE Returns list of sequential numbers that increment as specified.
SIGN Returns the sign of a number.
SUBTOTAL Choose the aggregation method e.g. SUM, AVERAGE, COUNT etc. and ignore or include filtered rows.
SUMPRODUCT Much more than just multiplying arrays and returning the sum of the results.

Statistical Functions

FORECAST Forecast future values using linear regression.
FORECAST.ETS Forecast future values using Exponential Triple Smoothing and machine learning. New in Excel 2016
FORECAST.ETS.CONFINT Returns a confidence interval for forecasted values. New in Excel 2016
FORECAST.LINEAR Forecast future values using linear regression. New in Excel 2016, replacing the FORECAST function.
RANK Return a rank for a dataset. Includes RANK.EQ and RANK.AVG

Text Functions

CHAR Returns a character based on its Windows-1252 character number.
CLEAN Removes non-printing characters from text.
CONCATENATE Joins text from separate cells together.
LAMBDA Define custom functions with LAMBDA.
LET Define variables and intermediate calculations to names inside of a formula.
SUBSTITUTE Replaces new text for old text in a text string.
T Checks whether a value is text, and returns the text if it is, otherwise returns a blank.
TEXT Converts numbers to text in the format you specify.
TEXTJOIN Joins text together and can ignore empty cells. New in Excel 2019.
TEXTAFTER Extracts text after a specified delimiter.
TEXTBEFORE Extracts text before a specified delimiter.
TEXTSPLIT Splits text based on a specified delimiter.
TRIM Remove the excess spaces from text, except for single spaces between words.

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x