Skip main navigation

Creating formulas in Excel

How to create 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.

Arithmetic operator and meaning Example Result
+ Addition =4+4 8
– Subtraction or negation =3-1 or =-1 2 or -1
* Multiplication =4 * 4 16
/ Division =4/2 2
% Percent =40% 0.4
^ Exponentiation =4^4 256

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:

Formula Output Explanation
=1+2 3 Excel expects numbers for arithmetic operators and automatically converts text values to numbers.
=1+$2.00 3 Excel converts text to numbers if the text is in a format that would usually be accepted as a number.
=6/1/2001-5/1/2001 0.0005 Excel interprets the text as a date (mm/dd/yyyy), converts the dates to serial numbers, and then calculates the difference between them.
1+2 1+2 Excel treats this as text rather than converting it to a number as there is no equal sign (=) preceding it.

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.

Screenshot showing a formula in Microsoft Excel

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

Screenshot showing the result of a formula using relative references in Microsoft Excel

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.

Screenshot showing the result of a formula

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.

Absolute reference Behaviour
$B$2 The column and the row do not change when copied.
B$2 The row does not change when copied.
$B2 The column does not change when copied.

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 %.

Screenshot of a formula with an absolute reference Microsoft Excel

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.

Screenshot of a formula with an absolute reference Microsoft Excel

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

This article is from the free online

Introduction to Data Science for Business

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education