How to Create Formulas in Excel, and What to Learn First

Ah, I remember the first time I laid eyes on Microsoft Excel. It was like walking into a party where everyone spoke a different language. Cells, rows, columns, and these strange creatures called ‘formulas’. I felt like an alien in an unfamiliar world.

But instead of running away (which, believe me, was tempting), I decided to face the beast head-on. And let me tell you, it was the best decision I’ve ever made. Excel formulas, my friends, are not the intimidating monsters they appear to be. In fact, they’re more like loyal sidekicks, always ready to lend a hand when you’re grappling with numbers.

Think of Excel as your Swiss Army Knife for business finances. It’s got a tool for every situation. Need to add up your monthly expenses? There’s a formula for that. Want to find out your average sales for the year? Excel’s got you covered. Curious about the highest revenue you’ve made in a quarter? Yep, there’s a formula for that too!

Here is how formulas work, starting from the equals sign.

Key Takeaways

Learning to create formulas in Excel is quite simple. Start by clicking on the cell where you want the result to appear, type an equals sign (=), and then enter your formula. For example, to add cells A1 and B1, you would type =A1+B1. Press Enter, and voila, your result will appear!

Getting Started, Understanding the Basics

Excel formulas are like the secret sauce that spices up your spreadsheets. They’re essentially instructions that you give to Excel to perform calculations or actions on your data. Think of them as your personal math minions, tirelessly crunching numbers so you don’t have to.

Let’s take a real-life example. Imagine you own a small bakery (mmm… fresh bread). You sell two products: baguettes and croissants. At the end of each day, you want to know how much you’ve earned from selling these delicious goodies. Instead of adding up the numbers manually, you can use an Excel formula to do the job for you.

Here’s how it would look:

=SUM(B2:B5)

This little gem, also known as the Sum Function, is telling Excel to add up all the numbers from cells B2 to B5, where you’ve recorded your daily sales. With just one simple formula, you can say goodbye to tedious manual calculations and hello to instant results!

The Language of Excel, Breaking Down Formula Syntax

Every formula follows the same grammar. Learn the grammar once and you can read a formula somebody else wrote, which is most of the job.

First things first, every Excel formula begins with an equal sign (=). That’s Excel’s way of knowing that you’re about to give it some instructions. It’s like saying, “Hey Excel, listen up!”

Next are the actual instructions, which can combine functions, cell references, numbers, and operators. Think of these as the ingredients in a recipe. Just as flour, sugar, and eggs combine to make a cake, these elements form an Excel formula.

Let’s take a look at a simple sum function: =SUM(A1:A5). Here, SUM is the function (or the action we want Excel to perform), and A1:A5 is the range of cells or cell reference we want the excel function to act upon. This formula tells Excel to add up the numbers in cells A1 through A5.

Remember, just like baking a cake, the order of your ingredients matters. In Excel-speak, we call this order of operations. Excel follows the BIDMAS rule for mathematical operations:

• Brackets
• Indices
• Division and Multiplication (from left to right)
• Addition and Subtraction (from left to right)

Three Ways To Write Formulas

If syntax has you stressed out, never fear! Excel will help you every step of the way. Here are three different ways to write formulas:

1. Manual Input in the Formula Bar

Think of this as the “old school” method. You click on the formula bar, type an equal sign (=), and then start entering your formula directly. For example, if you want to add cells B2 and C2, you’d type =B2+c2 into the formula bar. It’s that easy!

2. The Insert Function Button

For those who prefer a little guidance, the Insert Function Button is like your personal Excel tour guide. Click on the fx button next to the formula bar, and the insert function dialog box will pop up to help you build your formula step by step. It’s like having a GPS for your spreadsheet!

3. Formula Builder

If you’re using Excel on a Mac, you have an extra ace up your sleeve – the Formula Builder. This handy tool is a formula generator that helps you create predefined formulas and provides detailed explanations of each function. It’s like having your own private Excel tutor!

Your First Formulas, Addition, Subtraction, Multiplication, and Division

Start with the four that everything else is built on: addition, subtraction, multiplication and division.

Make sure to download our Excel workbook so you can follow along and practice your skills:

1. Addition

The SUM function is your go-to for adding numbers. Let’s say you have a list of expenses in cells H5 to H10. To add them up, you’d type =SUM(H5:H7) in the cell where you want the total to appear. Press enter and voila! Your total expense is calculated in a jiffy.

Practice: Try adding numbers in cells G5 to G7.

Solution: Your formula should look something like this: =SUM(G5:G7)

2. Subtraction

Now let’s do a simple subtraction formula using the minus sign. Suppose you want to subtract the expense in cell H5 from the income in cell G5. Simply type =G5-H5 and press enter. There you go, easy peasy lemon squeezy!

Practice: Subtract the number in cell H8 from the number in cell G8.

Solution: Your completed formula should look like this: =G8-H8

3. Multiplication

Excel multiplies numbers using an asterisk (). If you want to find out a rough estimate for the full year revenue, multiply G11 by 2 or =G11*2. And just like that, you’ve got your answer!

Practice: Multiply the number in cell H11 by 2 to estimate annual expenses.

Solution: Your formula should be: =H11*2

4. Division

To divide values, we use a forward slash (/). So, if you want to divide the total expenses against the total revenue to calculate margin, you’d type =I11/G11. Hit enter and there’s your answer!

Practice: Divide the number in cell I8 by the number in cell G8.

Solution: Your formula should be: =I8/G8

Going Beyond Basics, Exploring Common Financial Formulas

The next set is the one finance runs on. These are the formulas that turn up in every model you will ever open.

1. SUM

We’ve already met the SUM formula, our handy little helper for adding numbers. But did you know it can also add up a whole column or multiple values in a blink? Let’s say you have your monthly sales figures in cells A2 to A13. Just type =SUM(A2:A13) and boom! Your total sales for the year are calculated instantly.

2. AVERAGE

The AVERAGE function is like that friend who always knows the middle ground. It calculates the average of a set of numbers. For instance, if you want to find the average monthly sales from your yearly sales data in cells A2 to A13, you’d type =AVERAGE(A2:A13). Press enter and there you have it, your average monthly sales!

3. COUNT

Let’s meet COUNT, your personal assistant for counting numbers. If you want to know how many months you had sales (let’s assume any month with a zero doesn’t count), just type =COUNT(A2:A13). And just like that, COUNT tells you how many months had sales.

4. MAX & MIN

Meet MAX and MIN, the dynamic duo that helps you find the highest and lowest values in a set of numbers. Want to know your best and worst sales months? Just type =MAX(A2:A13) for your highest sales month and =MIN(A2:A13) for the lowest. It’s like having your very own financial highs and lows at your fingertips!

Now, let’s practice with a real-life business scenario. Imagine you’re a business owner who wants to track your monthly sales performance. With these formulas, you can easily calculate your total annual sales, average monthly sales, number of months with sales, and your best and worst sales months.

Leveling Up, Conditional Formulas and Lookup Functions

Conditional formulas and lookups are the step where Excel starts doing real work. They take a little longer to click than SUM did, and they are the two you will use most.

1. IF

The IF function is like your own personal genie. It does one thing if a certain condition is true, and something else if it’s not. The syntax goes like this: =IF(condition, value if true, value if false).

Let’s say you’re tracking monthly sales and want to highlight months where sales exceeded $10,000. Your formula would be =IF(A2>10000, "Exceeded", "Not Exceeded"). If the sales in cell A2 are more than $10,000, Excel will return “Exceeded”. If not, it’ll return “Not Exceeded”.

2. VLOOKUP

VLOOKUP is like your own detective. It looks for a specific value in the leftmost column of a table, and then returns a value in the same row from a column you specify. Here’s how you’d do it: =VLOOKUP(lookup value, table array, column index number, [range lookup]).

Imagine you have a product price list in cells A2 to B5, with product names in column A and prices in column B. You want to find the price of a product listed in cell D2. Your formula would be =VLOOKUP(D2, A2:B5, 2, FALSE). This tells Excel to look for the product name in cell D2 within the range A2 to B5, and return the corresponding price from the second column.

3. HLOOKUP

HLOOKUP is VLOOKUP’s cousin, but it looks up data horizontally rather than vertically. The syntax is similar: =HLOOKUP(lookup value, table array, row index number, [range lookup]).

Let’s say you have quarterly sales data in cells A1 to D3, with the quarters in row 1 and sales figures in rows 2 and 3. To find Q2 sales for Year 1 listed in cell A2, you’d type =HLOOKUP("Q2", A1:D3, 2, FALSE). This tells Excel to look for “Q2” in the range A1 to D3, and return the corresponding sales figure from the second row.

What changed now that AI can write the formula

Everything above is still worth learning, and the reason has shifted. It used to be that you learned formula syntax because otherwise you could not write one. Now you can describe what you want in a sentence and get a working formula back in about two seconds.

So why learn it at all? Because you still have to read the thing. A model hands you a nested XLOOKUP with an IFERROR wrapped round it, and you have to decide whether it is right before it goes anywhere near a forecast. That decision needs the grammar on this page.

How the workflow goes now

• Describe the result you want, including what should happen when the lookup fails. Leave that out and you get a formula that returns an unhelpful error on every unmatched row.
• Get the formula, then read it left to right and say what each argument does. If you cannot, ask for it to be rewritten more simply. There is nearly always a simpler version.
• Test it on a row where you already know the answer. This is the step people skip and it catches most of what goes wrong.
• Never let a model type a number into a cell. Have it write the formula and let Excel do the arithmetic. A number a model typed is generated text that happens to contain digits.

The last one matters more than it sounds. A model asked for a total will sometimes produce a plausible total rather than the correct one, and a plausible total in a spreadsheet is indistinguishable from a real one until somebody checks.

For the tools that do this inside the workbook, Claude for Excel is the one I use most, and the three-way head to head compares the three.

Troubleshooting, Fixing Common Formula Errors

Ah, troubleshooting – the unsung hero of mastering Excel. We’ve all been there: you’re typing away, lost in a world of cell references and formulas, when suddenly…#VALUE! Or even worse, #REF! But fear not, my Excel adventurers! Even the most seasoned pros make mistakes. It’s all part of the journey. Let’s look at some common errors and how to fix them:

1. #VALUE!

This error typically pops up when Excel encounters the wrong data type. For example, if you try to subtract a word from a number (I know, sounds silly, but trust me, it happens!). To fix it, double-check your formula to make sure you’re only performing operations on compatible data types.

2. #REF!

The #REF! error usually appears when a formula refers to a cell reference that no longer exists, often because it was deleted. To rectify this, replace the deleted cell reference with a valid one.

3. #DIV/0!

This one’s pretty straightforward – it means you’re trying to divide by zero, which is a big no-no in Excel world. Check your divisor; if it’s zero, you’ll need to change it to a non-zero number.

Now for a little humor, let me share my most epic Excel fail. I was working late one night, trying to calculate the average sales for a client. I typed in my formula, hit enter, and…#DIV/0! I spent hours checking and rechecking my formula, tearing my hair out, before I realized I’d forgotten to input the sales data. Yes, I tried to calculate an average with no numbers. So remember, always double-check your data!