Excel Functions & Formulas: Examples Explained

Excel functions and formulas are the built-in tools that let you perform calculations, manipulate text, and analyze data without manual effort. You enter a formula like =SUM(A1:A10) into a cell, and Excel returns the result instantly. This article explains the most useful functions, how they work, and gives concrete examples you can copy and adapt.

Formula

The calculator above works out your average pace and speed for a run or ride. You enter the total time in minutes and the total distance in kilometers.

Pace (min/km) = Total time (min) ÷ Distance (km)

Speed (km/h) = Distance (km) ÷ (Total time (min) ÷ 60)

Variables:

• t – total time in minutes (e.g., 50)
• d – distance in kilometers (e.g., 10)
• pace – minutes per kilometer, rounded to 2 decimals
• speed – kilometers per hour, rounded to 2 decimals

Worked Example

Using the default values: t = 50 minutes, d = 10 km.

1. Pace = 50 ÷ 10 = 5.00 min/km
2. Speed = 10 ÷ (50 ÷ 60) = 10 ÷ 0.8333… = 12.00 km/h

The calculator shows pace = 5.00 min/km and speed = 12.00 km/h.

Why Excel Functions Matter

Excel has hundreds of functions, but most people only need a few dozen. Functions save time, reduce errors, and make your spreadsheets dynamic. When you change an input, every formula that depends on it recalculates automatically.

Formulas always start with an equals sign (=). After that comes the function name, then parentheses. Inside the parentheses you put arguments – the values, cell references, or ranges the function needs. For example, =SUM(A1:A10) adds all numbers from A1 to A10.

Most Useful Functions with Examples

Here are the functions you will use most often, with simple examples.

• SUM – adds numbers. =SUM(B2:B10) totals the range B2 to B10.
• AVERAGE – mean of numbers. =AVERAGE(C2:C10) gives the average of C2 to C10.
• IF – returns one value if a condition is true, another if false. =IF(D2>100,"High","Low") shows “High� if D2 is greater than 100, otherwise “Low�.
• VLOOKUP – finds a value in the first column of a table and returns a value from another column. =VLOOKUP("Apple",A2:B10,2,FALSE) looks for “Apple� in A2:A10 and returns the matching value from column B.
• COUNTIF – counts cells that meet a condition. =COUNTIF(A2:A10,">5") counts how many numbers in A2:A10 are greater than 5.
• CONCATENATE (or CONCAT) – joins text. =CONCATENATE(A2," ",B2) combines the text in A2 and B2 with a space between.
• TRIM – removes extra spaces. =TRIM(A2) cleans up leading, trailing, and double spaces.

How to Choose the Right Function

Start by asking what you want the result to be. If you need a total, use SUM. If you need an average, use AVERAGE. If you need to count cells that match a condition, use COUNTIF or SUMIF. If you need to pull data from another table, use VLOOKUP or INDEX/MATCH.

For text problems like cleaning up names or extracting parts of a string, use LEFT, RIGHT, MID, and TRIM. For dates, use DATEDIF to calculate differences in days, months, or years.

When you are unsure, type = and start typing a description; Excel shows a list of matching functions. Hover over any function to see a short explanation.

Common Mistakes and How to Avoid Them

• Missing equals sign – typing SUM(A1:A10) without = treats it as text. Always start with =.
• Wrong range – =SUM(A1:A10) adds rows 1 through 10, not 1 through 9. Double-check the range.
• Mixing units – if you multiply hours by a rate per minute, the result is wrong. Keep units consistent.
• Rounding too early – if you round an intermediate result, the final answer may be off. Use ROUND only on the final output.
• Using VLOOKUP with approximate match – the fourth argument FALSE means exact match. If you omit it, VLOOKUP may return an incorrect value.

Limits of Excel Formulas

Excel formulas are powerful but not perfect. They can handle up to about 1,048,576 rows, but complex formulas on huge datasets can slow down your workbook. Some functions like VLOOKUP only look to the right – if you need to look left, use INDEX/MATCH or XLOOKUP (in newer versions).

Also, formulas do not automatically update when you insert or delete rows unless you use structured references (like Table1[Column1]) or dynamic array formulas (like =FILTER).

Frequently Asked Questions

What is the difference between a function and a formula?

A formula is any expression that starts with =, like =A1+A2. A function is a predefined formula, like =SUM(A1:A2). All functions are formulas, but not all formulas use functions.

How do I copy a formula down a column without changing the cell references?

Use absolute references with dollar signs, like =SUM($A$1:$A$10). The dollar sign locks the column, row, or both. Press F4 while editing a reference to cycle through absolute and relative options.

Why does my VLOOKUP return #N/A?

#N/A means the lookup value was not found in the first column of the table. Check for extra spaces, different text formats (text vs. number), or that the lookup column is actually the first column in the range.

Can I use a formula to combine text from multiple cells?

Yes. Use =CONCAT(A1," ",B1) or the older =A1&" "&B1. Both join the text with a space. For a range, use =TEXTJOIN(" ",TRUE,A1:A10) in newer Excel.

How do I calculate the percentage change between two numbers?

Use =(New-Old)/Old. Format the cell as a percentage. For example, if old is 50 and new is 75, =(75-50)/50 gives 0.5, which displays as 50%.

What is the fastest way to learn a new function?

Type = and the function name, then press Ctrl+Shift+A to insert the argument names as placeholders. Also, use the Insert Function button (fx) next to the formula bar to browse by category.

Related

• Tailscale Exit Nodes vs VPNs: Privacy, Speed, Setup
• Micro LED Vertical Stacking: Key Facts
• Large Screen Android One Handed: Tips & Modes

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2