The Excel formulas every analyst uses
The handful of functions that cover most of the work in a finance or operations job, with an example of each.
Excel has hundreds of functions, but a small set does most of the daily work. Learn these well and you can answer most questions a manager will ask of a spreadsheet.
Totals and conditions
SUMIFS adds up the rows that meet one or more conditions. Sales in the North region for one product:
=SUMIFS(D2:D500, B2:B500, "North", C2:C500, "Widgets")
The first range is what to add. After it come pairs: a range to test, then the condition it must meet.
COUNTIFS counts rows the same way, without a range to add: =COUNTIFS(B2:B500, "North", E2:E500, "Late") counts late orders in the North. AVERAGEIFS works like SUMIFS but returns the average.
IF makes a decision: =IF(D2>1000, "Large", "Small"). Keep each IF short. If you are nesting more than two, IFS or a lookup table is easier to read.
Lookups
Pulling a value from another table is the next most common task: a price for a product code, a manager for an employee ID.
=XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")
If your colleagues use older versions of Excel, INDEX with MATCH does the same and works everywhere:
=INDEX(Products!C:C, MATCH(A2, Products!A:A, 0))
The 0 in MATCH means exact match. Forget it and you get the same silent errors as an approximate VLOOKUP.
Dates
Reports run by month, so date functions come up constantly.
- EOMONTH gives the last day of a month:
=EOMONTH(A2, 0)for this month,=EOMONTH(A2, -1)for last month. Good for grouping and for due dates. - NETWORKDAYS counts working days between two dates, skipping weekends:
=NETWORKDAYS(A2, B2). Add a range of holiday dates as a third argument. - TEXT turns a date into a label:
=TEXT(A2, "mmm yyyy")gives "Jan 2026". - YEAR and MONTH pull out the parts so you can filter or total by them.
Cleaning text
Data exported from other systems is rarely tidy. A few functions fix most of it:
- TRIM removes extra spaces, the most common reason a lookup fails on data that looks right.
- LEFT, RIGHT and MID take characters from a code:
=LEFT(A2, 2)gives the country code from "GB-10442". - TEXTSPLIT splits on a separator:
=TEXTSPLIT(A2, "-")puts "GB" and "10442" in two cells. - PROPER fixes capitals: "jane SMITH" becomes "Jane Smith".
- TEXTJOIN puts pieces back together with a separator:
=TEXTJOIN(", ", TRUE, A2:C2).
Common questions
Which Excel functions should I learn first?
SUM and IF, then SUMIFS and COUNTIFS, then XLOOKUP. Those cover totals, decisions, conditional totals and lookups, which is most of what day-to-day reporting needs.
What do Excel tests for jobs usually ask?
Most cover lookups (VLOOKUP or XLOOKUP), conditional totals (SUMIFS, COUNTIFS) and IF, often on a small table of sales or staff data. Being quick and accurate with those matters more than knowing rare functions.
Do these formulas work in Google Sheets?
Almost all of them do, with the same names and arguments. For splitting text, Google Sheets has its own SPLIT function, which works the same way for simple cases.
Try it yourself
- Totals by region and product with SUMIFS
- Counting orders with COUNTIFS
- Look up a price with XLOOKUP
- Working days with NETWORKDAYS
- Pull out a country code with LEFT
- Or start the course, which covers all of these in order.