Creating formulas in Excel

In Excel, you can use formulas to populate the value of a cell. Formulas can be used to perform numeric calculations, such as:

• adding or multiplying
• altering text (e.g. removing white space and splitting values)
• testing logical expressions.

For now, let’s look at how formulas are constructed and how to use Excel to calculate the measures of central tendency and measures of spread to describe a data set. Formulas always start with an equal sign (=), and are constructed with a combination of cell references, constants, operators, and functions.

Operators

Excel has four types of calculation operators: arithmetic, comparison, text concatenation, and reference. The arithmetic operators available are shown in the table below.

Constants

Constants are values entered directly into the formula (e.g. 2, 200, 2.2, or “2”.) Excel expects a specific type of value for each operator. If you enter one that is not expected, Excel might try to convert the value for you.

The table below illustrates some common examples:

Cell references

Earlier, you learned how to create references. Cell references can also be used in formulas (e.g. ‘=A2*B1/2’). This will multiply the value in cell A2 by the value in B1, and divide the result by two. When you press Enter/Return, the value will display in the cell.

To see or edit the formula, click on a cell and the formula will appear in the formula bar above. You’ll notice that Excel highlights cell references in different colours to help you find them in the sheet.

When you reference cells, there are two types of reference: relative and absolute. Let’s understand the difference between both through an example.

Relative referencing

In this example, we want to calculate the total for each line item. Starting at the top, in cell D2, the formula would be: =B2*C2

Instead of having to type the formula into every cell, relative referencing enables us to copy the formula by expanding the bottom right-hand corner downwards across all required rows. This automatically adjusts the formula in each cell to represent the correct row.

Absolute referencing

However, sometimes you don’t want a cell reference to change when you’re filling cells. Unlike relative references, absolute references don’t change when copied or filled. You can use an absolute reference to keep a row and/or column constant. An absolute reference is designated in a formula by the addition of a dollar sign ($), which can precede the column reference, the row reference, or both.

Let’s look at an example in which we want to calculate the sales tax $-value of each line item, given the tax rate in %.

We type in the formula, highlight the cell reference we want to turn absolute, press F4 until the correct format appears, and press Enter for the result to appear. Now we can expand the cell overall rows (until Row 6) to get the sales tax $-amount for each line item.

Next, let’s learn about the various functions available in Excel.