How to Write Formulas in Excel: A Beginner's Guide to Functions & Calculations

How to Write Formulas in Excel

A formula in Excel is an expression that performs a calculation and always begins with an equals sign (=), which tells Excel that the characters you type should be evaluated rather than stored as plain text. Writing formulas is the most frequently repeated task when you work through calculation-based exercises in a spreadsheet, so getting the basic record right matters.

It helps to separate two terms that people often blur together. A formula is the whole expression you enter in a cell (for example, =A5+C6), while a function is a named, pre-built routine such as SUM or AVERAGE that you call inside a formula. Every function can be part of a formula, but not every formula contains a function — a plain =2+3 is a formula with no function at all.

Starting a Formula with the Equals Sign

Every Excel formula starts with the equals sign, and omitting it is the single most common beginner mistake. Type = first, then the rest of the expression; Excel immediately switches into formula mode, treating what follows as something to calculate. If you type A5+C6 without the leading equals sign, Excel simply stores that text in the cell and shows it verbatim. Excel also accepts a leading plus sign (+) for compatibility with older spreadsheet programs, but the equals sign is the standard and the convention you should adopt.

Basic Arithmetic Operators in Excel (+, -, *, /)

Excel uses four basic arithmetic operators, entered as single keyboard symbols, to build the bulk of everyday formulas. These operators connect numbers, cell references, and functions into a calculation:

• + — addition, for example =A1+B1
• - — subtraction, for example =A1-B1
• * — multiplication (the asterisk, not the letter x), for example =A1*B1
• / — division, for example =A1/B1
• ^ — exponentiation, for example =A1^2 raises the value in A1 to the power of 2

A useful property of Excel is automatic recalculation: whenever you change a value in a cell that a formula references, Excel re-evaluates every dependent formula instantly. If A5 holds 2 and C6 holds 3, the formula =A5+C6 shows 5; change A5 to 10 and the result updates to 13 without any further action. This live updating is what makes a spreadsheet a calculation tool rather than a static record.

Creating Your First Formula Step-by-Step

To write the formula 2+3 in Excel using cell references, place each number in its own cell and then reference those cells in the formula. The procedure is short and applies to almost every formula you will ever build:

1. Type the number 2 into a cell — say A5 — and press Enter.
2. Type the number 3 into another cell, for example C6.
3. Click the cell where you want the result to appear.
4. Type =A5+C6 and press Enter. The result, 5, appears in the cell while the formula stays visible in the formula bar.

You can write formulas almost anywhere in the worksheet, with one sensible exception: do not place a formula in a cell that holds raw data the formula depends on. In the example above, cells A5 and C6 contain the source values, so building the formula there would create a circular reference and an error.

Using Parentheses to Control Calculation Order

Parentheses change the order in which Excel performs the parts of a calculation, forcing the enclosed operation to run first. Only round parentheses are permitted — square brackets and braces are reserved for other purposes. You may nest parentheses to any depth, but the number of opening parentheses must exactly match the number of closing ones, or Excel rejects the formula. For instance, =(A1+A2)*A3 adds A1 and A2 before multiplying, whereas =A1+A2*A3 multiplies first and gives a different answer.

Order of Operations in Excel Formulas

Excel evaluates operators in a fixed precedence order, the same convention used in standard mathematics, and parentheses override that order wherever you place them. When several operators share the same priority, Excel works from left to right. The precedence, from highest to lowest, is:

1. Parentheses ( ) — always evaluated first, innermost set outward
2. Exponentiation ^
3. Multiplication * and division / (equal priority)
4. Addition + and subtraction - (equal priority)

So =2+3*4 returns 14, because multiplication runs before addition, while =(2+3)*4 returns 20. When in doubt, add parentheses: they cost nothing and make your intent explicit to anyone reading the formula later.

Understanding Cell References and Addressing

A cell reference is the address Excel uses to locate a value, and choosing the right type of reference determines how a formula behaves when you copy it. Each cell sits at the intersection of a column and a row, and the way you write that address — relative, absolute, or mixed — controls whether the reference shifts or stays fixed as the formula moves. Mastering references is what separates a one-off calculation from a formula you can reuse across hundreds of rows.

A1 Reference Style

The A1 reference style is Excel's default, naming each cell by its column letter followed by its row number — A1, B2, C6, and so on. Columns run A to Z, then AA to AZ, and onward, while rows are numbered. This style is intuitive because the address reads directly off the grid headers. Excel also offers an alternative R1C1 reference style, in which both columns and rows are numbered and a cell is written as RnCn — for example R6C3 is the cell at row 6, column 3 (the same cell as C6). You switch styles under File ▸ Options ▸ Formulas ▸ "R1C1 reference style"; most users keep A1, but R1C1 can make column-relative offsets easier to read in macro code.

Relative References

A relative reference, written plainly as A1, describes a cell's position relative to the formula and adjusts automatically when the formula is copied elsewhere. If cell D2 contains =B2+C2 and you copy it down to D3, Excel rewrites it as =B3+C3, shifting both references one row to match. This automatic adjustment is exactly what you want when applying the same calculation across a column of records, and it is the default behaviour of every reference you type.

Absolute References ($A$1)

An absolute reference, written with dollar signs as $A$1, locks onto one specific cell so the address never changes no matter where you copy the formula. The dollar sign before the column letter freezes the column, and the dollar sign before the row number freezes the row. Absolute references are essential when a formula must always point at a single fixed value — a tax rate in cell E1, for example. With =B2*$E$1 copied down a column, the B reference advances row by row while $E$1 keeps pointing at the rate. Pressing F4 while editing a reference cycles it through the absolute and mixed forms.

Mixed References

A mixed reference locks either the column or the row but not both, combining relative and absolute behaviour in one address. Write $A1 to fix the column while letting the row adjust, or A$1 to fix the row while letting the column adjust. Mixed references shine in two-way grids such as multiplication tables, where copying a single formula across and down should pull headers from a fixed column on the left and a fixed row along the top simultaneously.

How References Adjust When Copying Formulas

When you copy a formula, Excel adjusts each reference according to its type, which is the whole reason the three reference styles exist. Relative parts shift by the same number of rows and columns you moved the formula; absolute parts (dollar-locked) stay put; mixed parts shift only in the unlocked dimension. The table below summarises the behaviour when a formula is copied from one cell to another:

3-D References Across Multiple Worksheets

A 3-D reference lets one formula reach across several worksheets at once, which is invaluable for consolidating data spread over monthly or departmental sheets. You write the worksheet name, an exclamation mark, then the cell address — for example =Sheet2!B5 pulls the value from cell B5 on Sheet2. To sum the same cell across a span of sheets, name the first and last sheets with a colon between them: =SUM(Jan:Dec!B5) adds cell B5 on every sheet from Jan through Dec. Because 3-D references depend on sheet position, inserting or moving a worksheet inside the named range automatically includes or excludes it from the calculation — useful, but worth watching when you reorganise a workbook.

Using Constants in Formulas

A constant is a fixed value typed directly into a formula rather than referenced from a cell, and Excel happily mixes constants with cell references. In =A5*1.2 the number 1.2 is a constant applied to whatever value sits in A5. Constants are convenient for one-off factors, but a value you will reuse or revise — a VAT rate, a conversion factor — is better stored in its own cell and referenced absolutely, so you update it in one place instead of hunting through every formula.

Working with Cell Ranges in Formulas

A range is a block of cells treated as a single argument, and most functions operate on ranges rather than individual cells. You specify a range by its boundaries, which lets a single formula act on dozens or thousands of values at once. Ranges are the foundation of every aggregating function, from SUM to AVERAGE to COUNT.

Selecting and Specifying a Range (Colon and Semicolon)

Excel uses the colon to define a continuous range and the semicolon (a comma in many regional settings) to separate distinct arguments. A solid, unbroken range is written as the two outermost cell addresses joined by a colon — A8:C8 covers A8, B8, and C8. When a function takes several separate arguments or ranges, you list them separated by semicolons. The addresses of the cells holding the argument values always go after the function name, inside its parentheses.

Using Multiple Subranges in One Formula

You can combine several subranges in a single function by separating each with a semicolon, mixing continuous ranges and individual cells freely. For example, =SUM(A8:C8;E9:G9;K12) adds the numbers in cells A8, B8, C8, E9, F9, G9, and K12 — two continuous ranges plus one standalone cell, all summed by one formula. This flexibility means you rarely need to break a calculation across multiple cells just because the source data is scattered.

Using Built-in Functions in Excel

Functions are pre-built, named routines that perform calculations you would find tedious to write by hand, and Excel ships with hundreds of them across mathematics, statistics, text, logic, and lookup categories. Beyond the four basic arithmetic operators, this library is where the real power of the program lives. Each function follows a special mnemonic naming convention — the natural logarithm of the gamma function is GAMMALN, the cosine is COS, and the logical test is IF — so once you know the name you can call the function inside any formula.

Function Syntax and Naming Conventions

Every Excel function follows the same syntax: an equals sign, the function name, then a pair of parentheses enclosing the arguments. The general form is =NAME(argument1; argument2; …). Arguments are the inputs the function needs — they may be numbers, cell references, ranges, text, or even other functions nested inside. The addresses of the cells containing the argument values are given after the function name in parentheses; if an argument is a range, the cells are listed with semicolons, and a solid range is shown by its two outermost addresses joined with a colon. Some functions take no arguments at all but still require the empty parentheses, such as =TODAY().

The SUM Function

SUM adds all the numbers in the ranges or cells you give it and is the most-used function in Excel. Its syntax is =SUM(number1; number2; …), where each argument can be a single cell, a range, or a constant. Common applications include:

• =SUM(B2:B20) — total a column of figures
• =SUM(B2:B20; D2:D20) — total two separate columns in one formula
• =SUM(B2:B20)/COUNT(B2:B20) — combine SUM with another function to derive an average manually

The AutoSum button on the ribbon (the Σ symbol) writes a SUM formula for the adjacent range automatically, which is the quickest way to total a row or column.

The AVERAGE Function

AVERAGE returns the arithmetic mean of its arguments, dividing the sum of the values by how many there are. Written as =AVERAGE(B2:B20), it ignores empty cells and text but counts zeros, which is an important distinction when your data has gaps. For a conditional mean, AVERAGEIF averages only the cells that meet a criterion — =AVERAGEIF(C2:C20;"North";B2:B20) averages the figures in column B only for rows where column C reads "North". Pair AVERAGE with ROUND when you need a tidy result: =ROUND(AVERAGE(B2:B20);2) rounds the mean to two decimal places.

The COUNT and COUNTA Functions

COUNT tallies how many cells in a range contain numbers, while COUNTA counts cells that contain anything at all, including text. Use =COUNT(B2:B20) to count numeric entries — handy for finding how many records actually hold a value — and =COUNTA(B2:B20) when you want every non-empty cell regardless of type. A related function, COUNTBLANK, returns the number of empty cells, which together with COUNTA gives you a quick completeness check on a dataset.

Conditional Functions: IF, SUMIF, and COUNTIF

Conditional functions act only on cells that satisfy a test, letting one formula aggregate selectively rather than blindly. They are among the most practical tools for everyday analysis:

• IF — returns one result if a condition is true and another if false: =IF(B2>100;"Over";"Under").
• COUNTIF — counts cells matching a single criterion. =COUNTIF(C2:C20;"North") counts how many cells read "North", and the same function handles text-based filtering and date-based counting, e.g. =COUNTIF(D2:D20;">="&DATE(2024;1;1)).
• SUMIF — sums cells that meet one condition: =SUMIF(C2:C20;"North";B2:B20) totals column B for "North" rows only.

For multiple criteria, Excel provides the plural forms COUNTIFS and SUMIFS, which test several conditions at once — =SUMIFS(B2:B20;C2:C20;"North";D2:D20;">=100") sums only the rows that are both "North" and at least 100. When you need a weighted total or a count across several conditions in one expression, SUMPRODUCT multiplies corresponding arrays and adds the results, making it a versatile tool for conditional aggregation that predates the IFS family.

Advanced Array Operations

Modern Excel introduces dynamic array functions that return multiple results which automatically "spill" into neighbouring cells from a single formula. Available in Microsoft 365, these functions transform how you filter, sort, and de-duplicate data without dragging formulas:

• FILTER — extracts the rows of a range that meet a condition: =FILTER(A2:C20;C2:C20="North") returns every "North" record as a spilled block.
• SORT — reorders a range by one or more columns, for example =SORT(A2:C20;3;-1) sorts descending by the third column.
• UNIQUE — returns the distinct values from a range, ideal for building a clean list from messy data.
• GROUPBY — aggregates rows by category in a single step, summarising data much like a lightweight pivot table.
• XLOOKUP and VLOOKUP — look up a value in a table and return a matching value; XLOOKUP is the flexible modern replacement for the older VLOOKUP, searching in any direction and handling "not found" cleanly.

For genuinely bespoke logic, the LET function names intermediate calculations to make long formulas readable, and LAMBDA lets you define your own reusable custom function entirely in the formula language — Microsoft added LAMBDA so that repetitive logic can be packaged once and called by name across a workbook.

Using the Insert Function Wizard

The Insert Function dialog box guides you through building a function without memorising its syntax, which is the easiest route for unfamiliar functions. To open it, type the = sign and then choose the Insert Function command from the menu or click the corresponding toolbar button (the fx icon beside the formula bar).

Entering Cell Addresses with the Mouse

You can supply cell addresses to a formula by pointing with the mouse instead of typing them, which reduces errors and speeds up entry. Make sure the cursor sits in the required line of the dialog box, then click a single cell or drag across a block to mark a range. The first line of the dialog box displays the addresses of the marked cells and a new line appears; click in it with the left mouse button to add another argument. Repeat as needed, or click "OK" to transfer the data from the dialog box into the active cell.

Complex Formula Examples

Real-world spreadsheets often combine several functions, operators, and reference types into one formula, and reading these compound expressions is a skill worth practising. A few illustrative examples show how the pieces fit together:

• =ROUND(AVERAGE(B2:B20)*$E$1;2) — averages a range, multiplies by a fixed rate held absolutely in E1, and rounds to two decimals.
• =IF(SUM(B2:B20)>1000;"Target met";"Keep going") — nests SUM inside IF to return a text verdict based on a running total.
• =SUMIF(C2:C20;"North";B2:B20)/COUNTIF(C2:C20;"North") — divides a conditional sum by a conditional count to compute an average for one category, mirroring what AVERAGEIF does in a single call.
• =SUM(Jan:Dec!B5) — a 3-D reference totalling the same cell across twelve monthly worksheets for cross-worksheet consolidation.

Build complex formulas in layers: get the innermost calculation working first, confirm its result, then wrap the next function around it. This incremental approach makes errors far easier to locate than typing a long expression all at once.

Copying and Pasting Formulas

Copying a formula reuses the same logic across many cells while Excel adjusts the relative references automatically. The fastest method for adjacent cells is the fill handle: select the cell with the formula, then drag the small square at its bottom-right corner across the cells you want to fill. Double-clicking that handle fills down as far as the neighbouring column has data. To copy a formula to a non-adjacent location, use the steps below:

1. Select the cell containing the formula and press Ctrl+C to copy it.
2. Select the destination cell or range.
3. Press Ctrl+V to paste the formula, with relative references shifted to suit the new position.
4. To paste only the calculated results and not the formula, use Paste Special ▸ Values — this is also how you delete a formula while preserving its result.

When you need a reference to stay fixed as you copy, lock it with dollar signs before copying, as described in the absolute references section. Dragging a formula across cells without the right reference types is the usual cause of results that look "off by a row".

Editing Formulas and Cell Contents in Excel

You can edit any formula after entering it, either in the formula bar above the grid or directly inside the cell. Both methods let you correct a typo, change a reference, or extend a calculation without retyping the whole expression. Choose whichever feels quicker for the edit at hand.

Using the Formula Bar

The formula bar displays and edits the full contents of the active cell, making it the safest place to work on long formulas. Click the cell you want to change, then click anywhere in the formula bar and insert or erase the characters you need. Because the bar shows the entire formula at once, it is easier to spot a misplaced parenthesis here than in a narrow cell. Press Enter to confirm the change or Esc to cancel it.

Editing Directly in a Cell with F2

Pressing F2 puts the active cell into edit mode so you can change its contents in place. Make the cell active and press F2; the cursor appears inside the cell at the end of the formula, and Excel highlights each referenced cell in a matching colour, which makes it easy to see exactly which cells the formula uses. This in-cell editing is handy for quick fixes and for visually checking that a formula points where you expect.

Common Formula Errors and Troubleshooting

Excel signals a problem in a formula with an error value beginning with a hash sign, and each code points to a specific cause. Recognising them turns troubleshooting from guesswork into a quick lookup:

• #DIV/0! — division by zero or by an empty cell; wrap the formula in =IFERROR(…;0) or test the divisor first.
• #NAME? — Excel does not recognise a name, usually a misspelled function or unquoted text.
• #VALUE! — a wrong type of argument, such as text where a number is expected.
• #REF! — a reference points to a cell that has been deleted; common after removing rows or columns.
• #N/A — a lookup function such as VLOOKUP or XLOOKUP found no match.
• ##### — not strictly an error; the column is too narrow to display the result, so widen it.

If a formula shows its own text instead of a result, the cell is probably formatted as Text or "Show Formulas" is switched on (Ctrl+`); display the formula deliberately with that same shortcut when you want to audit a sheet. The Formulas ▸ Error Checking and Evaluate Formula tools step through a calculation piece by piece, and the Excel Tech Community run by Microsoft is a reliable place to research stubborn errors. Because Excel runs on Windows PCs across both x86 architecture and ARM architecture devices, the same formulas and error codes behave identically regardless of the underlying hardware.

Visualizing Formula Results with Charts and Graphs

Charts turn the numbers your formulas produce into a visual story, making reports clearer and more persuasive than columns of figures alone. You can graphically display the contents of cells using the Chart Wizard command. Select the block of cells you want to plot, click the "Chart Wizard" icon in the command menu or the "Insert chart" submenu, then follow the wizard's prompts.

Many of the Chart Wizard dialog boxes carry several tabs rather than one, so look through each tab, set the chart parameters one at a time, and watch the preview window update as you go. Once the parameters are right, move to the next window with the "Next" button or finish with "Done"; "Cancel" abandons the chart. Because charts are linked to their source cells, they redraw automatically whenever a formula recalculates, keeping the visual in step with the data.

If you frequently move spreadsheets between programs or need to share results, it helps to know how to open XLSX and XLS files and how Excel data fits alongside other office documents. Many of Excel's interface commands — customise, options, zoom, font, and page setup — are unified with other Windows applications, so the habits you build here carry over to the rest of your office software.