Reading Time: minutes remaining

Excel Formulas for Beginners: Bring Your Spreadsheet to Life

Jason Morrell

by Jason Morrell 
January 17, 2022

For beginners, formulas are one of the hardest parts of Excel to master. At least in your head! 

Formulas simply allow you to manipulate date in Excel. That could be something as simple as adding up a series of numbers or at the other extreme, formulas can be used for complicated forecasting or technical engineering equations.

In this post you'll discover how to write formulas for beginners; how to get started and create some simple formulas of your own. Welcome to this post.

1. What every formula has

  • Every formula must start with a '=' (found next to the BACKSPACE key).
  • The usual mathematical operators can be used

    +    (add)
    -    (subtract)
    *    (multiply - don't use x)
    /    (divide - there is no ÷ symbol on the keyboard!)
  • Formulas can use a mixture of

    ■    Plain numbers, called Constants (e.g. 5)
    ■    Cell references (e.g. A3)
    ■    Cell ranges (e.g. A1:E5)
  • Formulas can also include functions which simplify things once you get deeper into Excel.

For example, =1+2+3+4 and =A1+A2+A3 are both valid formulas.

As is =SUM(A1:A3).

Be careful. The = is on the same key as the + and it’s easy to type something like =2=2 instead of =2+2. In this instance, the two numbers won’t be added, but rather they will be compared against each other to see if they are the same. If they match, the answer is TRUE. If they don’t match, the answer is FALSE.

2. Watch the order. Memorise B.E.D.M.A.S

The steps of a formula are always calculated in a set order. These are called the Order of Operations and they were established in the 17th century to ensure everybody sings from the same sheet and any ambiguity is eliminated.

Here's an example.

Many people think the answer to =2+3*4 is 20, when in fact the correct answer is 14.

Let me explain.

The BEDMAS acronym shows the order in which parts of a formula are calculated. Excel uses symbols to represent mathematical operators. The table below explains what BEDMAS stands for and shows what symbols Excel uses.

  • B = Brackets      ( )
  • E = Exponentiation (to the power of)     ^
  • D = Division     /
  • M = Multiplication     *
  • A = Addition     +
  • S = Subtraction     -

Some examples:

  • The equation 2 + 2 * 2 + 2 gives an answer of 8, not 10.
  • The equation 100 – 50 * 2 gives an answer of zero, not 100.
  • The equation 2 + 3 * 4 gives an answer of 14, not 20.

Let's break this last one down. 

Following the BEDMAS sequence, the multiplication segment (3 * 4) is calculated before the addition segment. So, 3 * 4 = 12, then 2 is added to give an answer of 14.

We could change the formula to give an answer of 20 by adding brackets.

(2 + 3) * 4 equals 20, because the part inside the - brackets is done first.

3. How to enter a formula in Excel

Method 1: Type directly into a cell

Single-click a blank cell, then type a formula. This overwrites the existing contents of the cell.

How to enter a formula

OR

Method 2: Use the formula bar

The formula bar is the large white box located between the toolbars and the column headers and to the right of the  button.

How to enter a formula

Select a cell, click in the formula bar and type a formula.

When you are entering or modifying a formula, a cross (X) and a tick (✓) appear next to the Fx button on the formula bar.

  • Click the ✓ or press Enter to confirm the formula.
  • Click the X or press Esc to cancel any changes you have made.

4. How to edit a formula

Any of the following techniques can be used to edit the contents of a cell.

  • Double click a cell to edit the formula within the cell.
  • Single-click a cell and press F2 to edit the formula within the celł.
  • Single-click a cell then click in the formula bar to edit the formula there.

5. How to use Smart Tags

When there is an error, a small coloured triangle is placed in the corner of the cell.

Formulas for beginners often result in errors, indicated by a coloured triangle in the corner of the cell

When the cell is selected, a Smart tag appears.

Formulas for beginners often result in errors. Smart tags tell you why

When the Smart tag is clicked, options are provided to help resolve the error.

Smart tags give the reason for the error so you can easily address the issue

6. Common errors in beginners formulas

#DIV/0! Error

Formula is attempting to divide by zero. Check that cell references are not empty and do not contain zero.

#NUM! Error

The result of the formula is invalid (e.g. =1000^1000 cannot be represented as the answer is too large.

#REF! Error

Formula is attempting to reference a cell that is no longer valid. Check that the referenced cell has not been deleted or moved.

NB. Not to alarm you, but these are just three of the most common errors. There are many other errors which you will probably come across, given enough time!

7. Key Takeaways

  • To create a new formula, select a cell and start typing.
  • To accept a formula, press ✓ or Enter.
  • To cancel a formula, press X or Esc (Escape).
  • To edit a formula, double-click cell or select a cell and press F2 or click directly in the formula bar.
  • Every formula must start with '='.
  • Every formula will work according to the BEDMAS sequence.
  • To find the cause of an error click the cell with the error then click the smart tag next to it.

I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.

Have a fantastic day.

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.


He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.


Purely for amusement he sometimes talks about himself in the third person.

SHARE

more from

Office Mastery logo
Read More
Excel Screen: A Beginners Tour
Read More
5 Excel Functions for Beginners: Simplify Your Formulas and Release the Magic

JOIN THE DISCUSSION

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>