For anyone who is completely new to Excel or is wanting to venture into the world of formulas and functions, this post is here to give you some basic tips and tricks to get started. I’ve been surprised numerous times by people attending training who have been using Excel for many years but are still performing the calculation side of their work using a desktop calculator. I have found this is usually because the user either does not know where or how to begin or has tried in the past but doesn’t trust the answers that Excel provides.
Let’s dive in and explore some basic tips to get Excel doing the hard work for you!
Formula vs Functions
As you can see by my post so far, I’ve made reference to the phrase “formulas and functions”. You will see these phrases thrown around when using Excel so below I’ve given an overview of the difference between these two terms:
|Formula||A formula is a calculation created by the user. Formulas can be simple or complex depending on the user’s needs. A formula may contain cell references, functions, a named range, or a set value. All formulas must begin with the equals (=) sign.||=(A1+C3)*10|
|Function||A function is a specific code used in Excel with a specific purpose. A function can be used inside a formula or as a stand-alone calculation. All functions must begin with the equals (=) sign.||=AVERAGE(A4:A28)|
As you can see above, both formulas and functions require the use of the equals (=) sign at the beginning of the calculation. This is one thing that I tend to repeat during my courses quite a bit so that participants remember this by the end of the day. If you were to enter a formula or function into a cell without the equals (=) sign, Excel will enter the information in as normal text and not perform any calculations using the information. The equals sign is what tells Excel to use the content to perform a calculation so it is an important part of the process.
The Formula bar
Another important part of working with formulas and functions is the use of the Formula bar. The formula bar allows you to view the formula or function being used within a specific cell. You can use the formula bar to view how existing calculations have been created, edit functions or formulas and also provides easy access to the Function wizard. This is very useful if you have inherited a workbook from another user and you want to see how or where they have used formulas and functions to build the workbook.
The Formula Bar is located above the cell area:
When the time comes where you are creating more complex calculations, you have the ability to increase the height of the formula bar by clicking the down arrow to the far right-hand side of the bar. This will increase the height of the bar and allow you to view long complex calculations much more easily.
I have seen many instances where a user’s formula bar has disappeared, potentially after some erratic mouse movements or seemingly on its own. In order to get the bar back again click on the View tab and tick the option for Formula bar within the Show group.
Always use cell references
When creating formula or functions it is a good idea to use the cell reference which contains a value within the calculation rather than typing the actual value into the formula or function. By using the cell reference rather than the value, you provide numerous advantages including the ability to click on cells to include them in a calculation; if the value of a cell changes the calculation will automatically update, and formulas will automatically copy or can be duplicated.
In the example below, you can see on the left side that the calculation being created in cell B9 is using the actual values in the formula. If these values were to change at any time I would need to go through my formulas and manually update each value every time I use it. On the right side you can see that instead of using the actual values, I have used the cell reference of the cell containing the value I want to add together. If any value changes I just change it once within the cell which holds the value and all calculations using that will automatically update.
Order of operations
Last but certainly not least is having an understanding of how Excel handles calculations. The Order of Operations refers to the order in which formulas which contain more than one type of calculation are completed. You may remember back to your school days sitting in Maths and listening to your teacher talking about an acronym named BODMAS. BODMAS stands for:
Bracket Order, Division, Multiplication, Addition, Subtraction
Essentially BODMAS is the order in which Excel will calculate values together and in my experience a lack of understanding of how BODMAS is incorporated into Excel is the main reason behind users being unable to trust the answers that Excel can sometimes give.
In the example below, you will see the calculation on the left is adding together the values in cells B3, B4 and B5, it then divides the result by 3 to see the average of the income over the quarter. With the values for all three months being around the $3,000-$5200 range, it is not logical for the result to be $9,218.33 so we can see there is an issue with the calculation. If we go through the BODMAS order, Excel is first calculating the value of B5/3 and then adding the values in B3 and B4. So the calculation becomes $2995 divide by 3 plus $3,000 plus $5220 = $9,218.33. If we incorporate the BODMAS theory we can use brackets, or parenthesis, to force Excel to calculate the addition portion of the calculation first and then perform the division second. In the example on the right, we have added brackets around B3+B4+B5 and now the answer is correct.
Note: There are quicker and easier ways to perform a simple addition, using the SUM function, however for the purpose of this exercise I have created the calculation as shown.
Using these few tips you now have the starting information you need to begin creating your own formulas and functions in Excel.
If you want to continue and venture into the world of formulas you may like to see the following posts: