Skip main navigation

New offer! Get 30% off one whole year of Unlimited learning. Subscribe for just £249.99 £174.99. New subscribers only. T&Cs apply

Find out more

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.


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

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

Reach your personal and professional goals

Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.

Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.

Start Learning now