The real power of a spreadsheet begins with formulas. A formula performs a calculation in order to obtain a result. A formula must begin with an equal sign (=). It uses standard mathematical symbols to operate on cell addresses. When the data changes in those cells, the formula updates automatically. A formula can consist of mathematical operators, cell references, values or text, or functions (which will be discussed a little later).
Using Operators in Formulas
A variety of operators are available for use in Excel. The following table lists some operators that can be used in formulas:
|+||Addition||=A1+A2||Adds the values in cells A1 and A2.|
|-||Subtraction||=A1-A2||Subtracts the value in cell A2 from the value in cell A1.|
|*||Multiplication||=A1*.05||Multiplies the value in cell A1 by.05.|
|/||Division||=A1/12||Divides the value in cell A1 by 12.|
|^||Exponentiation||=A1^2||Result will be the value in cell A1 taken to the 2nd power.|
There are several methods that can be used when entering a formula:
- Formulas can be entered by typing. Type an equal sign (=) followed by the formula.
- Formulas can also be entered by typing an equal sign in the active cell, clicking the mouse on the cells you want to include in the formula, and typing the operator characters.
- Cells or ranges that we have designated by custom names can be inserted or pasted into formulas. We will learn about these specially named ranges later in the workshop.
Next, we will enter a formula by typing the formula directly in a cell.
Entering a Formula
We will enter a formula to calculate the total sales for each item, then we will copy and paste it to the other cells.
Step1. To make sure G3 is the active cell,
Click cell G3
Step2. To begin entering the formula, type:
NOTE: When you begin entering this formula, the Formula AutoComplete feature provides additional assistance by displaying a list of function and/or range names that begin with the letter "C." You will use this feature later in the workshop.
Step3. To finish entering the formula, type:
NOTE: You may also Click in the cell rather than type the specific cell reference.
Copying and Pasting the Formula
Now we can copy and paste the formula in G3 to the empty cells in G4:G9.
Step1. To make G3 the active cell,
Click cell G3
Step2. To switch to the Home tab, if necessary,
Click the Home tab
Step3. To copy the formula, in the Clipboard group,
NOTE: You can also use the keyboard shortcut Control key+C to copy the formula.
NOTE for MacOS Users: To copy the formula, press: Command key+c.
Step4. To highlight the cells where we want to paste the formula,
Press & Drag cells G4:G9
NOTE: In Excel 2016 for Windows, you may see a Quick Analysis icon that appears to the right of the selection: . By clicking on this icon, you can quickly analyze your data and apply conditional formatting, or insert charts, totals, or tables.
Step5. To paste the copied formula into the highlighted cells,
NOTE: You can also use the keyboard shortcut Control key+v to paste the formula. You can also select where you would like to have the contents be pasted and press Enter.
Step6. To view the Paste Options menu,
Step7. To cancel the menu, press:
Step8. To remove the data from the clipboard and turn off Cut & Copy mode, press:
Step9. To deselect the cells,
Click another cell