Excel functions, worked out on real sheets.
Syntax, examples whose numbers are calculated, the mistakes people make, and a sheet that grades your own formula.
Lookup & reference
Find a value in a table and bring back what sits next to it.
INDEXINDEX returns the value at a position you give it: the 3rd row of a column, or the cell where row 2 and column 3 of a table meet. Paired with MATCH, it becomes a lookup that works in every Excel version.MATCHMATCH tells you where a value sits in a list: its position, not the value itself. MATCH("A-104", A2:A6, 0) returns 4 because A-104 is the 4th item.VLOOKUPVLOOKUP searches the first column of a table for a value and returns the value in the same row from a column you choose by number. Always end it with FALSE for an exact match.XLOOKUPXLOOKUP looks for a value in one range and returns the matching item from another. It replaces VLOOKUP and INDEX/MATCH: it looks left or right, it finds exact matches by default, and it can say what to show when nothing is found.Logical
Make a formula decide: test a condition, catch an error.
Math & conditional sums
Add and count, all of it or only the rows that match.
COUNTIFSCOUNTIFS counts the rows that meet every condition you give it, such as orders that are from the East region and still open.SUMIFSSUMIFS adds the numbers in one range for the rows that meet every condition you give it, such as all sales in the East region above 1,000.SUMPRODUCTSUMPRODUCT multiplies the values on each row of two or more ranges and adds up the results. With tests such as (A2:A7="North") inside, it sums or counts only the rows that pass, in any version of Excel.Date & time
Build dates, move them by months or working days, and count the time between two.
DATEDATE builds a date from a year, a month and a day: =DATE(2026,3,9) is 9 March 2026. It's the safe way to make a date in a formula, because it means the same date on every computer, whatever its date settings.DATEDIFDATEDIF counts the complete years, months or days between two dates: =DATEDIF(A2,B2,"y") is an age in whole years. Excel doesn't suggest it as you type, but it works all the same.EDATEEDATE moves a date by a whole number of months and keeps its day of the month: =EDATE(A2,3) is three months after A2. When the new month is too short for that day, you get its last day.EOMONTHEOMONTH returns the last day of a month, a number of months before or after a date: =EOMONTH(A2,0) is the end of A2's month, and =EOMONTH(A2,1) the end of the next one.NETWORKDAYSNETWORKDAYS counts the working days between two dates, Monday to Friday, with both dates included and any holidays you list left out. NETWORKDAYS.INTL does the same for any other weekend.WEEKDAYWEEKDAY returns a date's day of the week as a number. By default Sunday is 1 and Saturday 7; =WEEKDAY(A2,2) counts from Monday as 1 to Sunday as 7, which makes weekends easy to test for.WORKDAYWORKDAY returns the date a number of working days before or after a start date, skipping Saturdays, Sundays and the holidays you list: =WORKDAY(A2,5) is five working days after A2.Text
Show numbers and dates as text, in exactly the format you want.
Dynamic arrays
One formula that returns a whole list and spills it down the sheet.
FILTERFILTER returns every value that meets a condition, as a list that spills down the sheet and updates by itself when the data changes. It's the formula version of the filter button.UNIQUEUNIQUE returns each different value in a range once, as a list that spills down the sheet and updates when the data changes.Financial
Loans, present values and returns.
NPVNPV returns the present value of future cash flows at a discount rate. Excel's version discounts the very first value too, so add today's investment outside the function: =NPV(rate, CF1:CFn) + CF0.PMTPMT returns the fixed payment per period on a loan: =PMT(rate/12, years*12, -amount) is the monthly payment.No function matches that yet.
Reading is step one. Writing the formula is the skill.
Practise in a live grid that grades what your formula calculates, with a specific hint when it's wrong.
Runs in your browser · Graded on your result · No install