Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Workplace Software and Skills

9.3 Calculations and Basic Formulas in Microsoft Excel

Workplace Software and Skills9.3 Calculations and Basic Formulas in Microsoft Excel

Learning Objectives

By the end of this section, you will be able to:

  • Create basic formulas
  • Add numbers in Excel using a formula or function
  • Subtract numbers in Excel using a formula or function
  • Multiply numbers in Excel using a formula or function
  • Divide numbers in Excel using a formula or function

At WorldCorp, you manage several account teams, each of which has a sales team. Part of your job is to compare the sales agents’ revenues using some basic data analysis, which you can extend to other applications in your job. To assess the performance of the agents and the sales team, you will need to create some basic formulas. These formulas could include the total sales during a specific time period by agent or the average sales per time period for the whole team. These values can then be used to put together a report of the performance of your entire team. Upper management compiles the reports from all the teams to summarize the company’s performance. Using basic formulas in Microsoft Excel expedites the process and helps management to uniformly analyze the information.

Setting Up Basic Formulas

Now that you have learned how to set up a table or spreadsheet, you can perform calculations on the data. First, select the cell where you want the calculation to appear. Use the Formula Bar located below the ribbon to type in the formula or function, starting with the “=” sign (Figure 9.17).

Excel ribbon with the Home tab selected and a spreadsheet. Cell H2 is selected and shows “$26,016.00” and “=E2*G2” typed in the formula bar. Currency is selected in the Number category.
Figure 9.17 Formulas must begin with the “=” sign and contain mathematical operators (i.e., +, -, *, /). (Used with permission from Microsoft)

Adding Numbers

You have learned the two ways to add quantities in Excel: formulas and functions. We can use WorldCorp’s sales data to illustrate how to add numbers. Figure 9.18a contains a set of sales data, with a row at the bottom for the total of all the sales but with the total amount missing. To fill in this empty cell, first, type “=” to open the calculation procedure. Then, click on the first cell you want to add, type a “+” sign, then click on the next cell in the column. Continue this process until all the cells you want to add are included. Figure 9.18b shows the finished formula that includes all the cells in the columns. Notice that Excel adds colors to the cells to help you follow the formulas.

(a) Cell E9 displays Total; blank cell F9 selected. (b) Cell F9 selected; formula bar displays =F3+F4+F5+F6+F7+F8; cells in F column are highlighted in different colors that correspond to cells in formula.
Figure 9.18 You can use a formula to find the sum of a set of cells. (a) The table has a cell for the total sales. First, type an “=” sign, then use the cell references or click on the cells and a plus sign to construct the formula. (b) The finished formula shows all of the cells referenced. (Used with permission from Microsoft)

When you have a small set of data, or when the numbers you are adding using the “+” operator are not all in one row or column, using an addition formula may be the best way to find your sum. However, Excel has built-in shortcuts, such as the SUM function. Functions can often save you time over performing the same calculations using formulas. Like formulas, functions must begin with an equals sign, but rather than using cell references to build an equation, you type the function name, in this case, SUM (Figure 9.19). Then, type an open parenthesis, insert the cell references, separated by commas, and then close the parentheses. You can click on each cell individually or type its cell reference, as in Figure 9.20, but you’ll notice that this method does not save time because you are still entering or clicking each cell reference. Functions allow users to reference individual cells, but they also allow us to choose multiple cells at the same time, which does save time. To select a range of cells, which is a group of cells that are contiguous, you can type in the function and opening parenthesis, and then highlight the cells you want to add. You can also type in the range, separating the first cell and the last cell with a colon. Figure 9.21 shows the range as F3:F11. Functions are often faster to type than formulas, but sometimes you will need to use both formulas and functions to perform complex calculations.

Cell F12 selected; displays =sum. A pane displays options for: SUM (selected), SUMIF, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2. A text box reads: Adds all the numbers in a range of cells.
Figure 9.19 Functions are time-saving methods for performing calculations on data. To find the sum of a range of cells, type the equals sign and “SUM.” (Used with permission from Microsoft)
Cell F12 selected; Formula bar reads: =sum(F3,F4,F5,F6,F7,F8,F9,F10,F11. Cells F3-F11 are highlighted in different colors corresponding to their selection. A box reads: SUM[number1, [number2], [number3], [number4], [number5], [number6], [number7], [number8], [number9], [number10], …).
Figure 9.20 Then, choose the cells you want to include. (Used with permission from Microsoft)
Cell F12 and the formula bar show “=sum(f3:f11.” Cells F3 through F11 are selected. A small text box shows: SUM(number1, [number2], …).
Figure 9.21 Using a range, rather than listing individual cells, can save you time. (Used with permission from Microsoft)

Subtracting Numbers

Understanding how to add numbers in Excel makes subtracting them rather intuitive. If you wanted to subtract many cells in a column, you could type out a long subtraction equation, starting with the equals sign and using cell references and the hyphen key, for example, “=F3-F4-F5-F6-F7-F8-F9-F10-F11.” Using a new set of data, you can see in Figure 9.22 that the margin is the price minus the cost. You can carry this formula down the rows in the same column.

Cell D4 is selected and shows 85.00. The formula bar says “=B4-C4.” B4 says $170.00 and C4 says 85.00.
Figure 9.22 Subtraction in Excel uses formulas that are similar to addition formulas, but there is no subtraction function. (Used with permission from Microsoft)

Multiplying Numbers

With multiplication, formulas and functions start getting more complicated. Sometimes, you may need to multiply the values of two or more cells, and other times, you may need to multiply a cell value by a constant value such as a percentage. One may be more efficient than the other, depending on the data. In Figure 9.23, the price per unit is the same for every row, so you can use cell references to multiply the quantity cell by the price-per-unit cell. Because this table contains only one product, the unit price does not change, and it can be tedious to enter the same price for each sale. You can enter the price per unit in the first row and then click and drag the value to the bottom of your table. Another method would be to simplify your table by eliminating the price-per-unit column and including the value in the formula Figure 9.24. Condensing a table in this manner is helpful, especially if you have a large table of data. To construct a multiplication formula, start with an equals sign and use an asterisk (*) as the mathematical operator.

Cell G4 is selected and says “$2,128.00.” The formula bar shows “=D4*F4.” Cell D4 says 76 and cell F4 says 28.
Figure 9.23 For multiplication, use cell references to construct formulas. (Used with permission from Microsoft)
Cell F3 says $280.00. The formula bar says “=D3*28.” Cell D3 says 10.
Figure 9.24 You can also use constants to construct formulas. (Used with permission from Microsoft)

For multiplication, you can also use the PRODUCT function. Figure 9.25 shows an example of the function syntax. When you have entered all of the cell references, close the parentheses, and the product will be the same as it would if you had used a formula.

Cell G3 is selected and says “$280.00.” The formula bar shows “=PRODUCT(D3, F3)”. Cell D3 says 10, and cell F3 says 28.
Figure 9.25 You can use the PRODUCT function for multiplication. (Used with permission from Microsoft)

Dividing Numbers

Division calculations use the operator “/” and follow a similar syntax as other basic mathematical formulas. Figure 9.26a shows an example of division for determining increases or decreases in prices of aluminum. As you can see, the gain on 1/10/2021 was 2.36 percent. Excel does have a QUOTIENT function, but it returns only whole numbers, leaving off the decimal portion of the quotient (Figure 9.26b). It may be useful for some situations, but formulas provide more exact answers.

(a) Cell E4 is selected (0.0236) and formula bar reads: =D4/C4. (b) Column E displays information with a % sign (i.e., 2.36%).
Figure 9.26 The division formula uses a slash and returns (a) a quotient with decimal places, but you can format it to be (b) a percentage. (Used with permission from Microsoft)

This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at
Citation information

© Jan 3, 2024 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.