Learning basic formula operations in Microsoft Excel 2019

Here, we will look at formula theory and the order of operations sothat you have a good understanding of how calculations are constructedwithin Excel 2019.

When you enter a formula into a cell, it always starts with an = sign. Once the = sign is typedinto a cell on a worksheet, the Formula Bar will become active, with theformula icons changing color and becoming ready for input.

The Formula Bar is located just above the worksheet column headersand consists of three parts. To the left of the Formula Bar, you willsee a list of commonly used functions and access to the More Functions… icon to locate morefunctions. Note that this part is active while the Formula Bar isactive, after which it is referred to as the Name Box.

The middle section of the Formula Bar is where you will find the iconto cancel formula input, to enter a formula, and to access the InsertFunction dialog box to search for more functions. The main part of theFormula Bar is the formula entry area where you will enter, copy, oredit calculations created in Excel. It can be expanded or collapsed,which is really useful when dealing with lengthy calculations consistingof multiple functions and operators, and can be seen in the followingscreenshot:

ms office 836

A formula is a cell entry that performs a calculation and returns aresult. To enter a formula, we use cell references and seldom enter acalculation, as we would do on a calculator. This is because the powerof performing calculations using references is that the data within thecell reference is often altered by the user, which in turn updates thecalculation and produces different results, depending on data input andworkflow. 

In the following example, the formula is using the addition operator(+) to sum the January sales for all four sales reps. Theanswer to the formula will be placed in cell C12, and therefore thecalculation is performed in that cell in order to return the result (theanswer once the calculation is performed). Although this is the longermethod of adding up in Excel, it shows how cell references are collectedon the worksheet to construct the formula.

After entering the = sign, you would then select the cellreference on the worksheet for the first sales rep by clicking on cellC8, followed by the + operator, then the second sales repcell reference C9, and so on. Once all cell references are selected, theformula is entered into the cell by pressing the Enter key onthe keyboard or the green tick on the Formula Bar. To follow anyexamples in this topic, please use the SSGRegions.xlsxworkbook. Refer to the following screenshot to view the formula inaction:

ms office 298
Note that we never leave an operator at the end of a formula. This willresult in a formula error, as Excel will assume you have not finishedthe formula. We also never use the mouse to click off of a formula toenter it into a cell, as it will assume that you want to include furthercell references and collect these as you click around the worksheetarea. The only way to confirm a formula is complete is to Enterusing the keyboard key or to click on the green tick on theFormula Bar to indicate you are finished constructing the formula andwould like to view the result.

In the preceding screenshot, you will see that Excel usescolor-coding to help you manage your cell references. Just at a glance,you can see which cells are matched with the cell references in theformula.

The following screenshot shows some examples of basic formulaconstruction using operators:

ms office 354

The operators are listed as follows:

  • Add: Type =N7+N8 and then pressEnter
  • Subtract: Type =N7–N8 and then pressEnter
  • Multiply: Type =N7*N8 and then pressEnter
  • Divide: Type = N7/N8 and then pressEnter

Leave a Comment