Excel formulas and functions: Essential examples you need to know

by Amber Rolfe

Let’s be honest, staring at a blank spreadsheet can be a little intimidating… 

If you’ve ever felt like you’re looking at the matrix (without the cool sunglasses), you’re definitely not alone. But mastering Microsoft Excel doesn’t require a degree in computer science – in fact, learning just a handful of basic formulas can completely transform how you work. Whether you’re tracking a personal budget or managing complex data for your team, knowing the right functions will save you hours of manual data entry.

To help demystify the elusive spreadsheet, here’s everything you need to know about the most essential Excel formulas and functions – from simple addition to the mighty VLOOKUP:

What are Excel formulas and functions?

People often use the terms ‘formula’ and ‘function’ interchangeably, but they are slightly different things. Here’s what they mean:

• A formula is simply an equation you type into a cell to perform a calculation. For example, typing =A1+B1 is a formula.
• A function is a built-in command that performs a specific, often more complex, calculation for you. For example, =SUM(A1:A10) uses the SUM function to add up a whole range of cells. Simply put, functions are ready-made formulas designed to make your life easier.

Knowing how to use these tools is one of the most valuable office skills you can develop, opening doors for better productivity and serious career development.

Excel formulas grouped by use

To make things easier to digest, these essential tools can be grouped into three main categories based on what you need them to do:

• Calculations: These handle the maths. From an addition in Excel formula to working out a percentage increase, these do the heavy lifting for your numbers.
• Lookups: These act like search engines for your spreadsheet. An Excel lookup formula helps you find and pull data from one place to another.
• Logical formulas: These test whether certain conditions are true or false, allowing you to count or add things up only when specific rules are met.

15 essential Excel formulas and functions

Ready to level up your spreadsheet game? Here are 15 essential tools you need to know, complete with an easy-to-follow formula example for each.

1. SUM

The classic Excel sum formula for adding things up.

• What it does: It adds together all the numbers in a range of cells.
• Formula example: =SUM(A1:A10)
• How to use it: Type the formula, open the bracket, select the cells you want to add together, and close the bracket.
• Example in context: Use it at the bottom of an expenses column to find out exactly how much you spent on coffee this month.

2. Addition

The simplest way to add two or three specific cells together.

• What it does: It acts as a basic addition in Excel formula, adding individual cells without needing a built-in function.
• Formula example: =A1+B1
• How to use it: Type an equals sign, click your first cell, type a plus sign, and click your second cell.
• Example in context: Adding your base salary in cell A1 to your bonus in cell B1 to see your total take-home pay.

3. Subtracting

The standard Excel formula for subtracting one number from another.

• What it does: It subtracts the value of one cell from another.
• Formula example: =A1-B1
• How to use it: Type an equals sign, click the starting number, type a minus sign, and click the number you want to take away.
• Example in context: Subtracting your total expenses from your total income to calculate your remaining budget.

4. Multiplication

The go-to Excel formula for multiplication.

• What it does: It multiplies two or more numbers together using an asterisk.
• Formula example: =A1*B1
• How to use it: Type an equals sign, click your first number, type an asterisk, and click the number you want to multiply it by.
• Example in context: Multiplying the cost of a single item by the number of items sold to find your total revenue.

5. Divide

The quick divide Excel formula.

• What it does: It divides one number by another using a forward slash.
• Formula example: =A1/B1
• How to use it: Type an equals sign, click the number you want to divide, type a forward slash, and click the number you are dividing by.
• Example in context: Dividing a yearly subscription cost by 12 to figure out your monthly payment.

6. AVERAGE

The best average Excel formula to find the middle ground.

• What it does: It calculates the average value of a selected range of numbers.
• Formula example: =AVERAGE(A1:A10)
• How to use it: Type the formula, select the range of cells you want to average, and hit enter.
• Example in context: Finding the average number of sales your team makes in a given week to set realistic targets.

7. Percentage

The basic Excel percentage formula.

• What it does: It helps you calculate percentage Excel figures by dividing a part by the total.
• Formula example: =A1/B1 (then format the cell as a percentage)
• How to use it: Divide the smaller number by the total number, then click the ‘%’ button on your toolbar to format it correctly.
• Example in context: Working out what percentage of your monthly sales target you have achieved so far.

8. Percentage increase

The Excel formula for percentage increase.

• What it does: It calculates how much a number has grown or shrunk compared to its original value.
• Formula example: =(B1-A1)/A1
• How to use it: Subtract the old number (A1) from the new number (B1), then divide that result by the old number. Format as a percentage.
• Example in context: Showing your boss exactly how much website traffic has grown since last month.

9. Annual growth rate (CAGR)

The annual growth rate formula Excel relies on for investments.

• What it does: It calculates the Compound Annual Growth Rate over a specific number of years.
• Formula example: =(B1/A1)^(1/5)-1
• How to use it: Divide the ending value (B1) by the beginning value (A1). Raise that result to the power of one divided by the number of years (e.g. 5), then subtract one.
• Example in context: Working out the average yearly growth of your savings account over a five-year period.

10. IF

The basic logical test.

• What it does: It checks if a condition is met, returning one value if true and another if false.
• Formula example: =IF(A1>=50, ‘Pass’, ‘Fail’)
• How to use it: Set your condition, followed by what Excel should say if it is true, and what it should say if it is false.
• Example in context: Automatically labeling student test scores as a pass or fail based on a minimum score of 50.

11. SUMIF

The selective adder.

• What it does: It sums up values in a range only if they meet a specific criteria.
• Formula example: =SUMIF(A1:A10, ‘Laptops’, B1:B10)
• How to use it: Select the range to check (A1:A10), the criteria you are looking for (‘Laptops’), and the range containing the numbers to add up (B1:B10).
• Example in context: Adding up the total sales revenue, but only for laptops.

12. COUNT

The basic counter.

• What it does: It counts how many cells in a range contain numbers.
• Formula example: =COUNT(A1:A10)
• How to use it: Type the formula and select the range you want to check.
• Example in context: Quickly finding out how many days in a month actually had sales figures recorded.

13. COUNTIF

The selective counter.

• What it does: COUNTIF Excel functions count the number of cells that meet a specific rule.
• Formula example: =COUNTIF(A1:A10, ‘Pending’)
• How to use it: Select the range you want to check, followed by the specific word or number you are looking for.
• Example in context: Counting exactly how many invoices in your spreadsheet are currently marked as ‘Pending’.

14. VLOOKUP

The classic search tool.

• What it does: The VLOOKUP formula searches for a value in the first column of a table and returns a value in the same row from a different column.
• Formula example: =VLOOKUP(A1, B1:D10, 3, FALSE)
• How to use it: Tell Excel what to look for (A1), where to look (B1:D10), which column holds the answer (3), and whether you need an exact match (FALSE).
• Example in context: Typing in a product code to automatically pull up the item’s price from a massive inventory list.

15. INDEX MATCH

The flexible upgrade to VLOOKUP.

• What it does: An Excel index and match combo uses the Excel index function and the MATCH function together to look up values in any direction, unlike VLOOKUP which only looks left to right.
• Formula example: =INDEX(C1:C10, MATCH(A1, B1:B10, 0))
• How to use it: Use MATCH to find the position of a value, and INDEX to retrieve the data from that exact position in another column.
• Example in context: Finding an employee’s ID number when you only know their last name, even if the ID column is to the left of the name column.

16. CONCATENATE

The text joiner.

• What it does: Concatenate Excel functions take text from different cells and merge them together into one cell.
• Formula example: =CONCATENATE(A1, ‘ ‘, B1)
• How to use it: Select the first cell, add a space enclosed in quote marks so the words do not run together, and select the second cell.
• Example in context: Joining a column of first names and a column of last names into a single ‘Full Name’ column.

17. TODAY

The automatic date stamper.

• What it does: It inserts today’s date in Excel, and updates automatically every time you open the workbook.
• Formula example: =TODAY()
• How to use it: Just type the formula exactly as shown with empty brackets.
• Example in context: Using it on an invoice template so the document always shows the current date when you print it.

18. Conditional formatting

The visual highlighter.

• What it does: While not technically a formula, conditional formatting Excel tools change the colour of a cell based on its value.
• Formula example: N/A – This is a tool found on the Home ribbon menu.
• How to use it: Highlight your cells, click ‘Conditional Formatting’, and set a rule (like ‘Highlight cell if greater than 100’).
• Example in context: Making any negative numbers in your budget spreadsheet automatically turn red so they grab your attention.

Tips for learning Excel

If you want to boost your CV skills and impress future employers, mastering these formulas is a great place to start. Here’s our advice: 

• Practise hands-on: Open a blank spreadsheet and try each formula using your own made-up data – because real learning happens by doing, not just watching.
• Go one formula at a time: Don’t rush to learn everything at once; tackling formulas individually helps you build solid skills (and keeps your brain from short-circuiting).
• Embrace mistakes: Error messages are part of the journey, and fixing them gives you genuine Excel confidence (even if you wish Excel spoke plain English).
• Use free resources: Dive into online tutorials, forums, and step-by-step courses – many are totally free and perfect for both beginners and the Excel-curious.
• Repeat, repeat, repeat: The more you experiment and repeat steps, the more natural Excel feels – soon you’ll be using shortcuts just for fun (seriously, it happens).
• Ask for help: If you’re stuck, don’t panic. Forums are full of Excel experts who are happy to lend a hand – or at least point out that rogue extra bracket.

Stick to these tips, and you’ll go from Excel newbie to spreadsheet wizard before you can say ‘SUMIF’.

Frequently asked questions

What are the most important Excel formulas to learn?

For beginners, the most important tools to learn are SUM, AVERAGE, COUNT, and basic arithmetic (addition, subtraction, multiplication, and division). Once you are comfortable with those, move on to logical formulas like IF and SUMIF, followed by lookups like VLOOKUP.

What is VLOOKUP used for?

VLOOKUP is used to find specific information in a large table of data. You give it a known value (like a product ID), and it searches vertically down a column to find that ID, then looks across the row to give you related information (like the product’s price).

What is the difference between VLOOKUP and INDEX MATCH?

VLOOKUP is simpler but limited because it can only search for data from left to right. INDEX MATCH is a combination of two functions that is slightly more complex to write, but much more powerful because it can look up data in any direction across your spreadsheet.

How do you calculate percentages in Excel?

To calculate a percentage, divide the smaller number by the total number using the forward slash (e.g. =A1/B1). Then, select the cell and click the percentage style button on the Home ribbon to format the decimal as a proper percentage.

What is the easiest way to learn Excel?

The easiest way is through hands-on practice. Start by learning excel online free through video tutorials and articles. Create your own spreadsheets for everyday tasks like budgeting or tracking a hobby.

Need more help? Take a Microsoft Excel course today.