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 Google Sheets that the text following the equal sign is a formula.
A variety of mathematical operators are available in Google Sheets. 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.
Copying the practice workbook
If you are beginning this course at this point, you will need to copy the practice workbook to your Google Drive.
NOTE: If you have been working through the course, your file should be up to date and you will not need to copy the practice document. You may continue at Entering a formula.
To begin copying the practice workbook, click the following button:
The file begins to open in a web browser. Before the document will open and can be edited, we need to make a copy of it.
To copy the workbook file,
ClickÂ
A copy of the file is now open and, by default, is saved in your Google Drive. The filename now has the words "Copy of" at the beginning.
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 Google Sheets, it is always recommended to use cell references when 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 and C2.
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, Google Sheets begins to give you visual cues:
Cell B2 is outlined and highlighted, the reference to B2 is the same color as the highlighted cell, and the value of cell B2 appears above the reference.
Let's continue writing the formula.
To finish the formula, type:
* C2 Enter key
The item sales for the tote bag, $405.00, appears in cell D2. Because the value in cell B2 was formatted as currency, Google Sheets automatically applies the currency formatting to the formula result.
Examining the formula bar
In Google Sheets, the formula bar appears under the toolbar. The formula bar shows us what is happening behind the scene in Google Sheets. 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 the cell:
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 menu bar,
Click Edit, Click Copy
When text or data is copied in Google Sheets, a dotted line appears around the copied area.
Now we need to tell Google Sheets where to paste this copied data.
To select cells D3:D6,
Press & drag cells D3:D6
To paste the formula, on the menu bar,
Click Edit, Click Paste
To deselect the cells,
Click in a blank area of the worksheet
The formula has been copied to cells D3:D6.
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.
