Mastering Structure: Fundamental Tips on Crafting Formulas in Excel

Quick Links

We’re not all mathematicians, but some tasks in Microsoft Excel are best done using formulas. Maybe you’re new to writing formulas or are trying but keep getting confusing errors . Here, we’ll cover the basics of structuring formulas in Excel.

Parts of a Formula

While the exact elements can vary, a formula can use the following pieces.

Equal Sign: All formulas in Excel, and Google Sheets as well, start with an equal sign (=). Once you type it into a cell, you may immediately see suggestions for functions or formulas.

Cell Reference: While you can type values directly into formulas (as a constant), it’s possible and usually handier to pull values from other cells. An example cell reference is A1, which is the value in column A, row 1. References can be relative, absolute , or mixed.

Relative Reference: This refers to the relative position of the cell. If you use the reference A1 in your formula and change the position of the reference (for example, if you copy and paste the data somewhere else), the formula updates automatically.
Absolute Reference: This refers to a specific position of the cell. If you use the reference $A$1 in your formula and change the position of the reference, the formula does not update automatically.
Mixed Reference: This refers to a relative column and absolute row or vice versa. For instance, if you use A$1 or $A1 in your formula and change the position of the reference, the formula only updates for the relative column or row automatically.

Constant: You can think of a constant as an inserted value. This is a value that you enter directly into the formula instead of or in addition to a cell reference. For example, instead of using A1 in the formula, you might use its value—15.

Operator: This is a special character that performs a task. For instance, the ampersand is the text concatenation operator for combining text strings . Here are a few more:

Arithmetic Operators: These include an asterisk for multiplication and a plus sign for addition.
Comparison Operators: These include a greater than, less than, and equal sign.
Reference Operators: These include a colon to designate a cell range as in A1:A5 and a comma to combine multiple cell ranges as in A1:A5,B1:5.

Parentheses: Like in an algebra equation, you may use parentheses to specify the part of the formula to perform first. For instance, if the formula is

    `=2+2*3`

, the answer is 8 because Excel performs the multiplication portion first. But if you use

    `=(2+2)*3`

, the answer is 12 because the portion within parentheses is performed before the multiplication.

Additionally, functions begin with an opening parenthesis, followed by the arguments (references, values, text, arrays, etc.), and finish with the closing parenthesis. Even if nothing appears in the parentheses as in

    `=TODAY()`

which gives you the current date , you must still include the parentheses.

Function: A common but not required part of a formula is a function . As with our above example the TODAY function provides today’s date. Excel supports many, many functions for working with numbers, text, lookups, information, and much more.

Formula Examples

Now that you know the basic parts of a formula, let’s look at the syntaxes for some examples.

Here is a formula to add the values in two cells . You have the equal sign, first cell reference (relative reference), plus sign (operator), and second reference (relative reference).

=A1+B1

This formula adds distinct values instead. You have the equal sign, first value (constant), plus sign (operator), and second value (constant).

=15+20

For a function example, you can add the values in a cell range. Start with the equal sign, enter the function followed by an opening parenthesis, insert the first cell in the range, a colon (reference operator), last cell in the range, and finish with the closing parenthesis.

=SUM(A1:A5)

Another symbol you may see in a formula is a quotation mark. This is commonly used when creating formulas for working with text , although quotes are not exclusive to text. Here’s an example.

You can use the SUBSTITUTE function in Excel to replace certain text with new text. Using this formula, you can substitute Smith for Jones in cell A1:

=SUBSTITUTE(A1,”Jones”,”Smith”)

As you can see, both the current (Jones) and new (Smith) text are contained within quotation marks.

Get Help From Excel

It can take time and practice to get the hang of writing formulas. Luckily, Excel does offer some help when you’re using functions in your formulas.

Start Your Formula

If you plan to use a function, you can get a jumpstart on the formula.

Select the cell where you want the formula, type the equal sign, and enter the first letter or two of the function you want to use. You’ll see a drop-down list of functions that apply.

Double-click the function you want and you’ll see the syntax for the formula you need to create.

You can then click an argument in the formula and enter or select what you want to use. Follow the formula you see by entering commas or other expected operators until you complete the formula.

Click here

### View the Function Library

Even if you know the function you want, you can take a look at the syntax for the formula ahead of time. This helps you prepare the data if it’s not ready.

Go to the Formulas tab and click “Insert Function” on the left side of the ribbon.

Enter the function into the Search box at the top, hit “Go,” and then select it from the results.

You’ll then see the expected syntax for the function near the bottom of the window. Plus, you get a description of the function for additional help. Below, you can see what you need for the COUNT function .

Hopefully these explanations and tips help you create the formulas you need in Microsoft Excel!

Win Answers

Mastering Structure: Fundamental Tips on Crafting Formulas in Excel