Formulas for beginners is one of the hardest parts of Excel. 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 get started and create some simple formulas of your own. Welcome to this post.
1. What you need to know about every formula (part 1)
For example, =1+2+3+4 and =A1+A2+A3 are both valid formulas.
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. What you need to know about formulas (part 2)
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.
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.
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.
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.
4. How to edit a formula
Any of the following techniques can be used to edit the contents of a cell.
5. How to use Smart Tags
When there is an error, a small coloured triangle is placed in the corner of the cell.
When the cell is selected, a Smart tag appears.
When the Smart tag is clicked, options are provided to help resolve the error.
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
Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item!
ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.
Enjoy the rest of your day!
About the author
Jason Morrell
Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!
SHARE
