discuss key points from tutorials 1-4 o cell references o formulas o functions o formatting o...

Agenda 11/5 – 11/7 Discuss key points from Tutorials 1-4

o Cell referenceso Formulaso Functionso Formattingo Conditional formattingo Charts

Introducing Microsoft Excel 2010

Computer program used to enter, store, analyze, and present quantitative data

Creates electronic versions of spreadsheetso Collection of text and numbers laid out in

a grid Displays values calculated from data Allows what-if analysis

o Ability to change values in a spreadsheet and assess the effect they have on calculated values

2

3

4

Worksheet Navigation A workbook can have two kinds of sheets:

oWorksheet contains a grid of rows and columns into which user enters data

oChart sheet provides visual representation of data• For detailed charts that need more space to be seen clearly

or to show a chart without any worksheet text or data• Do not contain worksheet cells for calculating numeric

values

Cell reference identifies column/row location5

Worksheet Navigation Keys

6

Working with Columns and Rows

To make data easier to read:

oModify size of columns and rows in a worksheet

To modify size of columns or rows:

oDrag border to resizeoDouble-click border to autofito Format the Cells group to specify

7

Deleting vs clearing Deleting and clearing a row or column

oDeleting removes both the data and the cells

oClearing removes the data, leaving blank cells where data had been

8

Working with Cells and Ranges

Range reference indicates location and size of a cell range

oAdjacent (A1:G5)oNonadjacent (A1:A5;F1:G5)

Selecting a range

oWork with all cells in the range as a group

Moving and copying a range

oDrag and dropoCut and paste

9

Working with Formulas Formula

o An expression that returns a valueo Written using operators that combine different values,

resulting in a single displayed value

10

Working with Formulas Entering a formula

o Click cell where you want formula results to appear

o Type = and an expression that calculates a value using cell references and arithmetic operators• Cell references allow you to change values used in the calculation without having to modify the formula itself

o Press Enter or Tab to complete the formula

11

Working with Formulas Order of precedence

o Set of predefined rules used to determine sequence in which operators are applied in a calculation

12

Introducing Functions Function

o Named operation that returns a valueo Simplifies a formula, reducing a long formula into a compact

statement; for example, to add values in the range A1:A10:

• Enter the long formula:=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10 - or -• Use the SUM function to accomplish the

same thing:=SUM(A1:A10)

13

Entering Functions with AutoSum

Fast, convenient way to enter commonly used functions

Includes buttons to quickly insert/generate:o Sum of values in column or row (SUM)o Average value in column or row (AVERAGE)o Total count of numeric values in column or row (COUNT)o Minimum value in column or row (MIN)o Maximum value in column or row (MAX)

14

Cell References and Excel Functions

15

16

Understanding Cell References

To record and analyze data

o Enter data in cells in a worksheetoReference the cells with data in

formulas that perform calculations on that data

Types of cell references

oRelativeoAbsoluteoMixed

17

Using Relative References

Cell reference as it appears in worksheet (B2) Always interpreted in relation (relative) to the

location of the cell containing the formula Changes when the formula is copied to another

group of cells Allows quick generation of row/column totals

without revising formulas

18

Using Absolute References

Cell reference that remains fixed when the formula is copied to a new location

Have a $ before each column and row designation ($B$2)

Enter values in their own cells; reference the appropriate cells in formulas in the worksheet

oReduces amount of data entryoWhen a data valued is changed, all

formulas based on that cell are updated to reflect the new value

19

Using Mixed References

Contain both relative and absolute references “Lock” one part of the cell reference while the

other part can change Have a $ before either the row or column

reference ($B2 or B$2)

20

Using a Mixed Reference

21

When to Use Relative, Absolute, and Mixed References

Relative referenceso Repeat same formula with cells in different locations

Absolute referenceso Different formulas to refer to the same cell

Mixed referenceso Seldom used other than when creating tables of

calculated values

Use F4 key to cycle through different types of references

22

Working with Functions Quick way to calculate summary data Every function follows a set of rules (syntax) that

specifies how the function should be written General syntax of all Excel functions

Square brackets indicate optional arguments

23

Working with Functions Advantage of using cell references:

oValues used in the function are visible to users and can be easily edited as needed

Functions can also be placed inside another function, or nested (must include all parentheses)

24

Using the Function Library to Insert a Function

When you select a function, the Function Arguments dialog box opens, listing all arguments associated with that function

25

Entering Data and Formulas with AutoFill

Use the fill handle to copy a formula and conditional formatting

oMore efficient than two-step process of copying and pasting

By default, AutoFill copies both content and formatting of original range to selected range

26

Working with Logical Functions

Logical functions

oBuild decision-making capability into a formula

oWork with statements that are either true or false

Excel supports many different logical functions, including the IF function

27

Working with Logical Functions

Comparison operator

o Symbol that indicates the relationship between two values

28

Using the IF Function

29

Returns one value if a statement is true and returns a different value if that statement is false

IF (logical_test, [value_if_true,] [value_if_false])

Working with Date Functions

For scheduling or determining on what days of the week certain dates occur

30

Financial Functions for Loans and Interest Payments

31

Working with Financial Functions

Cost of a loan to the borrower is largely based on three factors:

oPrincipal: amount of money being loaned

o Interest: amount added to the principal by the lender• Calculated as simple interest or as compound interest

o Time required to pay back the loan32

Using Functions to Manage Personal Finances

33

Function Use to determine…FV (future value) How much an investment will be worth after a series of

monthly payments at some future timePMT (payment) How much you have to spend each month to repay a

loan or mortgage within a set period of time

IPMT (interest payment)

How much of your monthly loan payment is used to pay the interest

PPMT (principal payment)

How much of your monthly loan payment is used for repaying the principal

PV (present value) Largest loan or mortgage you can afford given a set monthly payment

NPER (number of periods)

How long it will take to pay off a loan with constant monthly payments

Formatting Cell Text

Formattingo Process of changing workbook’s appearance by defining

fonts, styles, colors, and graphical effects

• Only the appearance of data changes, not data itself

o Enhances readability and appealo Live Preview shows the effects of formatting options

before you apply them

Themeso Named collections of formatting effects

34

Options in the Format Cells Dialog Box

Presents formats available from Home tab in a different way and provides more choices

Six tabs, each focusing on different options:

oNumberoAlignmento FontoBordero Fillo Protection

35

Copying and Pasting Formats

Copying formats with Format Painter

o Fast and efficient way of maintaining a consistent look and feel throughout a workbook

oCopies formatting without duplicating data

36

Copying and Pasting Formats

Use Paste Special to control exactly how to paste the copied range

37

Highlighting Cells with Conditional Formatting

Goal of highlighting: Provide strong visual clue of important data or results

Format applied to a cell depends upon value or content of the cell

Dynamic: If cell’s value changes, cell’s format also changes as needed

Excel has four conditional formats: data bars, highlighting, color scales, and icon sets

38

Highlighting Rules Each conditional format has a set of rules that

define how formatting should be applied and under what conditions format will be changed

39

Excel Charts

40

Chart Elements

41

Editing the Axis Scale and Text

Range of values (scale) of an axis is based on values in data source

Vertical (value) axis: range of series values Horizontal (category) axis: category values Primary and secondary axes can use different

scales and labels Add descriptive axis titles if axis labels are not

self-explanatory (default is no titles)

42

Communicating Effectively with Charts

Keep it simple Focus on the message Limit the number of data series Use gridlines in moderation Choose colors carefully Limit chart to a few text styles

43

Choosing the Right Chart

44

Chart When to UsePie charts Small number of categories; easy to

distinguish relative sizes of slices

Column or bar chart

Several categories

Line charts Categories follow a sequential order

XY scatter charts

To plot two numeric values against one another

Custom chart Available charts don’t meet your needs