INTRODUCTION TO BASIC EXCEL SPREADSHEETS

1
INTRODUCTION TO BASIC EXCEL SPREADSHEETS
2
Spreadsheet Software

• Software designed to perform complicated numeric
  calculations rapidly and accurately.
• Provides tools that assist in understanding the
  relationship that exists between numeric data
  such as cost and profit or expense and income.

3
A Spreadsheet

• A spreadsheet consists of a grid of cells that
  can contain text, number (values), equations, or
  functions.

3. Values are used in equations and functions
4. Equations and functions operate on values
2. Text is used for labels
1. Cells are identified by column letter and row
number. This is cell E3.
4
A Spreadsheet

• Column width, row height, font and cell format
  can all be adjusted using commands in the format
  menu.

2. Cell fill color and border color and weight
can be specified.
3. Numbers can be formatted as currency,
percentages, dates, etc.
1. Font type, color, size and weight can be
changed
5
Formulas (equations)

• Calculations always begin with an equal sign
  followed by the formula or function.
• After typing the formula, pressing Enter displays
  the results of the formula in the cell the
  formula remains displayed in the formula bar.

2. Cell E3 shows the results of equation
1. The formula bar shows the equation typed in
cell E3
6
Formulas

• What formula would you type in cell B6 to add up
  the January profits for the three divisions?

B3B4B5

Its common for new users to forget to type the
equal sign for formulas.
B3B4B5

7
Functions

• What if you have a long list to add up?
• Rather than having to type lengthy formulas,
  Excel provides functions.
• In this case you could use sum(B3B17)

Functions provide shorthandmethods for
specifyinglengthy or complicated formulas and
calculations
The colon is used to specify a range of cells, B3
through B17
8
Functions

• Fortunately, you dont need to memorize all the
  functions to use them. All you need to so is type
  the equal sign in a cell to access the functions
  menu.
• Click the Dropdown Arrow to access the list of
  functions.

9
Functions

• Fortunately, you dont need to memorize functions
  to use them. All you need to so is type the equal
  sign in a cell to access the functions menu.
• Click the Dropdown Arrow to access the list of
  functions.
• Click the desired function.

10
Functions

• A dialog box is displayed to assist you in
  setting up the function.

Enter cells, or cell range here (or select them
with your mouse)
Click OK to insert the function in the spreadsheet
11
Functions

• Excel provides hundreds of functions for many
  categories of problems
• Financialrate()
• Statisticalgrowth()
• Math and Trigsqrt()
• Logicif()
• Date and Timenow()

• Common functions that well use in this class
  include
• Sumsum(A1A12)
• Averageaverage(A1A12)
• Minimummin(A1A12)
• Maxmax(A1A12)
• Ifif(A1gt12, okay)

12
Copying Functions and Equations

• If you copy a function from one cell to another,
  the cells referenced in the function will
  automatically change to accommodate the new
  location. This is called a relative reference.
  For instance, below SUM(B3B5) becomes
  SUM(C3C5) when copied.

13
Copying Functions and Equations

• By placing a dollar sign before the column letter
  or row number in a formula you can prevent the
  cell reference from changing when copied. This is
  called an absolute reference. Below, B3, the
  interest amount, has been made an absolute
  reference.

14
Copying Functions and Equations

• You can copy and paste a function using commands
  in the edit menu.
• If you wish to paste a function several times in
  sequence, you can use the cells fill handle to
  drag and copy a formula or equation down, up,
  right, or left.

Fillhandle
15
What if?

• The purpose of spreadsheets is to provide users
  with insight and an understanding of complicated
  numerical data and the relationships between that
  data. What if the South East division earned
  20,000 in profits in January?

A change here.
16
What if?

• The purpose of spreadsheets is to provide users
  with insight and an understanding of complicated
  numerical data and the relationships between that
  data. What if the South East division earned
  20,000 in profits in January?

Effects the data here, here here
17
What if?

• Excel provides a number of what if tools. The
  solver tool for example allows you to work
  backwards to find out what needs to be done to
  meet a stated goal.

Here the solver is asked What monthly profits
must each of the divisions earn for us to meet a
goal of 200,000 in total profits.
18
Other Cool Stuff Charts

• Select a range of cells and click the Chart
  Wizard button to create a bar, line, or pie chart.

19
Summary

• This short presentation has introduced you to
  basic Excel concepts and features
• Cells, labels, and values
• Formatting text and cells
• Changing row height and column width
• Formulas and functions
• Relative and absolute cell references
• Copying functions
• What if tools
• Charts