MODULE OVERVIEW

Module 3 Part 1
What is A Spreadsheet?

Part 2
Gaining Proficiency: Copying and
Formatting
Part 3
Microsoft Office Suite Using Formulas & Functions

Microsoft Excel Part 4

Graphs and Charts: Delivering A Message

Objectives
Part 1 • Describe what a spreadsheet is and
potential applications
• Distinguish between a formula and a
constant
• Open, save, print a workbook;
– insert and delete rows and columns

What is A Spreadsheet? • Use Page Setup to print worksheet and

preview before printing

1
 Spreadsheet Basics Spreadsheet Basics
 The spreadsheet is the microcomputer application that is most widely
used by managers and executives (i.e. a computerized ledger)
Cell - the intersection of a row and column form a cell in a
spreadsheet.
Cell references - each cell has a unique address or
reference such as A9 (cell located at column A and row 9).
Annual Report with Constant - an entry that does not change and may be
Graph can be numeric or descriptive text.
created easily with
Excel
Formula - a combination of numeric constants, cell
references, arithmetic operators, and/or functions that
produces a new value from existing values.
Function - a predefined computational task or calculation.

Excel 2007 Basics Excel 2007 Worksheet

The Ribbon

 Common user interface on all Office Cell

2007 applications
Cell A1
(Column A ,
Row 1) is
also the

 A worksheet is an Excel spreadsheet

Columns
Active Cell

 A workbook contains one or more

worksheets
Rows

 The Ribbon—Replaces the previous

Menu Bar and Tool Bar
 The Office Button-Save, Open and
worksheets

Print commands

2
 Modifying the Worksheet Modifying the Worksheet
 Insert Command  Delete Command
 To add individual cells, rows or columns  To delete individual cells, rows or columns

Modifying the Worksheet

 Page Setup Dialogue Box Part 2
 Page, Margins, Header/Footer, and Sheet Tabs

Click the Dialogue Box

Launcher at the Page
Setup Frame of the Page
Layout Tab

Gaining Proficiency:
Copying and Formatting

3
 Objectives Getting around the Worksheet

• Define, select and deselect cell ranges

• Differentiate between relative, absolute
and mixed addresses
• Format a worksheet
Range

• Change column widths

Range

 Range - rectangular group of cells, which are specified

by indicating the diagonally opposite corners.

Getting around the Worksheet Getting around the Worksheet

 Absolute reference - a reference that does not

change when copied. It is specified with a dollar
sign in front of both the row and column ($A$1). Relative Reference for
 Relative reference - a reference that adjusts Cell E5 = C5-D5
Cell E6 = C6-D6
during a copy operation and is specified without Cell E7 = C7-D7
etc
dollar signs (A1).
 Mixed reference - a reference that adjusts Absolute Reference for
Cell F5 = D6*$B$15
either the row or column reference but not both. Cell F6 = D6*$B$15
Cell F7 = D7*$B$15
It is specified with a single dollar sign ($A1 or etc

A$1).

4
 Excel 2007 Formatting Excel 2000 Formatting

Select a row and move your cursor to the

row’s border to use the row height • Double- click the row height handler so that the row height is the
handler. minimum height that can make the row’s cells’ content visible vertically.
•Column width - the width of a worksheet column can be changed the
same way that row height can be changed.

Right-click a row to access the Row

Height attribute.
• Row height - the height of a row in a worksheet. The row height changes
automatically as the font size changes.

Excel 2007 Formatting Excel 2007 Formatting

• Number tab – enables you to specify the type of value

contained in a cell and how it should be displayed.
• Alignment tab - you may align text within the cells either
horizontally or vertically and then choose left, center, right,
justify or centered.
• Fonts tab – you may format the size, colour, style, and font
family to be used.
• Border tab - enables you to create a border around a cell or
Format Cells Dialog Box
range.
• Fill tab - lets you choose a different color to shade the cell or
• The most commonly used formatting commands are available as buttons at range
the Home tab or by right-clicking the cell to be formatted.

5
The Number Tab in the Format Cells The Number Tab in the Format Cells
Dialogue Box Dialogue Box
• General - the default format for numeric entries and • Time - displays the time in various time formats.
displays the way it was entered. • Percentage - the number is multiplied by 100 before is
• Number - displays a number without the thousands displayed with a % sign.
separator comma and with any number of decimal places. • Fraction - displays a number as a fraction such as ¼.
• Currency - displays a number with the 1000 separator • Scientific - displays a number as a decimal followed by the
comma and an optional dollar sign and negative values (in exponent of base 10.
red or minus sign).
• Text - left aligns the entry; useful for numbers that are not
• Accounting - displays a number with the thousand used in calculations such as zip codes.
separation, optional dollar sign (leftmost aligned) negative
values in ( ) and zero values as hyphens. • Special - displays a number with extra characters such as ( )
around a phone number area code.
• Date - displays a date in various date formats.
• Custom - allows you to develop your own formats.

Objectives
Part 3 • Write simple formula for basic calculations
• Use existing function of Excel :
– SUM
– AVERAGE
– MAX
– MIN
– PMT function
Using Formulas & Functions – Use Goal Seek
• Create, view and print large spreadsheets

6
 Formulas Formulas

• A formula is an entry that performs calculation • Formulas use the following arithmetic operators
• The result of the calculation is displayed in the to specify the type of numeric operation to
cell containing the formula perform :
• A formula always begin with an “=“(equal) sign, + for addition
which defines it as a numeric entry
• In a formula that contains more than one - for subtraction
operator, Excel performs the calculation in a
specific order or precedence. / for division
– Formula = 5*4–3 = 17 * for multiplication
– Formula = 5*(4–3) = 5
• The values on which a numeric formula ^ for exponentiation
performs a calculation are called operands
(numbers, cell references).

Formulas Functions

• Let say we have a • Functions are built-in formulas that perform

range of cells certain types of calculations automatically
(B1B10) with
value (1  10). • The syntax or rules of structure for entering all
• To get the total functions is
(add all value in = Function name(argument1, argument2,…)
cell B1 to B10), we • Common functions are :
can just simply
=SUM() Total of arguments
write down a
formula in B11 as =AVERAGE()  Average of arguments
follow =MAX()  Maximum value in argument
=MIN()  Minimum value in argument
B11=B1+B2+B3+B4+B5+B6+B7+B8+B9+B10

7
 Functions (SUM) Functions (AVERAGE)

• Let say we have a range • Let say we have a

of cells (B1B10) with range of cells
value (1  10).
(B1B10) with
• To get the total (add all
value in cell B1 to B10),
value (1  10).
we can just simply write • To get the average
down a formula in B11 value
which is quite lengthy (1+2+3+4+5+6+7+8
= B1+B2+B3+B4+B5+ +9+10 / 10 = 5.5) in
B6+B7+B8+B9+B10 an array of numbers
• Using functions =SUM(), we can use the
we can just use a =AVERAGE()
simpler formula which is
function, which is
=SUM(B1:B10) =AVERAGE (B1:B10)

Functions (MAX & MIN) What-If Analysis

• A technique used to evaluate the effects of changing selected
• Let say we have factors in a worksheet.
a range of cells • This is a common accounting function that has been made easier
with the introduction of spreadsheet programs
(B1B10) • By substituting different values in cells that are referenced by
formulas, you can quickly see the effect of the changes when the
• To get the MAX formulas are calculated.
or MIN value in
Price of car $67,677 Car Loan
an array of Manufacturer's rebate $0 You can change the
numbers we can Down payment $13,000
value of the down
payment to determine
use the =MAX() Amount to finance $54,677 your monthly
payment to the bank
or MIN() Interest rate 3.85%
function, which Term (years) 7
=MAX(B1:B10) = 10
is Monthly payment ($743.60)
=MIN(B1:B10) = 1

8
 Using a Worksheet for
Analysis of a Car Loan
Analyzing a Car Loan

 Set up a worksheet
 Can I afford it? template with initial
conditions
 How do I  Use the PMT function
to computes the
calculate for: associated payment
 rebates for a loan.
To calculate PMT we
 down payments 
need to know interest
 interest rates rates divided by 12,
 years of loan length of the loan, and
amount of the loan
 B7=PMT(B5/12,B6*12,B4)
 Use Goal Seek to set
an end result

Setting up Goal Seek Setting up Goal Seek (Cont…)

• PMT Function
– A predefined formula that accepts one or
• Establish PMT more arguments as input, performs the
– interest rate indicated calculation and returns another
– term value as output
– down payment • Goal Seek command
• Use Goal Seek – Enables us to set an end result in order to
determine the input to produce that result
– change result
changing one
variable

9
 Managing a Large Worksheet Freeze Panes
1. Select the
• Scrolling shows specific rows and first row
below the
columns rows that
should be
• Freezing Panes keeps headings in frozen (Row
5).
sight 2. At the View
tab, click on
• AutoFill capability enter series into Freeze
Panes.
adjacent cells 3. Rows 1-4
are frozen
• Repeat specific rows as headings for and visible
no matter
printouts how far you
scroll.

AutoFill Handler Printing Repeating Rows

1. Select the cells that can
specify the pattern of the
succeeding cells. 1. Launch the
2. Drag the fill handler until it Page Setup
previews the last of the Dialogue
required value. You can move Box.
the handler back and forth. 2. At the Sheet
3. Release the fill handler when tabs,
you’re done. specify the
rows to be
repeated.
$1:$4
specifies
Rows 1 to 4
3. Press OK.

10
 Objectives
Part 4 • Introduction to Excel charts
• Elements of an Excel chart
• How to create a chart

Graphs and Charts:

Delivering A Message

Introduction to Excel Charts Introduction to Excel Charts

Historical CGPA of Student X
• A chart is a visual Period of Study CGPA 4

representation of numeric
3.33
values (data on a Trimester 1 3.33 3
3.21 3.27

2.86
worksheet) 2.64

• It helps viewers to spot

Trimester 2 3.21 2.48
CGPA

2
trends or patterns from Trimester 3 3.27
tabulated data more
quickly Semester 1 2.48
1

• What can you infer from

this student’s historical Semester 2 2.64 0
Trimester 1 Trimester 2 Trimester 3 Semester 1 Semester 2 Semester 3

CGPA? Semester 3 2.86

Period of Study

What can you easily spot from the chart now?

11
 Introduction to Excel Charts Introduction to Excel Charts

• A chart is created from data on a

worksheet
• The different parts of a chart are derived
from the way the worksheet data is
structured, e.g.:
– the numeric CGPA values becomes the
charted points
– the names of the study periods become the
points on the x-axis • A chart can be embedded into the same
worksheet that contains the data values

Introduction to Excel Charts Introduction to Excel Charts

• It can also be placed in a separate chart • It can even be embedded into another Office
sheet (better for large or complex charts) document e.g. Word and remain dynamic

12
 Introduction to Excel Charts Elements of an Excel Chart
y-axis or
Value Axis Chart Title

• A chart is dynamic because it is linked to

the data values from which it was created Major Gridline

• If the a data value is edited, then the chart Scale Value

Plot Area Minor Gridline

is automatically updated to reflect the Data Label

Axis Title
edited value Data Marker

Legend

x-axis or
Category Axis
Chart Area

Axis Title Category

Elements of an Excel Chart How to Create a Chart

Ahmad Benjamin Chitra
Element Explanation 1. Enter the data
Chart Area The area occupied by the entire chart, including legends, GPA for Trimester 1 2.2 2.62 3.09
labels, etc.
into the GPA for Trimester 2 2.5 2.46 3.07
Plot Area The area occupied by the data plotted on the chart.
worksheet. GPA for Trimester 3 2.47 2.39 2.41

Data Marker The chart’s physical representation of a data value from a data 2. Structure it
series. In this chart, the data markers are columns. They could appropriately
be squares on a line chart, or segments of a pie chart, etc.
with headings.
Data Series A set of related data values e.g. GPAs of students from
Trimester 1 is one data series, GPAs of students from 3. Select the range
Trimester 2 is another data series. of structured
Category A grouping of comparable data values from each data series
e.g. Chitra’s GPAs from the three data series are under one
data (let’s say
category since it is her personal performance trimester by A1:D4) and
trimester that we want to compare in this chart. press Alt + F1

13
 How to Create a Chart How to Create Chart
3.5

2.5

2
GPA for Trimester 1
GPA for Trimester 2
1.5 GPA for Trimester 3

0.5 5. To add more chart elements, select the chart

0
and click on the Chart Tools context menu, and
Ahmad Benjamin Chitra
choose Layout. Specify the chart title, axis
4. An unformatted chart is created on the same
labels, data labels, gridlines, etc as required.
worksheet.

14