[Type
Overview:
Let Excel do your math for you!
spreadsheet format to create a very simple budget.
Student Skill Level:
Basic
Requirements
:
Good mouse skills
(ability to move the mouse on t
he computer screen and click or
double click as required with minimal assistance).
Length:
3 hours (with a 10 minute break)
Objectives
The student will be able to understand the basics of using Microsoft Excel:
o
Understand the function of the File Tab
o
Use Ribbon, Tabs, and Groups to select commands
o
Select single cells and groups of cells in order to format them properly
o
Change Column Width
The student will use these skills to set up a mock budget:
o
Decide how the spreadsheet will appear
o
Enter information
using:
The keyboard
AutoFill
o
Create and edit formulas using the formula bar
This is the Example Budget
we will create in this class.
Better Technology, Onsite and Personal
2
Open Excel
The File Tab on the Ribbon
The Ribbon, Tabs, and Groups
There ar
e
two
ways to open Excel
:
Double click on the
icon on the desktop
OR
Click once
on the icon and then press the
enter
key on the keyboard
The icon will turn
blue
and then a small hourglass will appear as the program opens
.
Ribbon
Tabs
Groups
The area above the blank page is called the
Ribbon
.
To use commands and options available to you in
Excel
, simply click the
button
on the correct
Tab
:
Home, Insert, Page Layout, References, Mailings, Review, View, or Format.
Buttons
are arranged in
Groups
. In general, the most used commands are in the
Home
Tab. For
example, it contains the
Clipboard
and
Basic Text Groups
.
Pictured here is part of the
File Tab
in
Excel
2010. Clicking on this tab
gives you a
ccess to some of the most powerful
commands in
Excel
.
Save and Save As
to keep a copy of the
workbook
in the location of
your choosing (hard drive, flash drive, etc)
Open
to find and re

open a previously saved
workbook
for editing
Close
shut down the current
workbook
Info
information regarding the
workbook
including any possible
compatibility issues (older to newer versions)
Recent
quickly re

open
workbook
s you have viewed or edited lately
New
creat
e a brand

new
workbook
(allows you to choose a
template if you want)
Print
get your
workbook
off the computer screen and on to paper!
3
Special Note on the Ribbon: The Formulas Tab
T
he cursor, cells, and the formula bar
Cells
are boxes
in which
the
row
s
and
the
c
olumn
s
intersect. It (a cell) is bound on four sides like a
ny
other block
.
All types of information are kept in a cell
including numbers and text
.
Cells are delineated by a letter (across top of worksheet) and a number (down the side). For example,
cell
G3 is selected on this sheet:
Cursor
(HP).
In Excel, the
cursor is in
both
the
cell
and the
formula
bar
.
What is shown in the cell is not always what is in the
formula bar
as we will see
as we practice.
this: use the Formulas Tab, the Help menu, or Google. A word on each:
part
of the
Formu
las Tab. Click
the
Insert Function
button
This is one way to find
the name of a formula in
Excel. All math functions
are done by
name
.
The
Help menu
may also be used to search for formula names. Type
your question in and, hopefully, it will display an answer.
Last, but not least, you may type your Excel question into the Google search engine. Be sure to be very
specific with your question and E
xcel version, because Google will search the entire Web to find any
answer pertaining to almost anything in your question. This can mean a lot of irrelevant answers!
4
Select Cells
AutoFill
Formula bar
is t
he place where formulas are entered
; f
(Author U
below
the
Ribbon
with an
This is important because the formula bar is where you will make corrections to virtually anything in the
cells.
Click once in the proper
cell
then click once in the
formula
ba
r. You will see the
cursor
in the bar, and
then you are ready to make corrections (with the
Backspace
or
Delete
keys)
Before performing
any task in
Excel
, you must select the cells
you want to work with. Selected cells appear
highlighted on
your screen.
To select one cell
:
Click
the cell you want to select
The cell becomes the active cell and displays a thick border
Use
Scroll Bars
to move up and down or across the
worksheet
TAB
brings you across rows to the right
Enter
moves y
ou down the column
Shift/TAB
brings you across columns to the left
Arrow keys
bring you up, down, left, or right across cells
Page Down
brings you to the next page of worksheet
Page Up
brings you to previous page of worksheet
CTRL/Home
brings you to cell A
1 at the beginning of
worksheet
Selecting a Group of Cells
:
Position the mouse over the
first
cell you want to select
Click and hold the mouse button and drag the mouse to
highlight all the cells you want to select (a thick black line will
surround the sel
ected cells with the first cell in
white
, the rest
blue
)
AutoFill
is used on
patterns
of numbers or text in cells.
Enter the start of a sequence (
January
)
Position the mouse cursor over the
bottom right
corner
of
the cell (mouse turns into a plus sign (
+
))
Click and hold the mouse button and drag the mouse
cursor (
+
) over the cells you want to receive your data
The cells will be outlined in gray and the results appear in
small type
Let the mouse button up
and the cells will automatically fill
with your data
5
Change Column Width
Format Cells (for numeric values)
Note
:
AutoFill
will also fill in a number or text sequence
you
create, or
known sequences
(for example, days of
the week or months of the year)
Click
cell A2. Type
Job 1
Now use the
Enter key
to continue typing the
income
labels
Please type in
four
types
of
expenses
using four different expenses in this class)
Click
AutoFill
to
complete the sequence of months in Row 1, if you
Total Expenses
now hangs over our
January
column. Correct
this by changing the column width
empty cell)
:
Position the mouse indicator over the right edge of the column
heading. (The
column heading
is between the letters at the
the cursor appears as such:
Click and hold the mouse button, draggi
ng the column edge
until the dotted line displays the column width you want.
OR
Position the mouse indicator over the right edge of the column
heading and double click.
REMEMBER to select the cells you wish to
cells B2 to D14, we will use the mouse. Your
spreadsheet should look like this:
6
Click
Format Cells
at the
bottom
In the
Format Cells
Dialog
box, select:
Number
click on the word (under Category)
Use
1,000 Separator
click checkbox
Red
(negative numbers)
click on the red
colored number
Click
OK
Once you have selected the proper cells, go to
the
Home
Tab
, find the
Cells Gr
oup
, and click
Format
.
7
Enter Numeric Values (Expenses Only)
Formulas
Formulas with Numeric Values
January only
There are many different kinds of formulas.
ALL
formulas in Excel begin with an
equal sign (=)
.
We will enter formulas
manually
We will create several simple formulas. These formulas are the building blocks of every other one you
may use in Excel, including:
Adding
numbers
(starting with our
Groceries
expense and moving to
Job 1
and
Job
2
) (Cells B10
,
B
2, B3
)
Using the SUM formula with
cells
(not numbers!) (Cells B5 and B12
)
Using a subtraction formula (with cells) (Cell B14)
To account for this, we may enter the numeric
values for
Rent
,
Telephone
, and
Electric
.
Use the
Tab
and
Enter
keys to move from one
cell to the next.
Note the formula bar
it displays one number for
each cell (
B
7
is
900
and nothing more). Please
enter values now.
Leave Groceries blank.
We can use Excel to account for weekly
expenses, even in the monthly format of our
budget, by using an
addition formula
with
numbers
.
For each grocery expense, we see a string of
numbers added together in the formula bar, NOT
a single number.
So in our first
Grocery
cell,
B10
, we see:
=80+56+102.34+42.35
As we look at our example budget, we may quickly note that some expenses are in a monthly format,
while others are generally weekly.
Rent
,
Telephone
, and
Electric
bills are usually due
once a month
,
while
Groceries
may be a weekly or biweekly expense.
formulas!
January
together; you may move on to the other months on your own in class.
8
Formulas with Cell Values
January only
Remember, you may simply take your weekly shopping receipts and add them together by:
Clicking
on
ce on
the cell
Clicking
once in the formula bar
Typing
in a
plus sign
and the
amount spent
on groceries that week
needed to account for an extra grocery
trip. We would click
on cell B10
and add the new expense. Our formula would add it together:
=80+56+102.34+42.35
+92.75
(displaying
373.44
in cell B10
)
No need to wait until the end of the month! Excel will add, subtr
act, multiply, or divide any numbers and
display the result in the cell you choose.
SKIP
UP
TO
JANUARY
JOBS
(Income)
Totals
later)
Our
jobs (income)
may be tallied in the same
manner. Most people are paid weekly or biweekly.
create a simple addition formula in cell
B2
:
=250.23+250.10+213.62+200.01
and only one cell to deal with. Some formulas work better based on
cells
rather than individual
numeric values. Using
cell references
anging numerical values allows us to
correct mistakes, add numbers, add cells containing other formulas, or even add rows or columns and
Excel will
automatically correct
any formula containing those specific cell references. This is the fun
of using the sp
readsheet!
In our example budget, the
totals
and
differences
are easily calculated using built

in Excel formulas.
Remember, there are no spaces in any formula and capitalization does not matter.
Excel uses the following:
=
remember all formulas start wi
th the equal sign
FORMULA NAME
we will use the SUM formula with cell references to get our answer (totals)
(
open parentheses tells Excel that a cell reference follows
:
or
,
colons tell Excel to see the reference as
a group, commas
or math symbols
indicate
non

continuous cell references
**You may also use math symbols
(+

* /)
for non

see this in the difference formula**
)
closed parentheses tells Excel that the cell reference has
ended
Again, the results will displ
ay as a
single number in cell B2
. You will see the formula in the formula
bar. Please create another formula for Job 2 in
January only
(you may practice the rest later)
9
Formulas
Mixed (Cell
References and Math Symbols)
Look at the following examples for January:
For
Total Expenses
, our formula in cell
B12
will be
as follows:
=sum(b7:b10
)
N
ote the equal sign, name of formula,
and continuous cell reference
with the parentheses
and colon. We also see a blue square around the
cells being added. This is a nice visual reference we
ing the right numbers.
Once we press the Enter key, w
e see the formula in
the formula bar and the answer displayed in the cell
itself.
total income
is in cell B5
:
=sum(b2:b3
)
The
difference
formula is a
combination of cell references and math
signs:
=(b5

b12
)
This takes into account non

continuous
cells and allows Excel to correct the
answers if needed.
Once the difference formula is in place, change
any
number (including any number we entered in
the
Grocery
formula) in the January column
in cell B14, will change!
Please
use the remaining class time to practice
creating formulas for February and March.
Remember to change your cell references as you
create the totals and differences!
10
Resources
**These items are available in the NIOGA Library System!**
**Contact your
local library for assistance!**
Call Number
Author
Title
Date
005.54 HARV
Harvey, G
Excel 2010 for Dummies
2010
005.369 MCFE
McFedries, P
Teach Yourself Visually: Excel 2010
2010
005.446 RATH
Rathbone, A
Windows 7 & Office 2010 for Dummies
2011
005.369 SHOU
Shoup, K
Office 2010 Simplified
2010
005.54 SYRS
Syrstad, T
Using Microsoft Excel 2010
2011
005.369
W
EVE
Weverka, P
Office 2010 All

in

one for Dummies
2010
Edited 4/23/14