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 7 and 8. 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
• If the #### shows up in your cell it means the cell width is too narrow to show the results of your formula.
• Type a date in as 10/9 and you'll have a date showing 9-Oct-05
• 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 grid lines and row/column labels
• Set the Print Area before you print - saves on paper
• File, Print Area, Set Print Area

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