EXCEL APPLICATION GUIDE

Select cells - ranges are defined by the upper left cell number to the lower right cell number (address : address)

Entering data -

types: (number, text and date)

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

a. plus (+), minus (–), percent (%), fraction (/), multiply (*), exponent (E or ^), 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
• Number or Text - make text with space or single quote

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 "$"
• budget with total percent spent of money each month.

• Concatenate with "&"

Functions:

• 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

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

• 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).

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) (format cell protect)

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

Using pictures

insert clip art or pictures

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

Sort (data / sort)

Hiding columns

Full paste special - (done already)

• transpose

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

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

Exercises:

Intro worksheet

Budget:

Starting $

Monthly:

• All your expenses (and % of total expenses for each item)
• All your income
• Income - expenses
• Starting $ +income - expenses

Van Comparison ??