Embedded Files
Copying Cells
Copying Cells
In this part of the tutorial, you will learn how to fill a cell, or range of cells, with a formula to increase efficiency in data entry.
Do the following:
➤ Create a new Spreadsheet called "Company".
➤ Enter the following data in column A, beginning in Cell A1:
Cell A1: The Book Company
Cell A2: Annual Projections
Cell A5: REVENUE
Cell A6: Sales
Cell A7: Service
Cell A8: Total
Cell A10: EXPENSES
Cell A11: Wages
Cell A12: Supplies
Cell A13: Total
Cell A15: INCOME
Cell A16: Gross
Cell A17: Tax
Cell A18: Net
➤ Select Cell B4 and enter Qtr.
➤ Adjust the column widths as necessary and use bold formatting for the headings
➤ Select the text below the bold faced headings, and align it to the right.
Your spreadsheet should look like the example provided.
Do the following:
➤ Enter the following constants into your workbook:
Cell B6: 46000
Cell B7: 55000
Cell B11: 62000
Cell B12: 22500
➤ Select cell B8 and enter the formula that sums Sales and Services.
=B6+B7 or =SUM(B6:B7)
The formula is summing up the two cells directly above cell B8.
Copying From One Cell to Another Cell
Copying From One Cell to Another Cell
If you look at cell B13, it will need a formula too. The formula in cell B13 will also sum up the two cells directly above it. This is where Sheet's Copy command is useful - you can simply copy the formula from one cell to another cell.
Do the following:
➤ Select cell B8
➤ Use the keyboard shortcut to copy the cell contents, either Ctrl+C for Windows or Command+C for Mac.
Note that Sheets places a marquee around the cell.
➤ Select cell B13
➤ Use the keyboard shortcut to paste the cell contents, either Ctrl+V for Windows or Command+V for Mac.
Your spreadsheet should now look like the example provided.
Observe that when you select cell B13, the formula =B11+B12 or =SUM(B11:B12) will appear in the formula bar. That is, the formula has summed the two cells directly above cell B13.
Copying From One Cell to a Range of Cells
Copying From One Cell to a Range of Cells
You now know how to copy a formula from one cell to another. Next, in order to duplicate the Qtr. heading across columns C through E, you will make a copy from one cell to several other cells.
Do the following:
➤ Select cell B4 and use the keyboard shortcut to Copy its contents
➤ Highlight the range C4:E4
➤ Use the keyboard shortcut to Paste the contents.
➤ Center the Qtr. headings
The next step is to fill in the range C6:E7 with projected revenues.
➤ Select cell C6 and enter the formula =B6*1.08 to calculate an 8% projected increase in revenue, then press Enter
➤ Copy cell C6
➤ Highlight the range C6:E7 and Paste the contents
Notice that when a cell in the C6:E7 range is highlighted, the formula (which can be viewed in the formula bar) multiplies the cell directly to the left of it by 1.08.
➤ Complete the Total row by summing the Sales and Service numbers for each remaining quarter
Copying Cells Using Autofill
Copying Cells Using Autofill
The Autofill feature is used to copy formatting, formulas, cell contents, or to perform different types of series extensions. For example, if you type Monday and Tuesday in consecutive columns, then drag the fill handle to the right, Sheets will fill in Wednesday, Thursday, Friday and so on into the selected cells to complete the series of days.
Do the following:
➤ Select cell C11 and enter the formula =B11*1.02 to calculate a 2% projected increase in expenses, then press Enter
Note that cell C11 contains the formula that you want to copy.
➤ Select cell C11. You’ll see a small blue circle in the lower right corner - we'll refer to this as the fill handle.
➤ Click and drag the fill handle across cells D11:E11 and then release.
➤ Use the Autofill feature to complete cells D12:E12
➤ Select Cell B13 and use AutoFill to calculate the Total for cells C13:E13
Complete the INCOME Section
Complete the INCOME Section
Use any of the methods provided to complete the rest of the tutorial.
Do the following:
➤ Select cell B16 and enter the formula =B8-B13 (Gross Income is equal to Total Revenue minus Total Expenses).
➤ Select cell B17 and enter the formula =B16*.22 (The Tax is 22%).
➤ Select cell B18 and enter the formula =B16-B17 (Net Income is Gross Income minus the Tax).
➤ Copy the formulas in the range B16:B18 to the range C16:E18 using any method you would like.
Add the Annual Totals
Add the Annual Totals
Do the following:
➤ Select cell F4 and enter Year then center the text.
➤ Select cell F6 and enter the formula =SUM(B6:E6)
➤ Copy the formula in cell F6 into the following ranges: F7:F8, F11:F13, and F16:F18.
➤ Select cells B6:F18 and change the number formatting to Financial (this will add commas where appropriate and limit the decimal places to two).
Your completed "Company" spreadsheet should look like the example provided. If your number don't match the example, review the formulas used in your spreadsheet to identify and fix any errors.
What If? Analysis
What If? Analysis
Now that your spreadsheet is set up to automatically perform calculations using formulas, you can now use the power of Sheets to quickly and easily perform a What If? analysis.
A What If? analysis involves three steps:
First, you ask a What If? question about your spreadsheet. For example, "What if first quarter Sales were only $10,000?"
Second, you alter the appropriate cell or cells in your workbook. In this case it would be cell B6.
Third, you observe how the different values in the workbook change.
Do the following:
➤ Experiment with a What If? analysis by entering $10,000 into cell B6. Observe that the Income entries are now negative.
This type of analysis can help answer questions like "What are the minimum quarter 1 Sales needed for the company to breakeven?"
➤ Further experiment by entering numbers into cell B6 until the Income for quarter 1 is equal to zero.
➤ When you are done experimenting, reset the number in cell B6 to 46,000.00
Now that you have learned several methods for copying cells, you can learn how to link between different tabs within a single Spreadsheet.
Page updated
Google Sites
Report abuse