COMPUTER LITERACY

Introduction

Lots of people are intimidated by Excel or spreadsheets in general because they seem to be so math intensive. But the beauty of using electronic spreadsheets versus the old pencil and paper is that you can let the computer do the math for you. Rather than memorize a bunch of formulas, you can just click a few buttons and the computer will enter the result of all those formulas with very little work on your part. The real work on your part comes from learning how to take advantage of the automation.

A word of caution when you're doing your lab assignments in Weeks 8, 9, and 10. Occasionally we see students using a hand held calculator to figure out the answers to formulas instead of putting the actual formulas in the spreadsheet. Bad idea! If you succumb to this temptation because you know how to use the calculator but you don't know how to enter formulas, you're defeating the whole purpose of learning about spreadsheets. You're also going to lose points on your assignments because we deduct points if you haven't properly entered the formulas. Work through the assignments and learn how to let the computer do the work for you!

Terminology

Workbook

same as a "document" in word processors
divided into pages called worksheet
each worksheet is a separate spreadsheet

Columns

labeled with an alphabetical character
vertical
256 columns in each worksheet

Rows

labeled with a numeric character
horizontal
16,384 rows in each worksheet

Cells

basic unit of a worksheet in which you enter data
the intersection of a row and a column
labeled with the column label first and row label second
examples - C11, M133, AA235 - called cell address or cell reference
4,194,304 cells in a spreadsheet page

Active cell

the cell in which you are currently entering data
identified several ways (I'll show you in lecture)

Cell block or range of cells

when you want to perform an operation on more than one cell
tell the computer the cell range that you want
enter the upper left cell first and then the lower right cell
use a colon : to separate the cell addresses

example: A1:H24

Menu bar

pretty much the same as word processors

Tool bar

also pretty much the same as word processors

Formatting toolbar

controls appearance of data

Formula Bar

area that shows exactly what you are entering into the active cell

Selecting a cell

point to the cell with the pointer and click
use arrow keys to move around
active cell address will be displayed on the Name box
deactivate your selection by pressing the ESC key on the key board

Entering Data

Constant

An entry that doesn't change
Can be a number, or descriptive text (label)

Entering data

Unless you enter an equal sign first the data will be read as text

Type a date in as 10/9 and you'll have a date showing 9-Oct-00

Numbers are right aligned unless you change it
Alphabetical characters are left aligned unless you change it
DON'T type in descriptors such as dollar sign $ or percent % sign

If you do, you'll get an error message telling you not to do that
Okay, I'll show you - enter =$5+$5 and see what happens

DON'T type in special characters such as commas - Excel doesn't need them and will only remove them if you do. Why waste extra key strokes when you don't need to.

Formulas

use cells with numbers not text
use the cell address and not the actual numerical value in the cell
must always start with an = sign

Functions

tons of functions in Excel
DON'T USE ACTUAL NUMBERS - USE CELL REFERENCES
predefined computational tasks that perform particular calculations
use either alone or within other formulas
values are called arguments include the arguments in parentheses after the function name
separate multiple arguments with a comma
easiest way to use them is with the Paste Function button on the Tool bar

Let the computer do the work for you!

Calculate a sum

highlight the row or column
click on the SpeedSum button
not necessary for you to type in each cell address

Selecting a block or range of cells

move the mouse to upper left corner
hold down the left mouse button
drag to the lower right corner
the block will be highlighted

Copying formulas

don't have to continually retype formulas and cell references
do the first formula
make sure the cell you want to copy is the active cell
click on the copy button move the mouse to the new cell or group of cells
click the paste button
formulas will adjust themselves to the new location

Formatting the Spreadsheet

make it look nice
easier to read
emphasize certain data
numbers

don't type in $ or % or ,
use the Formatting Toolbar or the Format Menu

adds the $ sign, commas, percent sign, dates, etc

Printing a Spreadsheet

select the block that you want to print

understand block ranges

A1..H22

use Print Preview

Options button

if you want to print the gridlines and row/column labels

Set the Print Area before you print - saves on paper

File, Print Area, Set Print Area

Referencing

If you don't learn anything else in spreadsheets, learn this!

When you use cell addresses in formulas and functions you may or may not want the cell addresses to change if you copy that formula/function to another cell. There are two ways to reference cell addresses:

Relative Referencing

no special annotations required
default status
cell addresses will change and adjust themselves to the new location

Absolute Referencing

requires a dollar sign $ in front of the column and/or row address
you must stipulate what you want
cell address will not change when you copy the reference to a new location
examples:

$B$2 - neither column and row address will change
B$2 - column reference will change; row reference won't change
$B2 - column reference won't change; row reference will change

Why should you care about this? Because if you're smart and use spreadsheet software correctly and efficiently, you'll be doing a lot of copying and pasting of formulas and functions. And, if you're smart and use spreadsheet software correctly and efficiently, you'll use cell references instead of actual numbers in cells in those formulas and functions. Understanding and using absolute/relative referencing will make you're life so much easier when you're copying and pasting formulas and functions. But you're allowed to do it the hard way if you'd like.

"What-If" Analysis

One of the most important features of electronic spreadsheets
Make sure you're using correct relative/absolute referencing
Change one or two number and all other cells are changed automatically to reflect the new set of numbers or figures
Charts associated with data are also changed
Process takes seconds instead of hours or days
Especially important in business situations

Charts

It's a proven fact that people understand information much faster by looking at pictures than by reading data. That's why Charts are such an important part of electronic spreadsheets.

Category labels - descriptive entries
Data points - numeric values
Data series - data points that are grouped and appear in rows or columns on the worksheet

Displaying charts
Types of charts

Pie - most effective way to display proportional relationships
Column - need to show actual numbers rather than percentages
Bar - same as column but horizontal
Two ways to create a chart
Embedded

stays on the same worksheet as the data it's associated with

Chart sheet Not embedded on the same worksheet but located on separate sheet

Two ways to create charts

Use Chart Wizard to help you create your chart - it's so much easier
Do it the hard way from scratch