As part of our series of helping clients with their small business needs Longmont Computer Physicians, LLC is offering these free classes on how to use different software programs. Here is our instructional video on using Microsoft Excel spreadsheets.

Microsoft 365 Beginner class – Excel
Microsoft 365 Intermediate class – Excel
Microsoft 365 Advanced class – Excel

You use formulas to perform mathematical functions on cells in Microsoft Excel. There are two basic ways of writing formulas: “ranged syntax” or “simple syntax.” A “syntax” is simply a way of expressing or writing something. It is important to note that these two syntaxes are not mutually exclusive! In fact, more complex formulas often incorporate elements of both to arrive at the desired result. Typically, you use the simple syntax to perform multiple mathematical calculations on multiple cells. You use the ranged syntax to perform a single mathematical function over multiple cells. To manually write a formula, first click into the cell where you want the results of the formula to appear. Next, write your formula. When finished, exit the cell to display the answer to the formula you wrote. Once again, this is the concept of “content versus display” at work. The actual content of the cell is the formula, but the cell displays the answer to the formula. If you click into the formula cell after creating it, the actual formula appears in the Formula Bar and the answer appears in the worksheet cell. To show the actual formulas in the worksheet, you can press the “Ctrl” key and the ` (single left quotation mark) key. When manually creating ranged syntax formulas, first select the cell into which to enter the formula. Then type the equal sign.

Formulas always start with an equal sign (=). This prevents Excel from interpreting the formula as a simple text entry, since formulas are just letter/number combinations. Next, type the name of the function to perform on the cell range or cell ranges. Then type an open parenthesis. Next, type the cell range or cell ranges upon which to perform the function. Finally, type a closed parenthesis. Then exit the cell using your keyboard or click the checkmark button in the Formula Bar to set the formula. Note that you don’t use spaces between elements in the formula. However, they are not case sensitive. Manually creating a simple formula in Excel is like writing a standard math problem. Once again, start by selecting the cell where you want to enter the formula. Type an equal sign (=). Then type the cell addresses to use in the formula and join them together with the standard mathematical operators. You can also enter standard numbers into a formula, if desired. However, you will more often want to manipulate the values of the cells, which may change, and so you will more often use cell references in Excel, instead. If you want to perform a single calculation on a range of cells, it is usually easier to use the ranged formula syntax instead of the simple formula syntax. In simple formulas, the standard order of operations applies: designated operations are performed from left to right with anything in parenthesis calculated first, then exponentiation, then multiplication and division, and finally, addition and subtraction.

Note that in Excel you can also use both syntaxes within a single formula. For example, to sum the first ten cells in column A and then subtract from that value the sum of the first ten cells in column B, you could express it as a single combined formula, like: =SUM(A1:A10)-SUM(B1:B10). In this case, you are using a simple syntax formula to subtract the values derived from two ranged syntax formulas. Most of the more complex formulas you create incorporate both syntaxes to derive the answer. Formulas that use cell references are automatically recalculated when you enter or change the values in the cells referenced by the formula. This is one of the best features of Excel and one of the reasons you will rarely find numbers manually entered into a formula. When you use cell addresses in formulas, you can either type them or use the mouse to select the cell or cell ranges to add. As always, after writing the formula, leave its cell to save it and display its answer with Excel.