Basic Calculations
The true power of the Microsoft Excel becomes apparent when you perform calculations on data entered into a spreadsheet. Excel can perform basic calculations like addition and subtraction or very complicated calculations such as payment schedules and financial ratios. The level of complexity depends on what information you need to obtain from the data.
Before you can begin to create a formula, you must first decide which cell will display the answer. Don’t be intimidated with the term formula – it is simply a set of instructions, usually directing the computer to calculate data. It is not uncommon for Excel enthusiasts to use the terms formula and calculation (or calc) interchangeably. Using the mouse, position the mouse pointer over the desired cell and click the left mouse button. A thick black boarder will appear around the cell indicating the cell has been selected. After the cell has been selected, you can begin to enter the formula.
Begin the new formula by typing either an equal = sign or a plus + sign. Either of these symbols at the beginning of the line indicates that you are about to create a formula. The equal = sign and plus + sign are located on the top line of your keyboard (just to the left of your backspace key). You may also use the plus + key located on your number pad at the far right of your keyboard. Once you have typed in the equal = sign or a plus + sign, you can enter or select the values you want to use in the calculation.
NOTE: Although it does not matter whether you use an equal = or plus + sign to start your formula, Excel will automatically change the first symbol to an equal = sign when you leave the cell.
NOTE: The plus + sign is called a mathematical operator. It instructs the computer to add the numbers. Other mathematical operators are the minus – sign (subtract), the asterisk * (multiply), and the slash / (divide).
Creating a Formula by Manually Typing in Values
The easiest way to learn how to create a formula in Excel is to walk through an example. Using the sample budget, you will calculate the total income for January. Move your mouse pointer to cell B7 and click the left mouse button to select the cell. You will calculate the answer by typing in the income values (numbers) manually. To tell the computer you are going to create a formula, type in an equal = sign. After the equal sign, type in the values for the calculation manually. Between each number, type a plus sign to indicate you want to add the numbers together. The example calculation for the month of January is =2500+850+50+0. After you have typed in all the values, press the Enter key on the keyboard to tell the computer to perform the calculation. The total 3400 will appear in cell B7.
To view the equation for the cell, click the mouse on the cell B7. Look at the Formula Bar near the top of the Excel window and to the right of the Name Box. The Formula Bar will display the entire mathematical equation you typed in the cell.
Manually Typing a Formula: Step by Step Instructions
- Click on the cell in which you want to place a formula.
- Type an “equal sign” (=) to indicate that you are creating a formula.
- Type in the formula manually (example: =300+50+200+800).
- Press the Enter key on the keyboard. The computer will calculate the answer.
Viewing a Formula: Step by Step Instructions
- Click on the cell displaying the total/answer. (This is the same cell in which the formula was initially typed.)
- Look at Formula Bar to view the equation.
Manually Typing a Formula: Try It Out
Congratulations you have created your first formula and completed your first calculation in Excel. The total income for January is 3400. Manually typing in the values used in a mathematical equation is very easy, but can lead to future problems on your spreadsheet. The problem arises when one of the values used in the formula changes. Consider the example budget, what if Salary 1 changed to 3500 instead of 2500. The total income calculated for January would be incorrect unless you remembered to manually correct the formula.
Test this out. Go back and select cell B3 with your mouse. Change the number 2500 to 3500. Note that the total income displayed in cell B7 did not change. The total for January still shows 3400 even though the correct answer is now 4400. To obtain the correct total, you must retype the formula to include the correct value for Salary 1. The mathematical formula in cell B7 must be changed to =3500+850+50+0 to calculate the correct total income for January. To avoid the problems associated with manually entering values (numbers) into calculations, Excel allows you to use cell references, rather than values when creating formulas. If you changed the value in cell B3 to 3500, please change cell B3 back to 2500 before continuing.
NOTE: When you type values (numbers) into a formula, the answer will not change automatically when you change one of the numbers in the cells you are totaling.
Creating a Formula by Manually Inputting Cell References
When creating a formula, the best practice is to reference the cells containing the values, rather than to type in the actual numbers. Remember each cell on a spreadsheet has a name. The cell name is the combination of the letter of the column and the number of the row where the cell is located. The cell’s name can be used to reference any cell on the spreadsheet. If you use cell references in a formula, you can change the values in the cells, and the system will automatically update the answer. This concept might seem confusing, so it’s best to look at an example.
To calculate the total income for the month of February, you will add the income values 2500, 850, 55, and 0. These values are located on the spreadsheet in cells C3, C4, C5, and C6 respectively. If you were going to calculate the total by typing in the values, the formula would be =2500+850+55+0. The answer for the calculation is 3405. If, instead, you use the cell reference method, you would type in the cell names instead of the specific values. The formula using cell references would be =C3+C4+C5+C6. The answer for the calculation is still 3405. The answers are identical. However, if you have to go back and change the value for Salary 1 located in cell C3 from 2500 to 3500, your calculation will automatically update to 4405 because you used cell references when you created the formula.
Using the sample budget, calculate the total income for February. Move your mouse pointer to cell C7 and click the left mouse button to select the cell. You are going to calculate the answer by manually typing the income cell references. To tell the computer you are creating a formula, type in an equal = sign. After the equal sign, type the names of the cells for the calculation. Between each cell name, type a plus sign to indicate you want to add the numbers together. The example formula for February is =C3+C4+C5+C6. Press the Enter key on the keyboard when done to tell the computer to perform the calculation. The total 3405 will appear in cell C7. Now go back to cell C3 and change the number to 3500. Note that the total automatically changes to 4405 as soon as you change the number in cell C3. This is your first encounter with the extraordinary power of Excel.
Creating a Formula by Selecting Cells
A slight variation to the previous method of creating a formula is to use the mouse to select each cell you want included in your calculation. You can try this method to calculate the total income for the month of March.
Move your mouse pointer to cell D7, and click the left mouse button to select the cell. You are going to select the cells used in the formula. To tell the computer you are going to create a formula, type an equal = sign. Instead of typing in the values or cell references, move the mouse to cell D3, the first cell you want to add, and press your left mouse button. The cell reference will now appear in the formula. After each cell selection, type a plus + sign to indicate you want to add the numbers together. Position the mouse over cell D4, the next cell you want to add to the calculation, and click the left mouse button. The cell reference has been added to the formula. Type a plus + sign. Continue to add cells D5 and D6 to the formula. Remember to type the plus sign between the cell references. After you have chosen the last cell, press the Enter key on the keyboard. (Make sure you end the formula with a cell reference and NOT a plus sign. If you end with a plus sign, the computer will tell you that you made an error and will not perform the calculation.) Once you have completed the formula and hit the Enter key, your total, 3500, will appear in cell D7.
Selecting the cells using the mouse is typically easier and faster than typing the cell references manually, but you can use whichever process is easier for you. The results are the same.
Creating a Formula by Selecting Cells: Step by Step Instructions
- Click on the cell in which you want to place a formula.
- Type in an “equal sign” (=) to indicate that you are entering a formula.
- Select a cell with your mouse.
- Type in a mathematical operator (+,-,*, /).
- Repeat steps 3 and 4 to add the remainder of the cells for the calculation.
- Press the Enter key on the keyboard. The computer will calculate the answer.
Practice Creating Formulas by Selecting Cells
It is important to become comfortable creating formulas using the data on the spreadsheet. Before moving on to the next topic, use what you have learned to create new formulas to finish the example spreadsheet. First, you will total the expenses for each month and then calculate the net income for each month. Since selecting cells using the mouse is the most common way to create a formula, you should practice using this technique.
To begin, position the mouse over cell B15 and click the left mouse button. Cell B15 is now selected to display the answer to the calculation totaling January’s expenses. To tell the computer you are going to create a formula, type an equal = sign. Move the mouse to cell B10, the first cell you want to add, and press your left mouse button. The cell reference will appear in the formula. Between each additional cell, type a plus + sign to indicate you want to add the cells together. Position the mouse over cell B11, the next cell you want add to the equation and click the left mouse button. The cell reference will be added to the formula. Continue to add cells B12, B13, and B14 to the formula. Remember to type the plus + sign between each cell reference. After you have chosen the last cell, press the enter key on the keyboard. Your total, 3500, will appear in cell B15.
Repeat the steps to calculate the total expenses for February, March, and April. February’s total expenses should be placed in cell C15, March’s total should appear in D15, and April’s total should be placed in cell E15.
You have successfully completed totaling the expenses for each month on the example spreadsheet. With the revenue and expenses totaled, the last formula you need to add to the sample spreadsheet will calculate the Net Income for each month. To correctly calculate the Net Income, you have to subtract your total monthly expenses from your total monthly revenue. For example, for the month of January you will subtract the expense total in cell B15 from the revenue total displayed in cell B7.
Position the mouse over cell B16 and click the left mouse button. Tell the computer you are going to create a formula by typing an equal = sign. Using the mouse, select cell B7 which contains the total revenue for January. The cell reference will appear in the formula. Since you are going to subtract the total expenses, type a minus (-) sign to indicate you want to subtract a number. Position the mouse over cell B15 and click the left mouse button. Press the Enter key. A Net Income of 1980 will appear in cell B16.
Copying and Pasting Formulas
The budget is going to utilize the same formula to generate the net income for each month as was used to calculate January’s net income. Instead of retyping the formula in cells C16, D16, and E16, you can use the Copy and Paste option. The copy and paste option will allow you to select a cell containing a formula, copy the formula into the computer’s memory, and then paste the copied formula into another cell on the spreadsheet. Since you used cell references in the formula, the computer will automatically adjust the cell references to the column/row they are related to when the formula is pasted into the new cells.
In the example, you are going to use the mouse to select and copy the formula in cell B16. Move the mouse pointer over cell B16 and press the left mouse button. Once the cell has been selected, position the mouse pointer over the COPY option, located in the Clipboard section of the Home ribbon, and click the left mouse button one time. The selected cell’s formula will be copied into the computer’s memory. Take a moment to look at the selected cell. The cell border is moving, indicating that you have successfully copied the data contained in the cell.
Move your mouse pointer over cell C16 and click your left mouse button. The bold, black selection box will appear around cell C16 indicating the cell has been selected. Once the cell has been selected, position the mouse pointer over the PASTE option, located in the Clipboard section of the Home ribbon, and click the left mouse button one time. Notice a value appears in cell C16. The formula used in cell B16 has been copied to cell C16 and the net income for February has been calculated. Remember the net income for February will not be the same as the net income for January because the income and expenses for February are different from January’s numbers.
To verify that the formula was pasted into the cell, look at the Formula Bar located at the top of the screen. You will see the equation used to calculate the net income for February. Notice the cell references automatically updated to use the cells C7 and C15 in the calculation for February. Copying and Pasting calculations is another example of the power of Excel.
You still need to calculate the net income for the months of March and April. Since the calculation used in cell B16 is still in the computer’s memory, move the mouse pointer to cell D16 and click the left mouse button. The bold, black selection box will appear around the cell D16 indicating the cell has been selected. Once the cell has been selected, position the mouse pointer over the Paste option, located in the Clipboard section of the Home ribbon, and click the left mouse button one time. Notice the calculation from cell B16 has been copied into cell D16.
Repeat the steps to paste the net income calculation in cell E16, and you will have completed the calculations on the example budget.
NOTE: Mathematical operations used in calculations:
- Addition: Use the plus + symbol
- Subtraction: Use the minus – symbol
- Multiplication: Use the asterisk * symbol
- Division: Use the slash / symbol
NOTE: Each time you change one of the values in a calculation which utilizes cell references, the answer will update automatically.
Review: Copying and Pasting: Step by Step Instructions
- Select the cell containing the formula you want to copy.
- Click the COPY option located in the Clipboard section of the Home ribbon.
- Select the cell where you want the copied formula to be placed.
- Click the PASTE option located in the Clipboard section of the Home ribbon.