Basic Excel Overview
Part 1
©
Mike Splane 2017
Excel
Overview (#1)
Time = 9:59
https://www.youtube.com/watch? v=eI_7ocE3h0
Calculation
Basics & Formulas (#3)
Time = 13:47
https://www.youtube.com/watch?
v=RgvdCHjOKYg
Video – for the MAC 
Excel Basics
Time = 7:08
https://www.youtube.com/watch?v=kkNY1ysaOe0&spfreload=10
Contents
Entering
Characters, Dates, and Numeric Data
Formatting
Cells from the Home tab
Formulas
Combining Strings of Text
Ranges
 Working with Large Groups of Cells
Mixed
and Absolute Cell References
Formulas
Using Cells in Other Worksheets
An Excel
spreadsheet is similar to a tictactoe grid, only much bigger. The squares in
the worksheet are called cells. Every cell has a name.
Select a cell with the mouse and you will see a letter highlighted at the top
and a number highlighted on the left. The name of the cell is the combination
of that letter and number. The name of the cell also appears in the Name Box,
above column A.
Pressing Tab moves the cursor to the next cell on
the right, Enter moves it to the next cell down, Shift + Tab moves it to the
next cell on the left. You can also shift the cursor with the arrow keys and
the Home, End, Page Up, and Page Down keys. Use the Ctrl key with the arrow
keys to move the cursor the maximum distance. You can also move the cursor into
a new cell by typing the cell address in the Name Box. Doubleclicking on a
cell wall moves you to the edge of a row or column of data.
Select a cell. Enter text or numbers by typing in
the cell, or in the formula bar that is centered
directly above the worksheet. Enter dates as mm/dd or
mmdd for the current year. Enter the date as either
mm/dd/yyyy or mmddyyyy for other years.
There
are several ways to copy and paste. Start by selecting the cells you want to
copy from. Use the mouse to select the cells that you
want to copy from by clicking on one cell, or by dragging across multiple cells
using the mouse.
WARNING! Do not mouse over the text
in one cell or in the formula bar area. That will delete the cell contents!
Autofill Handle Video Time = 1:17
https://www.youtube.com/watch?v=Iuy_8CirHcY
A
worksheet consists of a set of cells (boxes) aligned in rows and columns. Each
cell can contain a string (letters or numbers mixed with letters), a number, or
a date. Each cell is referred to by the letter of the column it
appears in, followed by its row number.

Column A 
Column B 
Column C 
Column D 
Row 1 
3 
5 


Row 2 
Text Message 
March 18, 2002 


·
In Column B Row 1 the content of cell
B1 is a number with the value 5.
·
In Column A Row 2 the
content of cell A2 is a label “Text Message.”
·
In Column B Row 2 the
content of cell B2 is a number, 37333. The number has been
formatted to appear as a date. Why is it a number? So
you can add and subtract dates or easily put them in calendar order. Excel
assigns a number to each date by counting the number of days since January 1,
1900.
·
A cell can also contain a formula. A formula is a mathematical
equation. Formulas always start with an = sign. The result of calculating the
equation will appear in the cell. The actual formula appears in the formula bar
area, above the worksheet, when the cell containing the formula is selected.
·
If you use a cell name (called a cell reference) in a formula, Excel uses the value found in that cell to calculate
the result of the formula. For example, if you enter =A1 + 5
as a formula in cell C1 Excel will look into cell A1. It sees a 3 in that cell, so it converts the formula into the form = 3
+ 5. The answer, 8, is displayed in cell C1. The
formula bar will display the formula, =A1+ 5.
Remember: formulas always start with = signs!
When you enter these statements or
formulas in cell C1 this is what will be displayed:
These
strings in cell C1 
Display this in cell C1 

These formulas in cell C1 
Display this in cell C1 
5+3 
5+3 

=5+3 
8 
B1 + 3 
B1 + 3 

=B1 + 3 
8 
A1+B1 
A1+B1 

=A1+B1 
8 
5+B1 
5+B1 

=5+B1 
10 
If a formula
contains multiple calculations, Excel calculates the parts of the formula in
this order: everything in parentheses, then exponents, then multiplication and
division, and finally addition and subtraction, always moving from left to
right. For example = 3+4*2 will display 11 because Excel would do the
multiplication first, while = (3+4)*2 will display 14
because Excel would do the addition inside the parentheses first.
To combine text
strings use the ampersand (&) symbol
in the formula. Strings must be enclosed in quotes.
For example =”Splane”&”,”&”Mike”
would display Splane,Mike.
You don’t have to type a string, instead you can
use a cell reference to a cell with text in it. If cell A4 contained the name Splane, and
cell B4 contained the name Mike, you could combine the names using the formula
=A4&”,”&B4. Note that cell references are NOT in quotes.
Select a cell and
its formula will appear in the formula box located above the worksheet grid area.
To view all of the cells as formulas, press Ctrl + ~ (tilde). The tilde key is
in the upper left corner of the keyboard. This same keystroke combination
returns you to the normal view. There is also a checkbox on the View tab where
you can reset the worksheet to display formulas.
·
Rather than listing all of the cells in a large block of the worksheet you can use a shortcut method to describe any
group of cells that form a rectangle.
·
The word “range” refers to a rectangular block of cells.
·
Enter the cell address from the upper lefthand corner of the range,
followed by a colon and the cell address from the lower righthand corner.
Excel will recognize this as a range and will include all of the cells inside
its borders when it performs calculations.
·
You can also assign a word to describe a range. Start by mousingover the cells in the range. Then type the word you
want to use to describe the range into the name box and press enter key. The
name box is right above column A in the worksheet.
·
If you use a range name in a formula, do not include quotation marks.
When you want to use a range in a formula,
rather than listing every cell in the range there is a shortcut, called range
notation.
To use range notation, type the range’s
topleft cell address, a colon, and its bottomright cell address.
In the following table, Range 1 is described in cell notation as B3:C4, Range 2 as E2:E4, and Range 3 as C6:E7.
You can also assign a text name to a range and use it instead of using
range notation.

A 
B 
C 
D 
E 
F 
1 






2 




Range 2 

3 

Range 1 
Range 1 

Range 2 

4 

Range 1 
Range 1 

Range 2 

5 






6 


Range 3 
Range 3 
Range 3 

7 


Range 3 
Range 3 
Range 3 

8 







C 
D 
E 
Comment 
4 
= A1+B2+C3 

= C1+D2+E3 
Letters change when you copy a
formula into a different column. 
5 




6 
= A3+B4+C5 

= C3+D4+E5 
Numbers change when you copy a
formula into a different row. 
The formula in cell C4 was copied into cells E4, C6, and E6. Note how the letters
and numbers change. 
Adding dollar
signs to cell references is useful if you want to create a set of formulas that
always refer to cells in the same row, the same column, or the same cell.
The letter or
number part of the cell address that has a $ in front of it never changes when
the formula is copied into other cells.
Cell
references can have dollar signs in front of just the number: = C$3, or in
front of just the letter: = $C3. These types of cell references are called “mixed” cell references.
If you want
to create a set of formulas that will always
refer to either one specific row: A$1, or one specific column:
$B2, use a mixed cell reference.
Cell
references can have dollar signs in front of both the letter and number: =
$C$3. These types of cell references are called
“fixed” or “absolute” cell references.
For a
formula with a fixed reference to a specific cell, or a specific range of
cells, use an absolute cell reference: $C$4.
One use for
an absolute reference is to divide numbers in several cells by the same
divisor, to find percentages. For example = A4 / $B$7.
You can combine absolute, mixed, and relative cell
references in a single formula.
In the following table, the formula in cell C4 was
pasted into other cells. Observe which row and column numbers changed
and note the effect of the dollar signs.

C 
D 
E 
Comment 
4 
=
A$1+$B2 

=
C$1+$B2 
Notice
that only the A and the 2 (in cell C4) change. Because of the $ symbol, the
others don’t change. 
5 




6 
=
A$1+$B4 

=
C$1+$B4 
There is no one right way to write a
formula; getting the correct result is what matters.
Use this table with the sample formulas
below:

A 
B 
C 
D 
1 
25 
28 
95 

2 
42 
91 
14 

3 
3 
4 
5 

SAMPLE FORMULAS: Spaces are not necessary in formulas, but were included in the examples
to make them easier to read.
Type of
Equation: 
Entered in Cell
D3: 
Result Displayed in D3: 
Addition of Two Cells 
= A2 + B3 
46 
Addition of a Constant 
= B1 + 25 
53 
Addition of a Row of Cells 
= SUM (A1:C1) 
148 
Addition of a Column of Cells 
= SUM (B1:B3) 
123 
Addition of a Range of Cells 
= SUM (B1:C3) 
237 
Addition of Scattered Cells 
= SUM (A2,B1,C3) 
75 
Subtraction of a Constant 
= C1 – 10 
85 
Subtraction of a Cell 
= B2 – B1 
63 
Multiplication by a Constant 
= A3 * 20 
60 
Multiplication of Two Cells 
= B3 * C3 
20 
Multiplication by a % 
= A1 * .40 
10 
Multiplication by a % 
= B1 * 25% 
7 
Division by a Constant 
= C1 / 5 
19 
Division by a Cell 
= A2 / C2 
3 
Exponentiation (Squaring) 
= B3 ^ 2 
16 
Exponentiation (Cubing) 
= A3 ^ 3 
27 
Square Roots 
=SQRT(A1) 
5 
Square Roots 
= A1 ^ 0.5 
5 
Cube Roots 
= B1 ^ (1/3) 
3.036589 
Increasing by a Percentage (4%) 
= A1 + (A1 * .04) 
26 
Increasing by a Percentage (4%) 
= A1 * 1.04 
26 
Increasing by a Percentage (4%) 
= A1 + (A1 * 4%) 
26 
Decreasing by a Percentage (8%) 
= A1  (A1 *.08) 
23 
Decreasing by a Percentage (8%) 
= A1 *.92 
23 
Decreasing by a Percentage (8%) 
= A1 – (A1 * 8%) 
23 
Calculate a Percentage (Part/Sum) 
=A3 / $D$3 
25% (Format as a %) 
Average of a Column 
= AVG (B1:B3) 
41 
Average of a Row 
= AVG (A3:C3) 
4 
Average of a Range 
= AVG (B1:C2) 
57 
Formula referring to a cell in
another sheet 
= Sheet2!C4 
The contents of cell C4 in Sheet2. 
Look at the bottom of your worksheet.
In older versions of Excel, you are given three
worksheets to work with. In newer versions you are given
one worksheet. Click on the + icon
to add more.
To move into a different worksheet just
click on the worksheet label.
By rightclicking
on a worksheet tab, you can insert and delete worksheets, move and copy
worksheets, rename a worksheet, select multiple worksheets to work on
simultaneously, and add colors to the tabs.
To write
formulas that refer to cells in other worksheets, add the worksheet name,
followed by en exclamation mark, before typing the
cell address. For example, =Sheet2!C4 will show the
contents of cell C4 in Sheet 2. If a worksheet is renamed,
Excel automatically corrects any cell references that referred to cells in the
renamed worksheet.
Error
Messages Video
Time = 4:28
https://www.youtube.com/watch?v=1_tr5IK6LpY
Error messages usually start with a pound sign (#).
·
##### If you see railroad tracks, your column is too narrow. Solution  widen
the column.
·
#DIV/O ! You are dividing by an empty cell or zero. Solution  fix the
formula’s denominator to refer to a cell that contains a nonzero number.
·
#REF ! Your formula refers to a range or cell that no longer exists, due to a
change in the worksheet.
·
#NAME ? Your
formula contains text that Excel doesn’t
recognize. This could have many causes. Usually it’s
a typo or missing punctuation in a formula, or a misspelled function name.
·
#VALUE ! You used the wrong data type as the
argument for a function.
·
#N/A Excel couldn’t
find a value in a list. This often appears as the result of a LOOKUP type of
function when the source cell is empty, or when the items in the reference
table are not in alphabetical order.
·
#NUM ! The calculated number is too huge or tiny to display.
· CIRCULAR If a cell reference in a formula refers to the cell where the formula is entered, Excel will not be able to use the formula.
For example, if =C4+5
was entered into cell C4. The “circular “error message will appear
in the status bar.
For some versions of Excel for the the
MAC, a circle icon will appear in the cell, which can easily be mistaken for a
zero, without giving you any error message. BOO Microsoft!
AutoFill Handle 
The small black plus sign that appears
when you move the mouse pointer over the lower right corner of a cell or
group of cells. 
Cell
References 
Cell references are pointers to a specific
cell in the worksheet. They consist of a letter (or letters) representing the
column and a number representing the row. For example C4. Cell references can include dollar
signs. C4, C$4, $C4 and $C$4 all
refer to the same cell. Excel automatically alters a cell
reference when a formula is copied into other cells,
except for the parts of a cell reference that are preceded by the dollar
sign. Those stay the same. Cell references with one
dollar sign are referred to as mixed cell references. Those with two dollar signs are called fixed cell references. 
Cursor 
A shape showing the location of the mouse
pointer on the screen. The shape changes depending on what you are trying to
do. For example, when you use the AutoFill handle the cursor changes to a
small black plus sign. 
Dialog
Box 
A screen that will open when you need to
make complex changes to part of your work. The dialog box will appear
automatically when you click on menu items or icons if Excel requires more
information to complete a task. You can also manually open dialog boxes by
clicking on the “dialog box launcher” icons at the bottom of the
ribbon. These dialog box launcher icons look like
tiny squares with an arrow inside. The arrow will be pointing down and to the
right. 
Formula
Bar 
The wide white bar that appears above the
cells. 
Function
Keys 
Most keyboards contain a row of keys
labeled F1, F2, F3 … through F12. These keys are called function keys. Pressing a function key performs
a task. For example pressing F1 is the shortcut for
opening the help page and pressing F12 opens the filesave dialog box. 
Help
Options 
To get help finding a solution to a
problem, or to get more information on how to perform a task, you can click
on the question mark found in the upper right part of the screen. Pressing
the F1 function key will also work. 
Hot Keys 
Keyboard shortcuts are also known as hot keys. For example pressing
the Shift key and the F11 function key together will add a new worksheet to
the workbook. I show the shortcuts in brackets [Shift
+ F11] 
Keyboard
Shortcuts 
Usually you click on an icon to perform a
task, but you can often perform the same task by typing a short series of
keystrokes. If you move the cursor over an icon and hold it
there you will see the shortcut for that icon. Keyboard shortcuts to find the icons used
in these lessons will be shown inside brackets and
in red font. [ALT H B] Press
the keys one at a time, in a sequence, not all at the same time! The plus signs are
not typed when you use the shortcuts. The letter combinations ALT,
ESC, CTRL, TAB, HOME, END, and SHIFT each refer to a single keystroke. Pressing the ALT key will
display several keyboard shortcuts at the same time. Note: The MAC does not
follow the same set of keyboard shortcuts. Shortcuts starting with ALT are
only for use in a PC. For
keyboard shortcuts starting with CTRL use the
Command key. Get help here: Office
for Mac Keyboard Shortcuts  Excel https://www.youtube.com/watch?v=60tEVwXiTEs 25
Basic Mac Keyboard Shortcuts 
Menu 
When you click on the righthand button on
your mouse (rightclick) a list of options will
appear on the screen. The list is called a menu. To
use the menu, click on any item in the list. To close the menu, click on any
other part of the display area. If you see a small triangle, pointing
down, on the right hand side of an icon, clicking on that triangle will open a
menu. This type of menu is called a dropdown list. 
Name
Box 
A small white box that appears above
column A, next to the formula bar. 
Quick
Access Toolbar 
A small rectangle appearing in the upper
left corner of the screen, containing a small group of weird symbols. The
symbols are called icons. 
Range 
A group of cells that form a rectangle. 
Ribbon 
The area across the top of the screen that
is about an inch high and is covered with weird
symbols. The symbols are called icons. You can move the cursor on top of an
icon to see what it will do when you click on it. The ribbon is too long to show all of the
icons at the same time, so it is divided into
sections. The names of these sections are shown in
tabs just above the ribbon. Clicking on a tab will change which set of icons
you see. In Office 2010
the first tab in the ribbon is named File and the second tab is named Home.
The ribbon can be hidden. If you do not see it, you
can unhide it by clicking on the icon on the upper right side of the window
that looks like a smile. The ribbon for Office 2007 has some small
differences from the 2010 ribbon. The first tab in the 2007 version, File, is shown as a circle with a fourcolor flag inside it. The
method of unhiding the ribbon is also different. To unhide the ribbon click
on the right end of the quick access toolbar If you are working with a graphic,
additional tabs will appear on the ribbon. Icons on these tabs are used for formatting and finetuning the graphic. 
Right
Click 
Mice and most laptops touchpads have two
buttons. The right one can be clicked on to display
a popup menu. If your laptop touchpad does not have the two buttons, you may
be able to tap it with two fingers, at the same time, to show the popup
menu. 
Saving
your work 
If you want to keep your Excel worksheets
to use them again you need to save the Excel
workbook as a file. Pressing the
F12 function key is a quick way to access the Save As dialog box. In the
dialog box you create a name for the file, select a
format for the file, and select a location for the file. The most commonly
used locations for storing files are either on the desktop or in the My
Documents library. 
Screen Clip or Screen Shot 
Both these terms refer to the same thing, a picture showing the
contents of an open window on the desk top. The keyboard shortcut method to create a screen shot is to: 1. Click on the window you want to take a picture of. 2. Press the ALT key and the Print Screen key to take the picture.
(Command Shift 4 in a MAC) 3. Switch to your Word document window. 4. Press Control and the V key to paste the screenshot into your
document. 
Scroll
Bar 
Two rectangles, one horizontal and one
vertical, at the edge of the screen. Clicking in the scrollbar will cause the
area of the worksheet that is displayed to shift up
or down, or left or right. 
Status
Bar 
A rectangle across the bottom of the
screen that contains icons and information about your Excel worksheet. 
Troubleshooting
Methods 
If Excel freezes as you are trying to perform
a task, or the icon you are trying to use is inactive, you are probably in
the middle of some other task within Excel. Hit the ESCAPE key two or three
times and try again. If you make a mistake while working in any
Office application you can undo your most recent
step by pressing Ctrl and Z at the same time. You can continue pressing this
keystroke combination reverse multiple steps. Pressing Ctrl and Y is the shortcut to
redo a step. If your application freezes completely,
press CTRL and ALT and 
Workbook 
A set of Excel worksheets (pages) saved
together in one file. 
Worksheet 
An Excel page. You can see the names of each
worksheet at the bottom of the screen. The names are shown
on tabs. Clicking on a tab will move you into that page. A worksheet name can contain up to 31
characters. It must not contain a colon, back slash, forward
slash, question mark or asterisk. Forbidden : \ / ? * The name can contain spaces but that can cause problems
when writing formulas so it is good practice to not use them. You can click and drag on worksheet tabs
to change their order, or rightclick on them for a menu listing other
worksheetrelated tasks. 