While a spreadsheet is a great place to store and organize data, the real power of spreadsheets comes with formulas. A formula performs a mathematical calculation. These calculations can be simple or complex.
A formula always begins with an equal sign (=). This tells Microsoft Excel that the text following the equal sign is a formula.
A variety of mathematical operators are available in Excel. These include: + (addition), - (subtraction), * (multiplication), / (division), and ^ (exponentiation). These operators can be combined with values, cell references, and functions (to be discussed in the next section) to create a formula.
Entering a formula
In this spreadsheet, column D, Item Sales, is currently empty, as shown in the following image:
We will use a formula to calculate the total amount of sales for each item.
When working in Microsoft Excel, it is recommended to use cell references whenever possible. In this example, instead of using the values of $15.00 and 27 to calculate the sales of tote bags, we will use the corresponding cell references: B2 (Item Price) and C2 (Number Sold).
By using cell references instead of the values in the cell, our spreadsheet will automatically adjust if changes are made. Maybe the price of the tote bags goes up to $17.00 or maybe we realize there were 17 bags purchased instead of 27. If we use cell references, our formulas will not have to be recreated if changes are made.
Let's create a formula to calculate the total sales for the tote bags.
To make cell D2 the active cell,
Click in cell D2
To begin entering the formula, type:
=B2
As you type, Microsoft Excel begins to give you visual cues:
Cell B2 is outlined and highlighted, and the reference to B2 in the formula is the same color as the highlighted cell.
Let's continue writing the formula.
To finish the formula, type:
* C2 Enter key
The item sales for the tote bag, $405.00, appear in cell D2. Because the value in cell B2 was formatted as currency, Excel automatically applies the currency formatting to the formula result.
Examining the Formula bar
In Microsoft Excel, the Formula bar appears under the ribbon. The Formula bar shows us what is happening behind the scene in Excel. In this case, the Formula bar will show us the formula contained in the cell while the result is displayed in the cell.
After pressing the Enter key to enter the formula into cell D2, our active cell became cell D3. Let's move back to D2 and examine the Formula bar.
To move to cell D2, on the keyboard, press:
Up Arrow key
We can now see the formula in the Formula bar and the formula result in cell D2:
Copying a formula
To use this formula in other cells, we could retype it each time, or we could copy and paste it. Let's copy the formula from cell D2 and paste it into cells D3:D6.
To copy the formula in cell D2, on the Home tab,
Click
When text or data is copied in Microsoft Excel, a dotted line appears around the copied area.
Now we need to tell Excel where to paste this copied data. We can paste data into multiple cells at the same time by selecting the cells before pasting the data.
To select cells D3:D6,
Press & Drag cells D3:D6
To paste the formula into the selected cells, on the Home tab,
Click
To deselect the cells,
Click in a blank area of the worksheet
To remove the outline around cell D2, on the keyboard, press:
Escape key
The formula has been copied to cells D3:D6, and we now see the formula results in those cells.
Exploring relative references
Copying a formula down a column works because the cell references used in the formula are relative. This means as the formula moves down each row, the cell references adjust to reflect the new location. Let's see how this works.
To make cell D3 the active cell,
Click in cell D3
While the value of cell D3, $456.00, appears in the cell, the formula =B3*C3, is visible in the Formula bar. The formula has adjusted to reflect the new row.
To inspect the formula in the other cells in column D, on the keyboard, press:
Down Arrow key
In each row, the formula has adjusted to reflect the location.
Before we move on to exploring functions, let's save the workbook.
