4/12/2018

INTRODUCTION TO EXCEL
Quantitative Methods for Financial Management
Professor: Mónica Oviedo

Contents

• The basics
• Functions and Formulas
4/12/2018

THE BASICS

Starting Excel
4/12/2018

Starting Excel

The commands you need are available in one control center called the Ribbon.
The three parts of the Ribbon are tabs, groups, and commands.

Creating a New Workbook

• When you start Excel,

you’ll see a new blank
workbook (Book1).
You can begin typing
in that workbook or
choose to create a
new workbook using
the File menu in the
Backstage view.
• You can also work
with Templates
(explore it AT THE
END of the class)
4/12/2018

Spreadsheets and Worksheets

• A spreadsheet is a grid of rows and columns in which
you enter text, numbers, and the results of
calculations.
• In Excel, a computerized spreadsheet is called a
worksheet. The file used to store worksheets is
called a workbook.
– Each workbook contains three worksheets by default– you
can add or remove as needed from there

The spreadsheet
• Excel spreadsheets organize
information (text and numbers)
by rows and columns.
• Columns appear vertically and are
identified by letters.
• Rows appear horizontally and are
identified by numbers.
• A cell is the intersection of a row
and a column. Each cell is
identified by a unique cell
reference.
4/12/2018

Ranges of Cells
• A group of selected cells is called a range. The range
is identified by its range reference, for example,
A3:C5.
– In an adjacent range, all cells touch each other and form a
rectangle.
• To select an adjacent range, click the cell in a corner of the range,
drag the pointer to the cell in the opposite corner of the range,
and release the mouse button.
– A nonadjacent range includes two or more adjacent
ranges and selected cells.
• To select a nonadjacent range, select the first adjacent range,
press the Ctrl key as you select the other cells or ranges you want
to include, and then release the Ctrl key and the mouse button.

Ranges of Cells

Adjacent range Nonadjacent range

4/12/2018

Selecting Nonadjacent Ranges

• Click and drag
to select the
first range.

• Ctrl-click and
drag to select
additional
ranges

(This cell is also

selected even though
it appears white).

11

Data Types

• Text: Letters and numbers combined in one cell

– Exception: Scientific notation
• 1.45E+05 (1.45 times 10 raised to the 5th power or 145,000).
• Numeric: Variety of formats
– General numbers, pates and time, percentages, fractions,
currency, etc.
• Formulas or functions: A formula is an expression
which calculates the value of a cell. Functions are
predefined formulas and are already available in
Excel.
4/12/2018

Aligning Cell Entries

• By Default:
– Text entries are left aligned and formula bar displays
actual content
– Number entries are right aligned and formula bar may
display different format (eg dates)

Data Entry
There are two ways to
enter information into a
cell:
1. Type directly into the
cell: Click on a cell, and
type in the data (numbers
or text) and press Enter.
2. Type into the formula
bar: Click on a cell, and
then click in the formula
bar. Now type the data
into the bar and press
Enter.
4/12/2018

Data Entry
• Open a blank Excel workbook
• Put your cursor in Cell B3 (Column B, Row 3)
• Enter the number 15000
– Right click on B3, select format cell. Explore formatting.
• Put the cursor in a different cell
– Enter ¾
• How does Excel interpret this? Explore formatting dates.
– Enter =3/4
• How does Excel interpret this? Explore formatting percentages.

Formula Bar Display

– Formula bar displays actual content of text content
– Formula bar may display different format of number
entries (eg dates)
4/12/2018

The Format Cells Dialog Box

• Six tabs that allow you to format different aspects of
your worksheet:

1. Number
2. Alignment
3. Font
4. Border
5. Fill
6. Protection

Number Formatting
• Select the cells that you want to format.
• On the Home tab, in the Number group, choose the Dialog
Box Launcher next to Number
4/12/2018

Number Formatting
• Formatting becomes the “property” of a cell.

• If you replace the contents of a cell, the formatting will

apply to the new contents as well.
Description of the available formats: here
Create or delete a custom number format: here

Text Control Options

• Merge across cells

• Wrap within cells
• Shrink to fit inside of a
cell
4/12/2018

Status Bar Functions and Customization

• Notice the functions

displayed on the
status bar.
• Right-clicking on the
status bar displays a
menu from which
you can add items to
or delete them from
the Status Bar.

Mouse Pointers in Excel

1. Select – main mouse pointer used for selecting cells. Click once in a cell
to select it or click and drag to select several cells.
2. Fill - appears after you have selected a cell (or cells) and will show only
at the bottom right of the selection (tiny box called the Fill Handle).
Using this will allow you to fill a series.
3. Insertion point - appears when you are typing or have double-clicked in
a cell. The flashing insertion point determines where new text and
numbers appear.
4. Column/Row selector - appears when you are over a column or row,
allowing you to click once to select the entire column or row
5. Resize - appears when you are between columns or rows, allowing you
to resize them
4/12/2018

FORMULAS AND FUNCTIONS

Formulas and Functions

• Formula: Simply a math problem done in Excel

• Function: Prewritten formula that helps simplify

complex procedures (for numbers and text).
4/12/2018

Formulas and Functions

Formulas and functions ALWAYS begin with an = sign. This “tells” Excel
that a calculation will need to be performed.

For example, cell A3 below contains a formula which adds the value
of cell A2 to the value of cell A1.

Here, cell A3 below contains the SUM function which calculates the sum of
the range A1:A2.

Creating Formulas
• Start with “ = ”
• Use cell and range references when possible
• Arithmetic operators in formulas:
– Addition: + =A1+3
– Subtraction: - =100-B3
– Multiplication: * =A1*B1
– Division: / =D1/100
– Exponentiation: ^ =A2^2
– Percent: % =3% (=0.03)
• Grouping: ( )
4/12/2018

Complex formulas
• You can use several operations in one function
• You can group those operations with parentheses

• Examples

=3*2+1

=c1*(a1+b1)

=(100*a2-10)+(200*b3-20)+30

=(3+2*(50/b3+3)/7)*(3+b7)

27

Formulas and Functions

Operator Precedence
• Excel uses a default order in which calculations occur.
If a part of the formula is in parentheses, that part
will be calculated first.
4/12/2018

Order of Operations
• When using several operations in one formula, Excel
follows the order of operations for math.
– first: all parentheses - innermost first
– second: exponents (^)
– third: all multiplication (*) and division (/). Do
these starting with the leftmost * or /
and work to the right.
– fourth: all addition (+) and subtraction (-). Do
these starting with the leftmost + or -
and work to the right.

The value of: 3 + 2 * 5 is 13, NOT 25!

29

Order of operations Example

30
4/12/2018

Modifying Formulas
• Can modify in one of two places:
– Formula Bar
– In cell

Insert a Function: the Function Wizard

• Clicking the Insert Function button displays the Insert
Function dialog box.
• Function Arguments: the cell or range of cells to be
included in the function
4/12/2018

Insert a Function
• Every function has the same
structure. For example,
SUM(A1:A4). The name of
this function is SUM. The
part between the brackets
(arguments) means we give
Excel the range A1:A4 as
input.
• In the 'Insert Function'
dialog box, search for a
function or select a function
from a category. For
example, choose COUNTIF
from the Statistical
category.

Insert a Function

The 'Function Arguments'

dialog box appears.
• Click in the Range box
and select the range
A1:C2.
• Click in the Criteria
box and type >5.
• Click OK.

Result. Excel counts

the number of cells
that are higher than 5.

Note: instead of using the Insert Function feature, simply type

=COUNTIF(A1:C2,">5"). When you arrive at: =COUNTIF( instead of typing
A1:C2, simply select the range A1:C2.
4/12/2018

Function AutoComplete
• Jumps into action once you type “ = ” and the
beginning letters of a function in a cell

Choose from the wide variety of functions

suggested by Formula AutoComplete

More on Functions
• The input for a function can be either:
– A set of numbers (e.g., “=AVERAGE(2, 3, 4, 5)”)
• This tells Excel to calculate the average of these numbers.
– A reference to cell(s) (e.g., “=AVERAGE(B1:B18) or “=AVERAGE
(B1, B2, B3, B4, B5, B6, B7, B8)”
• This tells Excel to calculate the average of the data that appear
in all the cells from B1 to B8.
• You can either type these cell references in by hand or by
clicking and dragging with your mouse to select the cells.
4/12/2018

Cell and Range References

Advantages:
• When references are used, formulas can be copied to
other cells.
• Since a reference refers to a cell or a range of cells,
the formula results are automatically recalculated
when the data is changed in the referenced cell(s).

Using Cell References in Formulas

• Relative: When you copy a

formula, the cell references
update automatically and Relative reference
refer to new cells relative to
the new formula cell. Absolute reference

• Absolute: Absolute
references always refer to
the same cell, regardless of Note! Absolute cell
which cell the formula is references are denoted
with $ signs.
moved or copied to.
4/12/2018

Examples
• The following all refer to the same cell
A1
$A$1
$A1
A$1

• The only difference between these cell references

relates to what happens when you copy a formula
that contains the cell reference.

39

Relative Cell Reference

• A1 This is a "relative cell reference".

– Changing the column: If I copy this cell reference to
another cell:
• the "d" will increment one letter for every cell that I move over to
the right.
• The "d" will decrement one letter for every cell that I move over to
the left
– Changing the row: If I copy this cell reference to another
cell:
• the "9" will increment by one for every cell that I move down.
• The "9" will decrement by one for every cell that I move up

40
4/12/2018

Absolute Cell Reference

• $A$1 This is an absolute cell reference.
– If I copy a formula with this cell reference, the cell
reference will NOT change AT ALL.

41

Mixed References
• $A1 and A$1 These are "Mixed" cell
references:

• $A1
– The "d" will stay the same when you copy the cell, but the
"9" will change.

• A$1
– The "d" will change when you copy the cell, but the "9"
will stay the same.

42
4/12/2018

Copying Formulas
• Copy and Paste OR AutoFill
• If you use the fill handle, you can click the AutoFill Options
button to choose how to copy or fill the information from the
source to destination cells

Displaying Formulas
• Choose to display formulas, rather than the resulting
values, in cells

Tip! Use the Show Formulas button to toggle between

displaying formulas and values
4/12/2018

AutoSum
• Automatically sums a column or row of numbers.

• Here, cell B9 was active and then the AutoSum command was
issued; Excel will propose to add the numbers above the cell

Common errors in formulas

The following are some errors that may appear in a spreadsheet (there are others too).

Error Explanation
####### Cell is too narrow to display the results of the formula. To fix this
simply make the column wider and the “real” value will be displayed
instead of the ###### signs. Note that even when the ###### signs
are being displayed, Excel still uses the “real” value to calculate
formulas that reference this cell.
#NAME? You used a cell reference in the formula that is not formed correctly
(e.g. =BB+10 instead of =B3+10)
#VALUE! Usually the result of trying to do math with a textual value.
Example: =A1*3 where A1 contains a word
#DIV/0! Trying to divide by zero. Example: =3/A1 where A1 contains 0 (zero)
Circular Reference Using a formula that contains a direct or indirect reference to the own
cell of the formula. Example: putting the formula =A1+1 in cell A1 or
putting the formula =SUM(A1:B2) in any of the cells A1, B1, A2, B2
4/12/2018

Copy and paste

• Put your cursor in cell G2.
• Enter a formula such as
=10+5
• Put your cursor in cell G3.
– Type the formula
= G2 – 1
• Copy this formula from G3 paste it into G4, G5, G6
– What happens to the formula as you paste it?

Copy, Paste, and Paste Special

• Select the range of cells that you want to copy
• Point to where you want to paste them and select paste
special
4/12/2018

References
• Liengme, B. V., & Ellert, D. J. (2009). A Guide to
Microsoft Excel 2007 for Scientists and Engineers.
Amsterdam ; Boston, Academic Press/Elsevier.
(Online resource at UAB Library).
• Office Training Center: Excel training