Creating a Basic Formula

We use formulas to calculate various mathematical outputs in Excel and to create custom calculations.  When constructing a formula in Excel, cell locations become cell references when added to a formula. This means Excel uses, or references, the number entered into the cell location when calculating a mathematical output. As a result, when the numbers in the cell references are changed, Excel automatically produces a new output. This allows Excel to create what-if scenarios, which will be explained later in the chapter.

To demonstrate the construction of a basic formula, we will begin working on the Budget Detail worksheet in the Personal Budget workbook, shown in Figure 2. To complete this worksheet, we will add several formulas and functions. Table 1 defines each spend category listed in the range A3:A11. When you develop a personal budget, these categories are defined based on how you spend your money. Every person could have different categories or define the same categories differently. Therefore, review the definitions in Table 1 to understand how we define these categories before proceeding.

Budget Detail Worksheet with categories and expense plan - percent of total, monthly, annual, and last year spend and %change

Figure 2 Budget Detail Worksheet

Table 1 Spend Category Definitions
Category Definition
Household Utilities Money spent on electricity, heat, water, cable, phone, and Internet access
Food Money spent on groceries, toiletries, and related items
Gasoline Money spent on fuel for automobiles
Clothes Money spent on clothes, shoes, and accessories
Insurance Money spent on homeowner’s or automobile insurance
Taxes Money spent on school and property taxes (this example of the personal budget assumes that we own property).
Entertainment Money spent on entertainment, including dining out, movie and theater tickets, parties, and so on
Vacation Money spent on vacations
Miscellaneous Includes any other spending categories, such as textbooks, software, journals, school or work supplies, and so on


The first formula we will add to the Budget Detail worksheet will calculate the Monthly Spend values. The formula will be constructed by taking the values in the Annual Spend column and dividing them by 12. This will show how much money will be spent per month for each category listed in Column A. The following explains how this formula is created:

  1. Click the Budget Detail worksheet tab to open the worksheet.
  2. Click cell C3.
  3. Type an equal sign (=). When the first character entered into a cell location is an equal sign, it signals Excel to perform a calculation or produce a logical output.
  4. Type D3. This adds D3 to the formula, which is now a cell reference. Excel will produce an output with whatever value is entered into cell D3.
  5. Type the slash symbol (/). This is the symbol for division in Excel. As shown in Table 2, Excel Mathematical Operators, the mathematical operators in Excel are slightly different from those found on a typical calculator.
  6. Type the number 12. This divides the value in cell D3 by 12. In this formula, a number, or constant, is used instead of a cell reference because it will not change. In other words, there will always be 12 months in a year.
  7. Press the ENTER key.

Table 2 Excel Mathematical Operators
Symbol Operation
+ Addition
− Subtraction
/ Division
* Multiplication
^ Power/Exponent


Cell References

Cell references enable Excel to dynamically produce new outputs when one or more inputs in the referenced cells are changed. Cell references also allow you to trace how outputs are being calculated in a formula. As a result, you should never use a calculator to determine a mathematical output and type it into the cell location of a worksheet. Doing so eliminates Excel’s cell-referencing benefits and your ability to trace formulas to determine how outputs are produced.

Figure 3 Adding a Formula to a Worksheet shows how the formula appears in cell C3 before you press the ENTER key.

Figure 4 Formula Output for Monthly Spend shows the formula output after you press the ENTER key. The monthly spend for Household Utilities is $250 because the formula takes the Annual Spend in cell D3 and divides it by 12. If the value in cell D3 changes, the formula automatically produces a new output. We are calculating the spend per month for each category because people often get paid and are billed for these items every month. This formula allows you to compare your monthly income to your monthly bills to determine whether you have enough income to pay these expenses.

Adding a formula to the expense plan worksheet - the formula is =D3/12

Figure 3 Adding a Formula to a Worksheet

Formula Output for Monthly Spend - shows the final figure of $250 in the monthly spend for household utilities.

Figure 4 Formula Output for Monthly Spend


Universal Constants

When using constants, or numerical values, in an Excel formula, they should be universal constants that do not change, such as the number of days in a week, weeks in a year, and so on. Do not type the values in cell locations into an Excel formula. This will eliminate Excel’s cell-referencing benefits, which means if the value in the cell location you are using in a formula is changed, Excel will not be able to produce a new output.