Why?

• What if scenario - plug in some numbers to see the effect on sales or cost or taxes or investment gains
• bookkeeping (taxes)
• creating test data
• create charts to help you analyze the numbers produced

What?

• A spreadsheet processes information in the form of tables. - single pages of rows and columns
• Hides mathematical formulas under the numbers.
• Creates charts from the numbers
• It automatically recognizes the type of data you have (date, etc)
• It allows you to change formatting of any of the info you have.

How?

Screen parts unique to Excel:

• A worksheet is an on-screen grid of rows, columns and cells. Access other worksheets by pressing worksheet tabs at the bottom of the page.
• Active cell - highlighted cell
• Formula bar - where you enter the hidden formulas.
• Each function key has a value - see page 11, session 1
• Toolbar - see page 6, session 1

Exercises we will use: - introduction worksheet, tax form and business loan amortization

Select cells - ranges are defined by the upper left cell number to the lower right cell number

Entering data -

types: (number, text and date)

1. numbers if the data is all hash marks, widen the column

a. plus (+), minus (–), percent (%), fraction (/), exponent (E), and dollar sign ($)

b. scientific notation 4.09E5 = 509,000

2. text is called labels (so if you see it in help, you know what it means)

a. can enter numbers to be treated like text by typing "'" = very good for id numbers that need the leading zeroes

3. dates

filling the sheet quickly with numbers (hold for later)

fill handle

edit / fill series (hold for later)

Editing data -

• clear vs delete (note that delete key clears)
• copy - can copy value, formula, or formatting. (copy / paste special)
• cut - insert cut row
• cut vs delete
• loses buffer if another click
• escape is useful
• contents and format are 2 separate things
• difference between being inside the cell and on the cell
• selection is what is in border, including the highlighted cell

Formatting data - format / cell / number and alignment

• see all the format types - holds both the underlying value and the displayed value. Uses underlying for calculation. Enter 5.0962 and format with 2 decimals. See both numbers
• wrap
• merge
• fonts

Entering formulas: - use the formula bar

• "=" means there will be a formula
• can use cell references in the formula = cell location (ex. a3)
• another worksheet = sheet2!a3
• when you copy, it copies logically (relative to the cells current position), unless you fix it (absolute) with a "$"
• functions: =keyword(parameters)
• ranges again are top left to bottom right, with a colon inbetween : a1:b9
• use cell references so it is easy to do what-ifs; always label data

• Using the IF function (Operators on page 135 in excel)
• Concatenate with "&"

• Some require many arguments.

For example, the PMT function determines the periodic payment on a loan. To calculate the payment, you must supply (at the minimum) the interest rate, the number of payments, and the amount financed (the value of the principal). The function's syntax looks like this:

PMT (rate,nper,pv,fv,type)

In this function, the required arguments (rate, nper, and pv) are shown in bold; the other two (fv and type) are optional. In place of these arguments, you need to supply values or cell references. The rate is the interest rate, expressed as a percentage. Nper refers to the number of payments. Pv refers to the principal amount. Note that the arguments must be separated by commas; do not insert additional spaces. To calculate the loan payment on a $21,000 automobile, assuming 60 monthly payments and a 9.5% annual interest rate, you type the following formula:

=PMT(9.5%/12,60,21000)

After you confirm this formula, the program returns the loan payment. The answer is negative (shown in parentheses) because this is money that you must pay out. Note that this formula divides the annual interest rate by the number of months in a year (12), producing the monthly interest rate.

• Insert function command tells all functions and their formats

• Look at the categories to see their meanings:

Category	Purpose
Database	Producing counts or sums or determining the maximum or minimum values in a range of data.
Date and Time	Displaying dates or times, such as showing today's date.
Engineering	Performing engineering analyses, such as working with complex numbers, converting one form of measurement to another, and converting numbers between one type of number system and another.
Financial	Calculating loan payments, appreciation of investments, and depreciation of property.
Information	Determining the type of information shown in a cell. These functions can be used to test for errors.
Logical	Determining whether a condition is true or false.
Lookup and Reference	Retrieving values from a list or table.
Math and Trigonometry	Performing mathematical and trigonometry calculations like those found on a scientific calculator.
Statistical	Performs statistical calculations on a range of data, such as finding the average or standard deviation.
Text	Changing text (for example, converting uppercase letters to lowercase).

protect - format cell protect

Changing the worksheet structure:

• add or delete rows or columns
• change column and row height manually, or with autofit
• hide rows or columns

Risks

• check your work
• separate input from output
• make formulas clear
• auditing tools - trace precedents
• edit input with logic commands
• protect cells (tools / protect with format / cells /locked)

Printing

• print area - file / print area / set print area
• titles - file / page setup / sheet / rows to repeat
• window - freeze pane (select below)
• orientation
• fit all on one page or fit width on one page

Save as HTML

More formatting

• borders and shading
• color
• styles
• orientation

Use a template -

• C:\Program Files\Microsoft Office\Templates\Spreadsheet Solutions
• 5Ch1 Loan Payments Template

Using pictures

insert clip art or pictures

Session 3

Working with ranges - (just do set a print range and skip the rest)

Difference between create and define: create uses labels, and in define you enter the name

Change the name in the name box (upper left)

• Create named ranges
• move to a named range
• Copy a range
• Print a range
• use a named range in a formula
• Create a list of ranges

Find and replace command

Use dumy rows and columns in a range (with the sum command)

Sort (data / sort)

Hiding columns

Full paste special - (done already)

• transpose

More on absolute, relative and mixed cell addresses and IF and ROUND (just mention ROUND)

IF uses not, and, or, =, <,<=,<>,>=,>

5 ch2 grade book

dummy / total points / % grade (use % <-.0 button)

add a rounded grade

if % grade > .894 then A

if % grade > .794 then B

else C

PMT(rate, nper, prin, fv, type)

example: 5 ch3 van

FV (rate, nper, pmt, pv, type) type 0 = end of period; 1 = begin of period; pmt or pv

PV (rate, nper, pmt, fv, type) type 0 = end of period; 1 = begin of period; pmt or fv

example: retirement

Session 4

Charts

• Column chart  A column chart provides a ready comparison among separate items. It can be used also to show changes over time (called a time series). For example, you can use a column chart to show the sales for each month of the year.
• Bar chart  Because the bars are stacked vertically, a bar chart is not a good choice for a time series. Use a bar chart to compare separate, distinct items. For example, you can use a bar chart to show the total sales for Models 101, 102, and 103.
• Line chart  This type of chart is designed for time series use, showing fluctuations in on-going, continuous trends over time. You could use a line chart to show how basic production costs, including the cost of raw materials and utility charges, have fluctuated over a one-year period.
• Pie chart  A pie chart shows the parts of a whole. Use this type of chart to show relative percentages when everything adds up to 100 percent. You could use a pie chart to show how Models 101, 102, and 103 contribute differently to the total sales picture.

Here are some ways to get fancy with charts:

• Multiple series  You can define more than one data series in any chart except a pie chart. For example, you could create a column chart that shows sales for eight product lines over a four-week period.
• Legend  A legend provides a key to what the different colors or shades mean. A legend comes in handy when you have a complex chart with lots of pie slices or different data series.
• Scaling  Spreadsheet programs adjust the scale of a chart automatically, but sometimes you need to adjust the scale manually to avoid giving a false impression. If a line chart shows sales rising too steeply, you can make the increase seem more gradual by changing the scale.

example: 5 ch1 - quarterly sales

wizard

add a series (right click in greyu - source data)

insert chart in sheet (copy, paste)

format labels

chart types - grey to change in to combine

change types

change sub-types

double click on pie to label

Session 5:

Three dimensional workbooks

Linked workbooks

Series and autofill

Insert hyperlinks

Macros

Mail Merge

Homework

• Create a spreadsheet of taxes using 1040 EZ form
• excel pactice worksheet
• royalty worksheet?
• van comparison worksheet