We also have alarge list of example formulas, amore complete list of Excel functions, andvideo training.
If you are new to Excel formulas,see this introduction.
Note: Excel now includesDynamic Array formulas, andalmost 50 new functions.
Date and Time Functions
Excel provides many functions to work withdatesandtimes.
If you want a static value, usedateandtimeshortcuts.
HOUR, MINUTE, SECOND, and TIME
Excel provides a set of parallel functions for times.
DATEDIF can also be configured to get total time in “normalized” denominations, i.e.
“2 years and 6 months and 27 days”.
you’ve got the option to use theEDATEandEOMONTH functionsfor this.
EDATE moves by month and retains the day.
EOMONTH works the same way, but always returns the last day of the month.
To calculate the number of workdays between two dates, you might useNETWORKDAYS.
If you need more flexibility on what days are considered weekends, see theWORKDAY.INTL functionandNETWORKDAYS.INTL function.
WEEKDAY returns a number between 1-7 that indicates Sunday, Monday, Tuesday, etc.
Use theWEEKNUM functionto get the week number in a given year.
However, theCONVERT functionis quite useful for everyday unit conversions.
Excel also hasISODDandISEVENfunctions that will test a number to see if it’s even or odd.
By the way, the green fill in the screenshot above is applied automatically with aconditional formattingformula.
Logical Functions
Excel’s logical functions are a key building block of many advanced formulas.
Logical functions return the boolean values TRUE or FALSE.
If you need a primer on logical formulas, this video goes through many examples.
In the screen below,VLOOKUPis used to retrieve cost from a menu item.
Column F contains just aVLOOKUP function, with no error handling.
Whereas IFNA only catches an #N/A error, theIFERROR functionwill catch any formula error.
IF and IFS functions
TheIF functionis one of the most used functions in Excel.
New in Excel 2019 andExcel 365, theIFS functioncan run multiple logical tests withoutnesting IFs.
Most famous of all isVLOOKUP:
More:23 things to know about VLOOKUP.
LOOKUP assumes values are sorted in ascending order and always performs an approximate match.
When LOOKUP can’t find a match, it will match the next smallest value.
ROWS and COLUMNS
TheROWS functionandCOLUMNS functionprovide a count of rows in a reference.
In the screen below, we are counting rows and columns in anExcel Tablenamed “Table1”.
Note ROWS returns a count of data rows in a table, excluding the header row.
By the way, here are23 things to know about Excel Tables.
HYPERLINK
you could use theHYPERLINK functionto construct a link with a formula.
Note: TRANSPOSE is a formula and is, therefore, dynamic.
If you just need to do a one-time transpose operation, usePaste Specialinstead.
OFFSET
TheOFFSET functionis useful for all kinds of dynamic ranges.
The result is arangethat can respond dynamically to changing conditions and inputs.
This concept is a bit tricky to understand at first, but it can be useful in many situations.
Below, we are using INDIRECT to get values from cell A1 in 5 different worksheets.
Each reference is dynamic.
If a sheet name changes, the reference will update.
For more details, see linked examples at the bottom of theINDIRECT function page.
Caution: both OFFSET and INDIRECT arevolatile functionsand can slow down large or complicated spreadsheets.
COUNTIF and COUNTIFS
For conditional counts, theCOUNTIF functioncan apply one criteria.
In the screen below,datais thenamed rangeC5:C13, used in all formulas.
Video:How to find the nth smallest or largest value
MINIFS, MAXIFS
TheMINIFSandMAXIFS.
RAND and RANDBETWEEN
Both theRAND functionandRANDBETWEEN functioncan generate random numbers on the fly.
RAND creates long decimal numbers between zero and 1.
RANDBETWEEN generates random integers between two given numbers.
ROUND, ROUNDUP, ROUNDDOWN, INT
To round values up or down, use theROUND function.
To force rounding up to a given number of digits, useROUNDUP.
To force rounding down, useROUNDDOWN.
To discard the decimal part of a number altogether, use theINT function.
MROUND, CEILING, FLOOR
To round values to the nearestmultipleuse theMROUND function.
TheFLOOR functionandCEILING functionalso round to a given multiple.
FLOOR forces rounding down, and CEILING forces rounding up.
MOD
TheMOD functionreturns the remainder after division.
In the screen below, we are using SUMPRODUCT to count and sum orders in March.
See theSUMPRODUCT pagefor details and links to many examples.
(seethis pagefor the full list).
The key differences are that AGGREGATE can run more operations (19 total) and can also ignore errors.
LEN shows up in a lot of formulas that count words orcharacters.
FIND, SEARCH
To look for specific text in a cell, use theFIND functionorSEARCH function.
These functions return the numeric position of matching text, but SEARCH allows wildcards and FIND is case-sensitive.
REPLACE, SUBSTITUTE
To replace text by position, use theREPLACE function.
To replace text by matching, use theSUBSTITUTE function.
CODE, CHAR
To figure out the numeric code for a character, use theCODE function.
To translate the numeric code back to a character, use theCHAR function.
In the example below, CODE translates each character in column B to its corresponding code.
In column F, CHAR translates the code back to a character.
To remove line breaks and other non-printing characters, useCLEAN.
TheCONCAT functionlets you concatenate (join) multiple values, including a range of values without a delimiter.
TheTEXTJOIN functiondoes the same thing, but allows you to specify a delimiter and can also ignore empty values.
Excel also provides theCONCATENATE function, but it doesn’t offer special features.
I wouldn’t bother with it and would insteadconcatenatedirectly with the ampersand (&) character in a formula.
EXACT
TheEXACT functionallows you to compare two text strings in a case-sensitive manner.
The text function lets you apply number formatting to numbers (including dates, times, etc.)
More:Detailed examples of custom number formatting.
Dynamic Array functions
Dynamic arraysare new inExcel 365, and are amajorupgrade to Excel’s formula engine.
