INLS261 Tools for Information Literacy

[schedule] [basics] [web pages] [markup] [spreadsheets] [RDBMS] [presentations]

[UNC] [SILS] [REB] [How Stuff Works] [Web Style Guide] [ATN] [ITS Tips] [Other Lives] [links]

[starters] <previous session] [next session>

[spreadsheets] [formulas & functions] [charting data] [database tools]
[spreadsheet intro] [practice exercises] [next time]

Spreadsheets

What is a Spreadsheet?

• it's data displayed in rows and columns on a plane

Microsoft's Excel spreadsheet program is one of the most used spreadsheet applications

• Excel's parts & its Worksheet are similar to Word & other Office applications. Many of the shortcuts in Word will work in Excel.
  • CTRL+S to save
  • CTRL+N for a new document
  • CTRL+C to copy
  • CTRL+X to cut
  • CTRL+V to paste, etc.
• Use this example as we review some of the basic components

•  Sheet tabs  for multiple worksheets
  • Workbook a collection of worksheets sheets
    • a workbook normally holds a default of up to 255 sheets in one workbook, but Excel 2007 has been tested with up to 5,447 worksheets in a single workbook
• Scrollbars right and bottom
•  Rows  numbered 1 though 65,536 in Excel 2003, through 1,046,576 in Excel 2007
•  Columns  lettered A through IV (256 columns) in Excel 2003, through XFD (16,384) in Excel 2007
•  Cells  are where the data is stored
  •  Fill handle  - the small black square in the corner of the cell
    • when you point to it, the pointer changes to a black cross
    • you can then drag a copy of the contents of that cell to an adjacent cell or fill in a series
    • to see fill options, right click as you drag

Online help

• Excel 2007: The Missing Manual
• Microsoft® Office Excel® 2007 Plain & Simple

[top]

Getting Started

Before you begin a complex spreadsheet, plan it out

• what are the desired outputs? (e.g., charts, professional look, for personal use?)
• what data do I need to enter?
• how is the data positioned?
• what mathematical expressions are used?
• who will be using the spreadsheet?

Formatting your workbook

• tune up your workbook to your needs. Make it work for you, not against you.

Saving a Worksheet

• Save with CNTL+S or Save As with ALT+F+A
• as in Word, try_not_to_leave_spaces_in_filenames

Entering Data

• select the cell where you want to enter data. Start typing. The data will appear at the top of the screen in the Formula Bar, where you can edit it, correct errors, and add text, figures, or formulas.
• the data is only placed into the cell when you hit the Return key, the Enter key, the Tab key, or any of the direction keys.

OR....

• double click in the cell. This will allow you to edit in the cell instead of in the Formula Bar.
  • if you enter a long string of data, Excel will either truncate the display of the label or spill the display over into the next cell.
  • you may see this: ########### this is OK
  • to increase a column, put the cursor on the top of the column where it borders the second column. The cursor will turn from an + to a line with 2 arrows (similar to changing tables in word). Left click and hold the mouse, drag it to the desired position.

OR....

• right click cell, select Pick From List

OR....

• Autofill
  • select cell and drag
  • autofill data or number sequences
  • can create custom lists for Autofill from popular options

[top]

Editing Data

• click on the cell you wish to edit, it's contents will appear in the Formula Bar

OR....

• double click the cell (see above under Entering Data)

Dealing with data

• click the cell

• if you use the Delete key, Excel assumes you want to clear contents of the cell(s) but does not clear any formatting you may have added
  • do not use the spacebar to clear cells; it adds a space to the cell rather than emptying it
  • you can also select a cell, right click, and select Clear contents
• if you use the right click Delete dialog, Excel assumes you want to remove the entire cell from the worksheet, not just the contents from the cell
• if you add data to a cell by pasting from another cell or another application, you have many options
  • pasting from another cell can bring values, formulas, relative references, and many other properties - as you wish

[top]

Moving from Cell to Cell

• use mouse
• use keyboard arrow keys
• use Tab (to move to the right)
• use CTRL+G), enter Reference (the cell you wish to go to), press OK

Moving to Another Sheet

• click the Sheet Tab

Selecting Cells

• click the cell, the pointer changes to a big cross
• to select multiple cells, select and drag
• when selecting a large area, use the Shift key to extend the selection. Click on the first cell of the range you want to select; then, while holding down the Shift key, click on the last cell in the range you want to select.
• select discontinuous range of cells by holding down the CTRL key while making the various selections.
• to select an entire row or column, click on the appropriate row or column heading
• to select the entire range in the worksheet, click on the box atop the row numbers and to the left of the column numbers

[top]

Inserting and Deleting Cells, Rows, and Columns

• insert
  • click in a cell
  • right click
  • select Insert...
• delete
  • select the area to be deleted
  • right click
  • select Delete...
  • select the desired option from the dialog box
• you can select a cell, an entire row, an entire column, or any group of cells

[top]

Cutting and Copying Cells

• Excel does not follow the standard sequence for cutting, pasting, copying.
  • select the cell or range of cells to cut or copy
  • choose either CNTL+X (Cut) or CNTL+C (Copy)
    • if you choose Cut, the contents of the selected cells do not disappear. The cells are surrounded by a flashing dotted line, and their contents are not actually moved until you click in the cell where you want the Paste to begin and choose the Paste command (CNTL+X)
    • copy does not move the contents of cells; it copies or reproduces those contents elsewhere in the worksheet
  • if you select a range of cells as the destination of either Cut or Copy, and this range differs in size or shape from the source, Excel will complain. Selecting a single cell is easier, since this method tells Excel where to start the paste and will ensure that the destination is formed exactly like the source
    • to turn off the flashing dotted line, press Esc

[top]

Basic Formatting is quite similar to Word

Renaming Sheet Tabs

• right click the tab
• select Rename
• type in a new name

[top]

Managing your spreadsheet

Absolute cell references

• If you need to lock a formula reference to a specific location, use F4 key to append the $ sign to a cell reference to lock in a specific location
  • example: $A1 refers to row A column will vary, but $A$1 refers to row A column 1

Techniques to Help View Data

Freeze Panes

• freeze row or column headings so that with long worksheets you won't lose sight of the labels as you scroll through your data
• to freeze a row/column, select the row/column immediately underneath or to the right of the row/column you want to freeze, go to View > Freeze Panes

Split Window

• select the row/column where you want to place the split, go to View > Split

What's the difference? When is one method preferable to the other?

Validation

• To validate data as it is entered, use Data, Validation, and build a data entry rule with the dialog box

Add-ins

• Excel comes with additional tools that don't necessarily load unless you ask for them. Look under Excel Options

• check the ones you want and they will be added to your Tools menu

[top]

Page Setup

• Also similar to Word, but here you want to set up an electronic spreadsheet for paper applications

Page  - for orientation & scaling

Header & Footer  - use the custom tools

Sheet - rows to repeat puts column headings on each page

• Print comments if needed
• Page order only necessary if worksheet is wider than a single page

[top]

© R.E. Bergquist