Microsoft Excel - Kansas State University
Download
Report
Transcript Microsoft Excel - Kansas State University
Microsoft Excel
Excel Formatting Rules
Numbers greater than 999 should be formatted to display a comma.
Use a consistent number of decimal places for all numbers
containing decimals. If any currency values on a spreadsheet
display cents (more than 0 cents), then all currency values on a
spreadsheet should be formatted to display 2 decimal places.
Right-align columns of numbers. Never left-align columns of
numbers.
Labels should be directly over the numbers that they describe.
Move the label so that it is directly over the numbers it describes.
NEVER move the numbers so that they are under the label.
Currency values need to display with a $ sign using the currency
style (and not accounting style which happens when you click the $ sign button).
Create formulas using cell references whenever possible. Don’t
hard-code your formulas using real numbers. Creating formulas
using cell references lets you create the interactive formula, so
that when you change the contents of one cell, the formula result
will automatically adjust itself.
Relative vs Absolute vs Mixed Cell References
If a cell, column or row is common to several formulas,
you can lock it down with the $ sign so that cell, column
or row does not change when the formula is copied to a
new location $A$1 or $A1 or
A$1
Parenthesis are used to specify that you want to “do this
operation first.”
It is best not to type in your Excel functions straight
from the keyboard (unless you know EXACTLY what
is required and how it should be entered. It is best to
use the Function Wizard to insert Excel functions.
Excel Functions
PMT: Used to calculate the periodic payment on a
loan (car loan, home loan)
Most payments are made monthly.
PMT function requires three arguments
• Interest rate per period (annual rate divided by 12 for monthly rate)
• NPER: number of payment periods (# of yrs * 12 months/year)
• Amount of the loan (entered into the function as a negative
number so your function result ends up positive)
Excel Functions
The IF function is used in spreadsheet decision making,
and it requires 3 arguments.
The condition being tested for (the cell and the actual test)
What to do if the condition is true
What to do if the condition is false
It yields one result if the condition is true, and a different
result if the condition is false.
The VLOOKUP function determines where
within a specified table, a value (either numeric
or text) is found, and then it retrieves a entry
from the table that corresponds to the value, and
it requires 3 arguments.
The value to be looked up
The table range where that
value can be found
The column containing the
corresponding value.
Entered as a number (2,3, etc.) in your function
When creating the table used by your VLOOKUP, the table
value breakpoints (in the first column) should be arranged in
alphabetical order or low number to high number order.
Microsoft Excel
Referencing with Multiple Sheets
Cell reference on the same sheet -- A1
Referring to cells on another sheet
QUARTER1!A1
‘Quarter One’!A1 (if more than one word in name)
You can also refer to another sheet or another file
within a function
=VLOOKUP($D5, Contributors'!$A$2:$E$44,2)
=VLOOKUP($D5, [CList.xls]Contributors'!$A$2:$E$44,2)
The easiest way to create formulas like this is to
Point and Click! Don’t type them in!
The VLOOKUP function determines where
within a specified table, a value (either numeric
or text) is found, and then it retrieves a entry
from the table that corresponds to the value, and
it requires 3 arguments.
The value to be looked up
The table range where that
value can be found
The column containing the
corresponding value.
Entered as a number (2,3, etc.) in your function
When creating the table used by your VLOOKUP, the table
value breakpoints (in the first column) should be arranged in
alphabetical order or low number to high number order.
Numerical Average: If there are XX items, add XX items together, and then divide
by XX (won’t use in assignment)
Weighted Average: Items with more weight make up a bigger part of the average
(will use: weights should appear as cell references)
(.1*100) + (.15*98)+(.75*40) = 54.7
Parenthesis are used to specify that you want to “do this operation
first.”
Formulas and functions can appear in parenthesis
IF statements are used to allow decision making in a spreadsheet.
Use the “real” currency style to format currency, and not the currency
style button (which is really accounting style).
Creating a worksheet group allows you to apply a common format or
feature to several worksheets at the same time. Be careful when you
use this feature. If you are not careful, you can multiply errors or bad
formulas across several sheets. Sometimes, creating a worksheet
group makes sense. In other cases, you might want to avoid it. I
recommend using a worksheet group to format items, while formulas
are made on a sheet-to-sheet basis (not using groups).
When using these features, PAY CLOSE ATTENTION TO DETAIL AND TAKE
YOUR TIME.
Microsoft Excel
Have students Open Word
Excel Lists
A common use of a spreadsheet is to manage lists of
data. Excel lists can be used to replicate a database
(each column in Excel is a field, and each row is a record)
Create a list range
Sort data (primary sort key, secondary sort key, third sort key)
AutoFilter lets you search for exact matches (like Access Filters).
Custom AutoFilter lets you search using complex criteria (just like
queries in Access)
Conditional formatting highlights information that meets the
criteria you specify. When viewing all of the data, certain items
are brought to your attention by the use of conditional formats.
Excel can summarize data in a list by inserting subtotals
(sums, counts, averages, maximums, minimums) for groups of data in
a list. A subtotal is inserted when a specified field changes
(so you need to sort your data prior to applying subtotals).
Pivot Tables
Excel lists often contain a wealth of information, but
because there is so much detailed data present, it is
often difficult to form a clear overall view of the
information.
A Pivot Table is an interactive report that enables
you to group and summarize an Excel list into a
concise table format for easier reporting and
analysis.
Can be used to summarize data into different categories
using functions such as COUNT, SUM, AVERAGE,
MAX, and MIN.
Pivot Tables
To generate a Pivot Table, you can specify the
dimensions by which you wish to view the data (one
factor or multiple factors), which allows you to see
relationships in the data.
Using the data fields, you specify
What field is to be used to create row items
What field is to be used to create column headings.
What field or fields are to be used data area/data items
Report Filters (Page fields) can be used to filter the Pivot
Table
Pivot Tables can help you see relationships in the data
Pivot Tables
can help
you see
relationships
in the data
Pivot Tables need to be formatted
Labels and numbers need to be in alignment
Columns need to be narrower
Numbers should be properly formatted ($, decimals, commas, etc.)
You can even change the mathematical operation used and the label text that
is displayed.
Pivot Tables can help you see relationships in the data
Pivot Tables can help you see
relationships in the data.
If you want to take a picture of what is on your
computer screen, use the PrtScn button (typically
found on the top row of your keyboard) and then
Report Filters (Page
fields) can be used to
filter the Pivot Table
Multi-factor Pivot Tables use multiple data
fields Don’t hand in something like this. It looks BAD and is hard to read.
I want to see the average amount purchased, the
average number in the household and the average
number of purchases made by each household, by
region and “rent vs own” (multiple factors)
Pivot Tables can help you see relationships
in the data
When moving values to rows, ALWAYS MOVE
THE DATA POINTS (found before the “by”
statement). The words after the “by” statement should
not be moved because they form the basis of your
rows and columns.
Select
what you
want to
move
(Values),
right-click,
select
“Move
Values to”
and you
want to
“Move
Values to
Rows”
Microsoft Excel
I am not teaching how to make a spreadsheet. It is
assumed that you can already do that.
My focus is on how a spreadsheet is used in a
business environment. Business problem-solving
Our emphasis in Excel
Building formulas and using Excel functions
• VLOOKUP, IF, PMT FV, and others
• Relative, absolute and mixed cell references
Multiple worksheets and the formulas that go between
them
Decision Making in Excel
• What-if Analysis
• Pivot tables, Filtering Excel lists, conditional formatting
Spreadsheets as a Decision Support
System
A spreadsheet can serve as a decision support
system for a business.
Its analysis/results can be used to make a decision or
make a better decision.
Managers use this tool.
• Guides you and assists you, but you have to know how to use
the tool. You have to know what you are doing, what needs to
be done, how to set up the model mathematically, when
something is not right, and you have to make the final decision.
Perform What-If Analysis
Change one assumption -- See the effect on the entire
spreadsheet
Formulas
Used to perform mathematical calculations
Must begin with an =
Use normal algebraic rules
Formula
Evaluate whatever is in parentheses first
Then, perform multiplication and division
Then perform addition and subtraction
Formulas are always evaluated from left to right.
Formulas should be created using cell references
whenever possible. Avoid real numbers in your
formulas. Spreadsheet should be totally interactive.
Relative/Normal Cell References
When copied, references adjust
RELATIVE
Absolute Cell Reference
Do not adjust when copied, it will always refer to
the same cell, column or row.
Add $ before row and column
$A$4 = locks in cell
$A4 = lock column, row varies
A$4 = locks row, column varies
ABSOLUTE
Easy way to make an absolute reference, click on
reference, press F4 key
Save time by allowing copying of complex
formulas
Development activities for creating effective worksheets
Build the worksheet
Enter labels
Enter the data (the values, formulas, and functions)
Format the worksheet
Verify/test the worksheet to make sure everything works.
Order of precedence for mathematical operations
2 + 3 * 4 = 14
(2 + 3) * 4 = 20
2*2^2=8
(2 * 2) ^ 2 = 16
Relative and Absolute References
Excel automatically changes a relative cell reference, such as B8,
when a formula containing it is copied (down to become B9 or across
to become C8). To convert this to an absolute reference, which does
not change when copied to another location, each element that you
don’t want to change must be preceded by a $. Therefore, the relative
reference B8 becomes an absolute reference when it is changed to
$B$8, and when it is copied, it never changes. By contrast, $B8 and
B$8 are mixed references. In the first example, the column is fixed
and the row is relative (it change adjust). In the second example, the
row is fixed and the column is relative (it can adjust). Copying down
would yield $B9 and B$8, while copying across would yield $B8 and
C$8. Understanding this concept is fundamental to understanding
how to structure formulas and functions which will be copied.
Without this understanding, students will constantly get incorrect
results and have to re-enter their formulas and functions.
Excel Functions
VLOOKUP: used to look up an item in a table and
it returns its price, discount %, or other form of
result.
IF: checks a cell. Does one thing if the test is true,
and it does something else if the test if false.
PMT: Used to calculate yearly and monthly loan
payment amounts.
Excel Formatting Rules
Numbers greater than 999 should be formatted to display a comma.
Use a consistent number of decimal places for all numbers
containing decimals.
Right-align columns of numbers. Never left-align columns of
numbers.
Labels should be directly over the numbers that they describe.
Move the label so that it is directly over the numbers it describes.
NEVER move the numbers so that they are under the label.
Currency values need to display with a $ sign. If any currency
values on a spreadsheet display cents (more than 0 cents), then all
currency values on a spreadsheet should be formatted to display 2
decimal places.
Create formulas using cell references whenever possible. Don’t
hard-code your formulas using real numbers. Creating formulas
using cell references lets you create the interactive formula, so
that when you change the contents of one cell, the formula result
will automatically adjust itself.
The End
Functions
Used to perform common/complex calculations
Use the following standard format:
=NAME(arguments)
Arguments can be
Numbers
Cell References
Formulas
COMMON FUNCTIONS
SUM Function: Adds things together
Individual Numbers =SUM(4,5,6)
Specific Cells =SUM(A1,C10,D4)
A range of Cells =SUM(A1:A5)
Financial
Sum
=PV(rate, nper, pmt, fv)
Computes the present value of a series of future payments
=PMT(rate,nper,pv,fv)
Calculates a payment amount for a loan or mortgage based
on a constant interest rate and number of periods.
COMMON FUNCTIONS
Reference
Vlookup(lookup_Value,Table_Array,
Column_Index_Value)
1. Specify a value to look up
2. Specify the table where that value can be found.
3. Specify the column where the result can be found.
Logical
=IF(condition, value if true, value if false)
Specify a condition, what to happen if true, and what to
happen if false.
Rounding vs Formatting
=ROUND(number, number of places)
Formatting only changes the appearance of
cell contents.
Rounding actually changes the values in the
cells.
Formatting Cells
Column Widths
Rather than extending across several columns, widen
columns to fit text
Widen columns by dragging the right border of the
column title (or double clicking it to widen to fit).
Numbers = MUST USE CONSISTENT FORMATS
Consistent Number of Decimal Places
Commas
Consistent negative number format
Align Decimals
Formatting Cells, Continued
Borders
Use in financial reports, draw lines
Alignment
Left, Right,Center
Center Across Selection
Bold, Italic, Fonts
Color should be used to indicate data entry
areas or values that might change.
Working with Sheets
Copying Sheets
Naming Sheets
Moving Sheets
Referencing with Multiple Sheets
Cell reference on the same sheet -- A1
Referring to cells on another sheet
QUARTER1!A1
‘Quarter One’!A1 (if more than one word in name)
You can also refer to another sheet or another file
within a function
=VLOOKUP($D5, Contributors'!$A$2:$E$44,2)
=VLOOKUP($D5, [CList.xls]Contributors'!$A$2:$E$44,2)
The easiest way to create formulas like this is to
Point and Click! Don’t type them in!
Charts and Graphs
Great way to present information in an
easy-to-understand format.
Chart Types
Pie
Line
3-D Enhancement
Bar or Column Chart
Use the CONTROL KEY to select ranges
of data that are not side-by-side.
I am not teaching how to make a spreadsheet. It
is assumed that you can already do that.
My focus is on how a spreadsheet is used in a
business environment. Business problem-solving
You must know to properly design and
FORMAT a spreadsheet for business applications
Alignment
Borders
Number formats: commas, decimal places
Spelling
Data entry areas
Charts
Proper formula construction
Column widths
Appropriate print formats
Attention to Detail: Be Careful: Think
Most students know HOW to
format a spreadsheet.
The problem is that most students
don’t know WHEN to format,
or
they fail TO RECOGNIZE when
a formatting problem has
occurred.
Designing Good Spreadsheets
Computations should always be performed using
formulas, functions and/or cell references!
Outputs should be Easy to Read
Titles
Appropriate Formatting
It should be easy to Update/Change
Test out your spreadsheet to make sure that your
results make sense.
Your Spreadsheets should pass the
“Reasonableness Test.”
Do your answers make sense in a real-world
business setting?
Taxes
• Subtract your income tax to find out your income after taxes.
• Add your sales tax when purchasing goods.
Does it make sense for a portfolio of stocks and bonds to
make or lose lots of money from one day to the next?
After you have paid off a loan, does it make any sense
for you to still owe money, or get money back (refund)?
Is your percentage for a change from one year to the next
realistic? Is a 1,000% change realistic?
Using A Data Entry Area
What?
An area where all the values used in the spreadsheet
(especially those likely to change) are entered.
Cells in the output area reference these cells
When?
The spreadsheet will be used over and over
• Monthly/Weekly/Daily Reports
• What-If Analysis
The spreadsheet will be used by someone who doesn’t
understand the calculation or spreadsheets
DEA#1
Rules for Data Entry Area
Include Directions
Clearly describe what information is needed
Dollars, Units?
Avoid Abbreviations
Additional Directions if needed
Clearly identify where the information
should be put
Format Data Entry Cells Appropriately so
that information looks nice when entered.
DEA#2
Printing
Portrait versus Landscape
For this class
The printout of a spreadsheet fits on 1 page
Your Name needs to appear as a footer on EACH
sheet/printout that you make.
Display/Print formulas
Go to the Tools menu, select Options
Print
Proper Formula Construction
Don’t type in all of your formulas and cell
references from the keyboard. The easiest way
to create formulas is to Point and Click them in!
Let Excel create the formulas for you. Don’t
type them in!
Formulas should be created using cell references
whenever possible. Avoid real numbers.
Spreadsheet should be totally interactive.
Excel Functions
ROUND: rounds a number or formula to a specified
number of digits.
VLOOKUP: used to look up an item in a table and
it returns its price, discount %, or other form of
result.
IF: checks a cell. Does one thing if the test is true,
and it does something else if the test if false.
PMT: Used to calculate yearly and monthly loan
payment amounts.
Always keep your labels and their related numbers
in alignment. Move the label, not the numbers.
Rounding vs Formatting
Formatting only changes the appearance of
cell contents. What you see many not
actually be there
March 31 vs March 31, 1999 vs 3/31/99
Rounding actually changes the values in the
cells. What you see in the cell is what is
really there.
I wish to display my formulas in Excel. What menu
and options within that menu do I choose?
Tools Menu, Select Options, Select the View Tab, place
a checkmark next to Formulas
Add / Subtract
Evaluate Left-to-Right
Multiply / Divide
Evaluate Items in Parenthesis
Evaluate Items in Parenthesis first
Next, do all of your multiplication and division
Next, do all of your addition and subtraction
Within equal operations, evaluate your formulas from left to right
Excel Functions
PMT: Used to calculate the periodic payment on a
loan (car loan, home loan)
Most payments are made monthly.
PMT function requires three arguments
• Interest rate per period (annual rate divided by 12 for monthly rate)
• NPER: number of payment periods (# of yrs * 12 months/year)
• Amount of the loan (expressed as a negative number)
FV: Returns the future value of an investment based on
constant payments and a constant interest rate
(investments)
Goal Seek: allows you to set a desired end result. Tells
you what a variable must equal to get that desired result