Which Excel and Google Sheets Formulas Do You Need for Work?

What makes a formula 'essential' for work?

Spreadsheet software includes hundreds of functions, and nobody needs most of them. When we run spreadsheet workshops at Bodhih Training, we ask participants to bring the questions they actually answer at work. The same handful come up every time: how much did we sell, how many are overdue, what is the average score, what is the price of this item, how many working days until the deadline.

A formula is essential if it answers one of those recurring questions and saves you from filtering, copying or typing by hand. The list below is short on purpose. Learn these fifteen well, with practice on realistic data, and you will handle most office requests faster than colleagues who know fifty functions badly.

Which basic formulas should everyone know first?

Start with the whole-column functions. SUM adds a range, AVERAGE gives the mean, COUNT counts cells containing numbers, COUNTA counts any non-empty cells, and MIN and MAX return the smallest and largest values. They are identical in Excel and Google Sheets.

Alongside them, learn how references behave when you copy a formula. Microsoft's own overview of formulas explains the three types: relative references such as A1 change when copied, absolute references such as $A$1 stay fixed, and mixed references such as A$1 or $A1 fix only the row or only the column. Press F4 while editing a reference to cycle through them. Most 'my formula worked in one row and broke in the next' problems come down to a missing dollar sign.

How do IF and IFS help you make decisions in a sheet?

IF takes three parts: a test, the result if the test is true, and the result if it is false. =IF(M6>=1000,"Large","Standard") labels each order. Combine tests with AND when all must be true, or OR when any one is enough.

When you need three or more outcomes, IFS is easier to read than nested IFs. It checks each condition in order and returns the result for the first one that is true, so always write the strictest condition first. A final TRUE acts as 'everything else'. IFS is available in Google Sheets, Microsoft 365 and Excel 2019 or later; in older versions, nest IF functions instead.

One caution: IFERROR is useful for tidying dashboards, but it hides every error, including real mistakes. Get the formula working and understand any errors first, then decide whether to wrap it.

How do COUNTIFS and SUMIFS replace manual filtering?

Most business questions are a count or a total with conditions: revenue where the region is West, orders where the status is Overdue, hours where the department is IT and the month is June. COUNTIFS and SUMIFS answer these directly, without touching a filter.

Both take pairs of arguments: a range to check, then the condition. SUMIFS puts the range you want to add first: =SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2). This is the opposite of the older SUMIF, which puts the sum range last, so if you only learn SUMIFS you only need to remember one order.

A reliable method is to say the question as a sentence before typing. 'Sum Revenue where Region is West and Category is Furniture' becomes =SUMIFS(M6:M400,C6:C400,"West",I6:I400,"Furniture"). Then filter the data once to check the answer matches.

• Exact text: "West" (not case-sensitive)
• A value in a cell: G2 (no quotes), so the question can change without editing the formula
• Not equal: "<>Returned"
• Greater than a number in a cell: ">"&G3
• On or after a date: ">="&DATE(2026,6,1)
• Starts with: "Del*" (asterisk for any characters)

Should you use XLOOKUP, VLOOKUP or INDEX MATCH?

All three fetch a value from another table using a shared key, such as a product code. The right choice depends mainly on which software your colleagues use.

XLOOKUP is the modern option. According to Microsoft's documentation, it matches exactly by default and takes the lookup value, the range to search, the range to return and an optional 'if not found' value. It is not available in Excel 2016 or Excel 2019. Google added XLOOKUP to Google Sheets in August 2022, as announced on the Google Workspace Updates blog, so it now works there too.

VLOOKUP works everywhere but has three weaknesses: it can only look to the right of the key column, its column number breaks if someone inserts a column, and it does an approximate match unless you end it with FALSE. INDEX with MATCH avoids all three and works in every version, which makes it the safest choice for shared files when some people still use older Excel.

Which text and date formulas save the most time?

Text arrives messy. TRIM removes leading, trailing and double spaces, which are the most common reason lookups fail. PROPER, UPPER and LOWER fix capitals. LEFT, RIGHT and MID extract characters, and FIND tells you where a space or dash sits so you can split names and codes: =LEFT(B6,FIND(" ",B6)-1) returns a first name. TEXT turns a number or date into formatted text for labels and sentences.

Dates are stored as numbers, so you can subtract one from another to get days. EOMONTH(date,0) returns the last day of the month, which is ideal for monthly reporting periods. NETWORKDAYS counts working days between two dates and can exclude a list of holidays. DATEDIF(start,end,"Y") gives full years between two dates for tenure or age. All of these behave the same in Excel and Google Sheets.

When should you use a pivot table instead of a formula?

Formulas are best when you need a few specific numbers in a fixed layout, such as the tiles on a monthly dashboard. Pivot tables are best when you are exploring, when the question keeps changing, or when you need many groups at once, such as revenue by region by month.

A pivot table needs tidy data: one header row, one record per row, no blank rows and no totals mixed in. In Excel, click inside the data and choose Insert, then PivotTable. In Google Sheets, choose Insert, then Pivot table. Put the field you want down the side in Rows, the field you want across the top in Columns and the number in Values, then choose Sum, Count or Average.

Two habits prevent most pivot mistakes. In Excel, refresh the pivot after the source data changes, because it does not update on its own (Google Sheets pivots do). And check one cell of the pivot against a filtered total of the source data before you share it.

Why do formulas give wrong answers, and how do you check them?

When a formula returns a strange result, the data is usually to blame. Numbers stored as text are ignored by SUM. Extra spaces make 'West ' different from 'West'. Dates imported as text will not sort or filter properly. Ranges that stop at row 200 miss the rows added later.

Spreadsheet problems are not just small annoyances. In 2020, PublicTechnology reported that 15,841 positive COVID-19 cases in England were not passed to the contact-tracing system over about a week, because data was handled in an older Excel file format limited to roughly 65,000 rows. No formula was wrong; the process simply had no check.

The habit that protects you is simple: check one number by hand before you send anything. Filter the data to the same conditions as your formula and compare with the status bar total. If they match, you can trust the rest.

What is the fastest way to learn these formulas?

Practise little and often on data that looks like your real work. Twenty-five minutes a day for four weeks will take most people further than a full-day course followed by nothing. Work through one function family at a time: basics and references, then IF and IFS, then COUNTIFS and SUMIFS, then lookups, then text and dates, then pivot tables.

AI assistants can help you write and explain formulas, but treat their output as a draft. Test every formula on a row where you already know the answer, and do not paste confidential data into tools your company has not approved.

If you want a structured starting point, the Spreadsheet Superpowers kit from Bodhih Training includes a practice workbook with 200 rows of realistic sales data and 20 exercises that mark themselves, plus cheat sheets for Excel and Google Sheets. To measure where you are now, try the Spreadsheet & Data Literacy assessment on AssessAll, and add the skills you want to build to a development plan on Jobulary.