Basic Excel Overview
©
Mike Splane 2017
Videos
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
Glossary
Cell Names
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.
Moving Around the Worksheet
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.
Entering Characters, Dates, and Numeric 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.
Editing Cell Contents
·
Select the cell and then click in the
formula bar to edit its contents, or select the cell and type in a new entry,
or select the cell; then press F2 to edit directly in the cell, or select the
cell and double click to edit directly in the cell.
·
Click and drag to move the contents of a
cell.
·
Use shortcut keys to undo or redo a
step: Ctrl + Z for undo, Ctrl + Y for redo.
Copying
There are several ways to copy and paste. Use
the mouse to highlight the contents in the cells that you want to copy from. Do not highlight the formula bar area! That will delete the cell contents!
·
Press Ctrl + C. Highlight the area to
copy into. Press Ctrl + V. Whatever you pasted is still on the clipboard, so
you can paste multiple times with this method.
·
Or, click on the Copy icon.
Highlight the area you want to copy into. Click on the Paste icon.
·
Or, click the Copy icon. Highlight the
area you want to copy into and press Enter, or highlight the upper left corner
of the area you want to copy into and press Enter. This will clear the
clipboard
·
Move the cursor around until you get a
small black cross in the lower right hand corner of the highlighted area.
Click and drag (down or across) to highlight the area you want the formula
copied into. Release the mouse.
Formatting Cells from the Home tab
·
The Font group contains icons for text
options (Font, Size, Bold, Italics, and Underline) you’re probably
familiar with from Word. It also contains an icon to add borders to cells. The
“A” icons grow/shrink the font size.
·
The Alignment group contains text
alignment options (Left, Center, Right, Indent, and Decrease Indent). It also
has an icon to merge/unmerge cells. Other icons let you position text
vertically in the cell, wrap text, or rotate text.
·
The Numbers group contains icons for
formatting numbers as accounting style, percents, or comma style. Two icons
increase or decrease the number of decimal places. A pulldown menu gives you
other formatting options.
·
The Styles group provides options to apply
special formatting.
Working With Columns and Rows
·
The Home tab, Cells group has two icons,
insert and delete, used to manage formatting for columns and rows. Options
include deleting, inserting, changing column width or cell height, and
hiding/unhiding columns.
·
You can find these same options by
rightclicking on a column or row heading.
·
You can also use the mouse to change row
heights and column widths. Just click and drag between the row or column
indicators.
·
To resize a column’s width to
match its widest content, doubleclick to the right of its columnletter
indicator.
The Format Dialog Box
·
To quickly select large areas of the
worksheet
·
mouse over large groups of cells,
·
or click on one or more column letters
to select columns,
·
or click on one or more row numbers to
select rows,
·
or click on the gray cell in the upper
left hand corner, above the 1, to select all of the cells in the worksheet.
·
After you have selected cells to format,
rightclick and select format cells to open a dialog box. You can also access
this dialog box by pressing Ctrl + 1
·
The numbers tab in the dialog box is
used to change the numbering style.
·
The alignment tab lets you choose how
letters are displayed within a cell.
·
Other tabs give you more options.
Copying Formatting
·
Select a cell with the desired format.
This is called the source cell.
·
Click the Paintbrush icon on the Home
tab.
·
Drag the mouse across cells, row numbers
(to select a row), or column letters (to select a column) to apply the format.
The format will be copied into the selected cells. Try using the arrow keys to
quickly apply the format to any cell next to your source cell.
The AutoFill Handle
·
You can use a builtin feature of Excel,
the AutoFill handle, to enter a series of dates; to extend a series of numbers;
to copy formulas; and to copy text.
·
Position the cursor in the lower right
hand corner of a range. A small + appears. Drag in any direction.
Working with Formulas
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 A Row
1 the content of cell A1 is a number with the value 3.
·
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,
will be 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, here 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 
Order
of Operations (PEMDAS)
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 14because Excel would do the addition
inside the parentheses first.
Formulas
Combining Strings of Text
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.
Displaying Formulas
Select
a cell and its formula will be displayed in the formula box located above the
worksheet 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.
Working
with large groups of cells:
·
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 term range is used to refer 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.

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 






Relative Cell References

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. 
Cell
references, when
written like those in the table above, are called relative cell references.
Cell addresses written in this format will change when the formula is copied
into other cells. The rowindicator numbers change when the formula is copied
into a different row. The columnindicator letters change when the formula is
copied into a different column.
Absolute and mixed cell references
You
can write cell references in a different form. Adding dollar signs to cell
references tells Excel that you don’t want those cell references to
change when the formula is copied. The part of the cell reference following
each $ will not change.
The
dollar sign does not change the result of the formula. = A1+B2 and = $A$1 +
$B$2 will display exactly the same thing.
Cell references can
have dollar signs in front of just the number, = C$3, or in front of just the
letter, = $C3. These are called “mixed cell references.” Cell references with dollar signs in
front of the letter and number, = $C$3, are called “absolute 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.
For a formula with a fixed reference to a
specific cell, use an absolute cell reference.
You
can combine absolute, mixed, and relative cell references in a single formula.
In the following table, the formula in cell C4 was copied 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 
One use for an
absolute reference is to divide numbers in several cells by the same divisor,
to find percentages. The formula can be written once, using an absolute cell
reference to refer to the cell containing the divisor. Then it can be copied
into other cells. For example = A4 / $B$7.
Working with Multiple Worksheets
Look at the bottom of
your worksheet. You are given three worksheets to work in when you open a new
Excel file. Click on a worksheet tab to move into a different worksheet.
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.
Formulas Using Cells in Other Worksheets
To write formulas
that refer to cells in other worksheets, add the worksheet name, followed by en
exclamation mark, before 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.
Some common error
messages:
Error
messages 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.
·
CIRCULAR If a cell
reference in a formula refers to the cell where the formula is entered, Excel
will not be use the formula. For example, if =C4+5 was entered into cell C4.
The “circular “error message will appear in the status bar.
Examples of commonly used formulas: There
is no one right way to write a formula; getting the correct result is what
matters. Spaces are not necessary in formulas, but were included in the
examples to make them easier to read.
This table is used with the sample
formulas below:

A 
B 
C 
D 
1 
25 
28 
95 

2 
42 
91 
14 

3 
3 
4 
5 

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 worksheet 
=
Sheet2!C4 
The contents of cell
C4 in Sheet2. 
Name _______________________________ Date___________ Section _______
Check for
Understanding – Excel Concepts 1
Y/N I understand how to navigate
the Excel worksheet.
Y/N I understand how to format
cells
Y/N I understand how to enter
(and edit) data into cells
Y/N I understand how to copy
and paste in Excel
Y/N I understand how to write and use
formulas.
Y/N I understand how to what a range is.
Y/N I understand cell references.
Y/N I understand error messages.
Note: I do not need to see a screenshot
for this assignment