Excel formulas, explained step by step
Every walkthrough writes the formula out one step at a time, explains each part, and ends with a sheet you can download.
Formula walkthroughs Pull a price in from another sheet — Matching on a product codeCheck a name against another list — Is this one already on the list?Add up only the rows that match — SUMIFS with two conditions at onceTotal the rows between two dates — Everything from one date up to anotherA total for each name — Summary beside the raw rowsBuild a running total — Where the dollar signs come inCommission tiers and grade bands — Commission tiers, grades, RAG statusTurn a mark into a letter grade — Five bands, and a guard for an absent pupilFlag what is overdue — Anything past its dateCompare two lists — What is in one and missing from the otherFind who unfollowed you — Compare last month's list with today'sAdd up one customer's rows — A total for a single conditionCount rows that meet two conditions — How many, with more than one ruleShow a blank instead of an error — Keep a sheet clean when a formula failsLock a cell so it stops moving — Why $ signs matter when you fill downCount the days between two dates — Plain days, weekends includedRemove a character from a code — Take the dashes out of a referenceFind the duplicates in a list — Before you clean a mailing listSplit first and last name — First and last nameGet the text between two dashes — For codes like INV-1042-BRemove extra spaces — The usual reason a lookup failsPut two columns together — Building a full name or a keyCount working days between dates — Weekends left outAdd months to a date — Renewals and review datesRank a column — Who is first, second, thirdWork out a percentage change — This year against last yearLook up with INDEX and MATCH — Survives an inserted columnCount the distinct values — How many different, not how many rowsHighest value in each group — The top figure for one categoryWeighted average — When some rows count for moreEach row as a share of the total — Percentages that add to 100Age or length of service — In whole yearsGet the month name from a date — Turning a date into JanuaryCompare two columns row by row — Spotting what changedConvert text to numbers — When a column will not add upRound prices to the nearest 5 — Tidy pricing and time blocksThe third biggest value — Top three, not just the top oneFind the empty cells in a column — A gap in a column, named before it causes troubleGet the domain from an email address — Everything after the @ signSplit a code into two columns — Letters into one column, number into the nextTotal just one month’s rows — Selecting March from a full year of datesRound to two decimal places — Rounding the value, not just the displayPad a number with leading zeros — Turning 7 into 00007 so codes line upCount cells greater than a number — A single count of the rows above the limitMake initials from a full name — First letter of each of the two namesCount the days until a deadline — The count updates by itself each dayShow blank instead of zero — Leave the cell blank when there is nothing to reportFunctions
Formula errors
Formula List
Formula Fixer
Formula List
Settings: separator, Excel version
Argument separator
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Comma =SUM(A1,B1)
Semicolon =SUM(A1;B1)
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
Microsoft 365 or 2021
2019 or earlier
Is a formula returning an error?
Paste it below for a line-by-line check.
Check the formula
Clear
or press Enter
Try one of these
© Formula Steps — formulasteps.com
Bookmark
Share
