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.
To begin copying the practice workbook in a new browser window,
The file begins to open in a new window of the web browser. Before the document will open and can be edited, we need to make a copy of it.
Move to the new browser window.
To copy the workbook file, in the browser window,
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, the Item Sales column (column D) 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 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 for the price and C2 for the number of items 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, Google Sheets begins to give you visual cues:
Cell B2 is outlined and highlighted, the reference to B2 in the formula is the same color as the highlighted cell, and the value of cell B2 appears in a pop-up 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, appear 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 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 into the other cells we want to use it in. 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. 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 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, 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.
