What Excel Formula Should I Use?

When you know you need a formula but you're not sure which one, it's hard to even know what to search for. Here's a practical guide to finding the right Excel formula for the most common tasks.

Contents:

1. Quick Answer

2. I Need to Look Something Up

3. I Need to Add, Count, or Average Values

4. I Need to Work With Text

5. I Need to Work With Dates

6. I Need to Make a Decision (If/Then Logic)

7. I Need to Do Math

Quick Answer

The formula you need depends on what you're trying to do. Use XLOOKUP to find a value in a table, SUMIF or COUNTIF to add or count with conditions, IF for yes/no logic, and TEXT or LEFT/RIGHT for text tasks. The sections below match each common task to the right function.

I Need to Look Something Up

If you have a value and you want to pull related information from a table, a lookup function is what you need.

XLOOKUP is the modern choice. Give it a search value, the column to search in, and the column to return from.

= XLOOKUP("John Smith", A2:A100, B2:B100)

If you're on an older version of Excel that doesn't have XLOOKUP, use VLOOKUP instead. Check out how to perform a two-way XLOOKUP if you need to match on both a row and a column at once.

I Need to Add, Count, or Average Values

For a basic sum or count of everything in a range, SUM and COUNT work fine. But most of the time you need a condition attached.

SUMIF adds up values where a condition is met. COUNTIF counts them.

= SUMIF(A2:A100, "West", B2:B100)

= COUNTIF(A2:A100, "West")

The formula above adds (or counts) only rows where column A says "West". For multiple conditions, step up to SUMIFS or COUNTIFS.

For averages, AVERAGEIF works the same way. If you want to average a range but skip zeros, see how to take the average of non-zero cells in Excel.

I Need to Work With Text

Excel has a whole set of text functions for cleaning, splitting, and combining data.

To join two pieces of text (like a first and last name), use TEXTJOIN or the ampersand operator.

= TEXTJOIN(" ", TRUE, A2, B2)

To pull characters from the left or right of a cell, use LEFT or RIGHT. To pull from the middle, use MID.

= LEFT(A2, 5)

= RIGHT(A2, 4)

To clean up extra spaces, TRIM is your friend. For more detail, see how to remove extra spaces from text.

I Need to Work With Dates

Date formulas cover a wide range. Here are the most common needs.

To get today's date, use TODAY. To get today plus the current time, use NOW.

= TODAY()

To find the number of days between two dates, just subtract them, or use DATEDIF for years/months/days breakdowns.

= B2 - A2

To find the end of a month from any date, EOMONTH is the go-to.

= EOMONTH(A2, 0)

I Need to Make a Decision (If/Then Logic)

When you need a formula to return one thing or another based on a condition, IF is the core function.

= IF(A2 > 100, "Over Budget", "On Track")

That reads: if A2 is greater than 100, return "Over Budget", otherwise return "On Track". You can nest IFs for multiple conditions, or use IFS (available in Excel 2019 and later) to keep things cleaner.

For checking whether multiple conditions are ALL true, wrap your tests inside AND. For checking if ANY condition is true, use OR.

I Need to Do Math

Beyond basic arithmetic, Excel's math functions cover a lot of ground.

To round a number, ROUND is the standard. ROUNDUP and ROUNDDOWN force a direction. For rounding to a specific multiple, use MROUND.

= ROUND(A2, 2)

To get an absolute value (strip the negative sign), use ABS.

= ABS(A2)

For everything else, SUM, PRODUCT, MOD (remainder after division), and POWER cover most needs in day-to-day work.

--

If you're still building your foundation with formulas, the basic Excel formulas and functions guide is a good next stop.