Practice basic spreadsheet tools and formats

Download this Excel worksheet and use it to practice the skills discussed today.

  • save it in your working folder to a name of your choosing
  • highlight some columns and try out the Excel formatting functions on the formatting toolbar (that is, try out the formatting functions that are new in Excel and different from those in Word)

[top]

Tidy up the worksheet

  • rename the sheet tab from Table 1 to Library Profile
  • then select rows 1 and 2 and delete them
  • you have now placed the title of the worksheet on the tab and cleaned up the sheet itself. Why might this be preferable to the way the original author created the worksheet?
  • now go to Page Layout > Page Setup and place the new tab name on each printed page of the worksheet by putting the tab name in the top right header location

[top]

Work with the title

  • look at the title cells called Service Outlets
    • where is the phrase "Service Outlets" placed? (check column D)
      • why does it show up where it does? (check the cell formatting for that cell and look at the horizontal alignment. What does this do for the title placement?)
  • leave this cell alone and move on to the other header cells

[top]

Tidy up the header cells

  • the current headers range over 1, 2, or 3 cells.  Place the header titles in a single cell atop each column.
    • this will involve cutting and pasting between cells
  • then format the newly created header cells in columns C-J with alignment center, bottom, wrap text
  • then delete the unnecessary rows by highlighting them and using Right Click > Delete

[top]

Cell formatting

  • check the cell formatting for the cells in column H
    • what kind of number formatting do you see?
    • try out different kinds of formatting
      • format the cells as numbers: check commas, decimal places, and how you want to display negative numbers
      • format the cells as currency: try out different monetary symbols
      • format the cells as accounting
        • what is different between the currency formatting and the accounting formatting? (look at the monetary symbols)
        • why is one format preferable over another?

[top]

Cutting and pasting cells

  • go to the North Carolina totals at the bottom of column C
    • copy the total in that cell and then paste it into the cell two rows below
      • did the same number appear?
      • why or why not?
    • since the values you copied in the step above are still on the clipboard, go to the cell two rows below and prepare to paste the value in this cell
      • but before you paste, right click and select paste special
    • at the dialog box, check Value
      • did you get the same result as your first paste effort?
      • why or why not?

[top]

Cell formatting

  • look at the format of the value you got in the last paste operation
    • why does it look as it does?
    • format the entire column (by formatting cells) to display numbers with no decimal places and with commas as thousands separators

[top]

AutoFill

try two different AutoFill operations

  • using the GoTo function (CNTL+G), go to cell J81 (if you have done the delete rows operations above, this cell should have the value 584 in it)
    • using the grab handle on the lower right corner of the cell at the bottom of cell J81, drag the value into the cell to the right
      • what value appears in the new cell?
    • now drag it to the next cell below it?
      • what is the value after the second drag?
    • drag the value to the right as far as you wish and then down as far as you wish
    • what are the values in the cells affected by the diagonal drag?
  • using the grab handle on the lower right corner of the cell at the bottom of column F, drag the value into the cell below it. 
    • what value appears in the new cell?
    • now drag it to the next cell below it?
      • is the value after the second drag a number that is one more than the value in the original cell?
    • repeat the right drag and down drag operations you did in the previous section, but with the values in these cells
  • what does this tell you about how the grab handle works?

[top]

Clean up the look of the sheet

  • remove the bold from all but the header rows
  • use different fonts and/or font colors for the different classes of libraries

[top]


setting up spreadsheets | formulas | functions | all functions | PRACTICE EXERCISES | next session

Sessions