How to Use a Formula in Excel
Using formulas in Excel helps you perform calculations and analyze data effortlessly. In this guide, we’ll walk you through the basics, from entering your first formula to understanding how to use more advanced functions. Let’s dive in!
Step-by-Step Tutorial: How to Use a Formula in Excel
In this section, we’ll go through the steps to help you start using formulas in Excel. By the end, you’ll be able to create simple calculations and even use some complex functions.
Step 1: Open Excel and Select a Cell
Select the cell where you want the result of your formula to appear.
When you open Excel, you’ll see a grid of cells. Pick any cell to start with. This will be the cell where your result will be displayed.
Step 2: Begin with an Equal Sign
Type an equal sign (=) into the selected cell.
The equal sign tells Excel that what follows is a formula. This is the first step in creating any calculation or function.
Step 3: Enter the Formula
Type the formula you need. For example, if you want to add two numbers, type =A1+B1.
Formulas can be simple or complex. For basic arithmetic, you can use the plus (+), minus (-), multiply (*), or divide (/) symbols. The cell references (like A1 and B1) are where Excel gets the numbers from.
Step 4: Press Enter
Hit the Enter key to complete the formula.
When you press Enter, Excel calculates the formula and displays the result in the cell you selected in Step 1. If there’s an error, Excel will let you know.
Step 5: Copy the Formula
Drag the fill handle (a small square at the bottom-right corner of the cell) to copy the formula to adjacent cells.
Dragging the fill handle lets you apply the same formula to other cells, automatically adjusting the cell references. This is super handy for calculating totals and averages for large datasets.
After completing these steps, your spreadsheet should now show the calculated results based on your formula. If done correctly, you’ll see how Excel instantly takes the data you’ve referenced and performs the calculation.
The Parts of an Excel Formula
Every formula starts with an equal sign and can contain four kinds of building blocks, as described in Microsoft’s overview of formulas in Excel:
- Constants: numbers or text typed directly into the formula, such as 10 or "Paid".
- Cell references: addresses like A2 or ranges like A2:A10 that point to values elsewhere in the sheet. When those values change, the formula recalculates automatically.
- Operators: symbols that tell Excel what to do, such as + (add), – (subtract), * (multiply), / (divide), ^ (power) and & (join text).
- Functions: built-in formulas such as SUM or IF that take arguments inside parentheses.
Order of operations
Excel calculates in a fixed order: parentheses first, then exponents, then multiplication and division, then addition and subtraction, working left to right. That means =2+3*4 returns 14, not 20. Add parentheses to control the order, for example =(2+3)*4 returns 20.
How to Enter a Function Without Typing It
If you do not remember a function’s name or arguments, Excel can build it for you:
- AutoSum: select the cell below a column of numbers and press Alt + =, or click Home > AutoSum. Excel guesses the range; press Enter to accept it. The arrow next to AutoSum also offers Average, Count, Max and Min.
- Insert Function: click the fx button next to the formula bar, search for what you want to do, and fill in each argument in the dialog box.
- Formula AutoComplete: start typing, for example
=VL, and Excel lists matching functions. Press Tab to accept one, then follow the tooltip that shows the arguments.
Ten Formulas Every Beginner Should Know
These cover most everyday spreadsheet tasks. Each example assumes data in rows 2 to 10.
- SUM adds a range:
=SUM(B2:B10). See how to add numbers in Excel for other options. - AVERAGE returns the mean:
=AVERAGE(B2:B10). - MIN and MAX find the smallest and largest values:
=MAX(B2:B10). - COUNT and COUNTA count cells with numbers, or any non-empty cells:
=COUNTA(A2:A10). - ROUND rounds to a set number of decimal places:
=ROUND(B2,2). - IF returns one result if a test is true and another if it is false:
=IF(B2>=50,"Pass","Fail"). - SUMIF adds only values that meet a condition:
=SUMIF(A2:A10,"East",B2:B10). Our SUMIF guide covers more examples. - COUNTIF counts cells that meet a condition:
=COUNTIF(A2:A10,"East"). - XLOOKUP finds a value in one column and returns the matching value from another:
=XLOOKUP(E2,A2:A10,B2:B10). It is available in Microsoft 365 and Excel 2021 or later; see how to do an XLOOKUP in Excel. - Text joining with the & operator or CONCAT:
=A2&" "&B2. See how to concatenate in Excel.
How to Edit, View and Check Formulas
- Click a cell and edit its formula in the formula bar, or press F2 to edit directly in the cell. Press Esc to cancel changes.
- Press Ctrl + ` (the grave accent key, usually left of 1) to show every formula on the sheet instead of its result. Press it again to switch back.
- Use Formulas > Trace Precedents to draw arrows to the cells a formula uses, which helps you spot wrong references.
- Use Formulas > Evaluate Formula to step through a complex formula one calculation at a time.
Troubleshooting Common Formula Errors
#DIV/0!
The formula divides by zero or by an empty cell. Check the divisor, or wrap the formula in IFERROR, for example =IFERROR(A2/B2,0).
#NAME?
Excel does not recognize something in the formula, usually a misspelled function name, text without quotation marks, or a function your version does not support.
#VALUE!
The formula is trying to do math with text, such as a number stored as text or a stray space. Check the referenced cells and convert text to numbers if needed.
#REF!
A cell the formula referred to has been deleted. Press Ctrl + Z to undo the deletion, or edit the formula to point to the correct cells.
#N/A
A lookup function could not find the value. Check for spelling differences or extra spaces in the lookup value and the lookup range.
The formula shows as text instead of a result
The cell is formatted as Text or the formula is missing its leading equal sign. Change the format to General, then click in the cell and press Enter.
Results do not update
Calculation may be set to manual. Go to Formulas > Calculation Options and choose Automatic, or press F9 to recalculate now.
Tips for Using Formulas in Excel
- Always start with an equal sign (=).
- Use parentheses to group parts of your formula and manage the order of operations.
- Take advantage of Excel’s built-in functions like SUM, AVERAGE, and VLOOKUP.
- Double-check your cell references to ensure accuracy.
- Use the Formula tab to explore more complex functions and tools.
Frequently Asked Questions
What is a formula in Excel?
A formula in Excel is an expression that calculates the value of a cell. It can include numbers, cell references, and mathematical operators.
Can I use more than one operator in a single formula?
Yes, you can use multiple operators in a single formula. For example, you can add and then multiply in one formula, like =A1+B1*C1.
How do I fix a formula error?
Look for common issues like incorrect cell references or mismatched parentheses. Excel typically provides an error message that can guide you.
What are some common Excel functions?
Common functions include SUM (adds numbers), AVERAGE (calculates the mean), and IF (tests a condition and returns one value if true and another if false).
How do I reference cells from another sheet?
Use the sheet name followed by an exclamation mark and the cell reference, like =Sheet2!A1.
What is the difference between a formula and a function?
A formula is anything that starts with an equal sign and calculates a result, such as =A1+A2. A function is a named, built-in formula such as SUM or IF that you can use inside your own formulas.
Why does my formula show a green triangle in the corner?
That is an error-checking flag. It may mean the formula is inconsistent with nearby formulas or that a number is stored as text. Click the cell and the warning icon to see the reason and suggested fixes.
Relative and Absolute Cell References
One of the most important formula concepts is what happens when you copy a formula. A reference such as A1 is relative and normally changes when copied to another cell. A reference such as $A$1 is absolute and stays fixed. Mixed references such as $A1 or A$1 lock only the column or row. Understanding these references prevents many spreadsheet calculation errors.
Check Formulas Before Copying Them
After filling a formula down or across a range, inspect a few results and formula references. Excel can calculate exactly what you entered even when the reference is not the one you intended.
Related: add numbers in Excel.
Related: how to make an absolute reference in Excel on Mac.
Related: How to Round in Excel.
Related: How to Create a Multiplication Formula in Excel.
Related: how to use absolute cell references in Excel.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.