Excel Basics Week 2 Notes Creating Worksheets

This week covers the following learning objectives
  • Plan, build, test, document, preview and print a worksheet
  • Enter labels, values and formulas
  • Calculate a total using the AutoSum button
  • Copy formulas using the fill handle and Clipboard
  • Learn about relative, absolute and mixed cell references
  • Use the Average, Max and Min functions to calculate values
  • Spell check the worksheet
  • Insert a row
  • Reverse an action using the undo button
  • Move a range of cells
  • Format the worksheet using AutoFormat
  • Centre printouts on a page
  • Customise worksheet headers

Developing a Worksheet

  1. Determine the worksheet's purpose
  2. Enter the data and the formulas into the worksheet
  3. Test the worksheet
  4. Edit the worksheet to correct any errors or to make modifications
  5. Document the worksheet
  6. Improve the appearance of the worksheet
  7. Save and print the completed worksheet

Planning the Worksheet

  1. What is the goal of the worksheet?
  2. What are the desired results or output of the worksheet?
  3. What data or input is needed to calculate the results you want to see?
  4. What calculations are needed to produce the desired output?

Copying Cell Contents With the Fill Handle

  1. Click the cell that contains the label, value or formula you want to copy. If you want to copy the contents to more than one cell, select the range of cells you want to copy.
  2. To copy to adjacent cells, click and drag the fill handle to outline the cells where you want the copy or copies to appear, and then release the mouse button.
  3. Copying formulas Using Relative Referencing - when you copy a formula that contains cell references Excel automatically adjusts the cell references for the new location

Absolute vs Relative Cell References

If you want a cell reference to point to the same location in the worksheet when you copy it, you must use an absolute reference. An absolute reference is a cell reference in a formula that does not change when it is copied to another cell.

Editing Cell Reference Types

· Double click the cell containing the formula you want to edit
· Use the arrow keys to move the insertion point to the part of the cell reference you want to change
· Press the F4 key until the reference is correct
· Press the Enter key to complete the edit

Copying and Pasting a Cell or Range of Cells

· Select the cell or range of cells to be copied
· Click the copy button on the standard tool bar
· Select the range into which you want to copy the formula
· Click the Paste button on the standard toolbar
· Press the enter key

Quick Check Questions Part 1

1. Describe how the AutoSum works

2. In cell C5 you have the formula =A5+B5. After you copy this formula to cell C6, the formula in cell C6 woulld appear in the formula bar as

3. In the formula =A5+B5, A5 and B5 are examples of

4. In the formula =A8+(1+$C$1) $C$1 is an example of

5. When you copy the formula using the Copy and Paste buttons on the standard toolbar, Excel uses the to temporarily store the formula

6. The is a small black square located in the lower right corner of a selected cell

7. Describe the steps you take to change the name of the sheet tab

8. List the steps to follow to create a worksheet.