Juice Excel Training Worksheets

Juice Excel Training Worksheets
Document Outline
Excel fundamentals
Keyboarding
Shortcuts (Windows)
Shortcuts (Mac)
Exercises (5)
Absolute and relative references
Data and functions

Find and replace
Date and time
Functions (2)
Text functions (2)
Text function exercises (2)
Vlookup
Vlookup exercises (3)
Data filters
Data filter exercises (3)
Presenting data
In-cell graphics
Conditional formatting
Conditional formatting examples (4)
Conditional formatting exercises
Chart Exercises (4)
Keyboard Shortcuts (part 1 of 2)
What: Allows you to navigate excel and perform tasks much quicker than when a mouse has to be invo
Why: Learning the Keyboard shortcuts will drastically improve your efficiency when working in excel
Note: Not all keyboard shortcuts will work in precisely the same way on Excel for Mac. In general, the "Apple
Menu-based Keyboard shortcuts

Applicable to all Microsoft Office applications.
Looking at the toolbar above, we can see that one letter for each menu item is underlined. If yo
the corresponding dropdown will open. From here press the letter for the item you want to acces
For example, to open up a simple data filter, one would normally have to click on Data -> Filter -
Using a keyboard shortcut, one would press ALT + D + F +F (that is, the alt button, then the "D",
hen a mouse has to be involved.
cy when working in excel
r Mac. In general, the "Apple key" replaces the "Control" key for most commands.
nu item is underlined. If you press and hold the "alt" button and then press one of these underlined letters,
r the item you want to access.
e to click on Data -> Filter -> AutoFilter in the Toolbar
the alt button, then the "D", then "F", then "F")
derlined letters,
Keyboard Shortcuts (part 2 of 2)
What: A list of useful keyboard shortcuts that are not menu-based
Why: Learning the Keyboard shortcuts will drastically improve your efficiency when working in excel
Note: Not all keyboard shortcuts will work in precisely the same way on Excel for Mac. In general, the "Apple
General
New file Ctrl + N
Open file Ctrl + O
Save file Ctrl + S
Move between open workbooks Ctrl + F6
Close file Ctrl + F4
Save as F12
Display the print menu Ctrl + P
Select whole spreadsheet Ctrl + A
Select column Ctrl + Space
Select row Shift + Space
Undo last action Ctrl + Z
Redo last action Ctrl + Y
Exit Excel Alt + F4
Spell Check F7
Cut Ctrl + X
Copy Ctrl + C
Paste Ctrl + V
Find text Ctrl + F
Recalculate F9
Navigating
Move to next cell in row Tab
Move to previous cell in row Shift + Tab
Up one screen Page Up
Down one screen Page Down
Move to next worksheet Ctrl + Page Down
Move to previous worksheet Ctrl + Page Up
Go to first cell in data region Ctrl + Home
Go to last cell in data region Ctrl + End
Data Region Left Ctrl + Left Arrow
Data Region Right Ctrl + Right Arrow
Data Region Down Ctrl + Down Arrow
Data Region Up Ctrl + Up Arrow
Select Whole Data Region Ctrl + Shift + 8
Move to Next Sheet Ctrl + Page Down
Move to Prior Sheet Ctrl + Page Up
Access Drop down menu Alt + Down/Up
Zoom in / out Arrow
Ctrl + mouse scroll
ve your efficiency when working in excel
way on Excel for Mac. In general, the "Apple key" replaces the "Control" key for most commands.
Formatting Text In Worksheet

Bold toggle for selection Ctrl + B
Italic toggle for selection Ctrl + I
Underline toggle for selection Ctrl + U
Strikethrough for selection Ctrl + 5
Change the font Ctrl + Shift + F
Change the font size Ctrl + Shift + P
Apply outline borders Ctrl + Shift + 7
Remove all borders Ctrl + Shift + Underline
Wrap text in same cell Alt + Enter
Formatting Cells
Format cells Ctrl + 1
Select font Ctrl + Shift + F
Select font size Ctrl + Shift + P
Format as number Ctrl + Shift + 1
Format as date Ctrl + Shift + 3
Format as currency Ctrl + Shift + 4
Format as percentage Ctrl + Shift + 5
Editing/Deleting Text
Delete one character to right Delete
Delete one character to left Backspace
Edit active cell F2
Cancel cell entry Escape Key
Highlighting Cells
Select entire worksheet Ctrl + A
Select entire row Shift + Spacebar
Select entire column Ctrl + Spacebar
Manual select Shift + Arrow Key
Inserting Text Automatically

Autosum a range of cells Alt + Equals Sign
Insert the date Ctrl + ; (semi-colon)
Insert the time Ctrl + Shift + ; (semi-colon)
Insert columns/rows Ctrl + Shift + + (plus sign)
Insert a new worksheet Shift + F11
Keyboard Shortcuts, Excel for Mac
For a more complete resource (PDF): http://www.isd196.k12.mn.us/rhs/techhelp/rhs/excel2001keybdshortcu
Command Applies to
Display the Find command (Edit menu) Excel
Display the Replace command (Edit menu) Excel
Create a new workbook Excel
Open an existing file Excel
Quit Excel
Save Workbook
Decrease font size Workbook
Paste the selection Excel
Repeat the last action Workbook
Undo the last selection Excel
Insert cells Worksheet
Define name Worksheet
Undo Last action(s)
Moving / Selecting
Select the entire worksheet Worksheet
Move to the edge of the current data region Cell, Range
Move to the beginning of the worksheet Worksheet
Delete selected cells Cell, Range
Move to the next sheet in the workbook Workbook
Move to the previous sheet in the workbook Workbook
Select the current region around the active cell (the Cell
current region is an area enclosed by blank rows and
blank columns)
Extend the selection to the last nonblank cell in the same Cell
column or row as the active cell
Extend the selection to the last cell used on the Cell
worksheet (lower-right corner)
Extend the selection to the beginning of the worksheet Cell
Extend the selection to the last nonblank cell in the same Cell, Range
Extend the selection to the last nonblank cell in the same Cell, Range
Select the entire column Cell
Edit the active cell Cell
Move to the last cell to the right that is not blank in the Cell, Range
current row
Extend the selection to the last cell used on the Cell, Range
worksheet (lower-right corner)
Extend the selection to the last cell in the current row Cell, Range
Select the entire row Cell, Cells
Formatting
Display the Cells command (Format menu) Workbook
Apply or remove bold formatting Cells, Text Boxes, Chart
objects, Chart items
Apply or remove italic formatting Cells, Text Boxes, Chart
objects, Chart items
Display the Formula Palette after you type a valid function Worksheet
name in a formula
Insert the argument names and parentheses for a Formula
function, after you type a valid function name in a formula
Formulas
Insert the argument names and parentheses for a Formula
function, after you type a valid function name in a formula
Select only cells that are directly referred to by formulas Cell

in the selection
Start a new line in the same cell Cell
Select all cells that are directly or indirectly referred to by Cell
formulas in the selection
p/rhs/excel2001keybdshortcuts.pdf
US
COMMAND+F
COMMAND+H
COMMAND+N
COMMAND+O
COMMAND+Q
COMMAND+S
COMMAND+SHIFT+,
COMMAND+V
COMMAND+Y
COMMAND+Z
CONTROL+I
CONTROL+L
F1
COMMAND+A
CONTROL+ARROW KEY
CONTROL+HOME
CONTROL+K
CONTROL+PAGE DOWN
CONTROL+PAGE UP
CONTROL+SHIFT+* (Asterisk)
CONTROL+SHIFT+ARROW KEY
CONTROL+SHIFT+DOWN ARROW
CONTROL+SHIFT+END
CONTROL+SHIFT+HOME
CONTROL+SHIFT+LEFT ARROW
CONTROL+SHIFT+RIGHT ARROW
CONTROL+SHIFT+UP ARROW
CONTROL+SPACEBAR
CONTROL+U
END, RETURN
END, SHIFT+ARROW KEY
END, SHIFT+HOME
END, SHIFT+RETURN
SHIFT+SPACEBAR
COMMAND+1
COMMAND+B
COMMAND+I
CONTROL+A
CONTROL+SHIFT+A
CONTROL+SHIFT+A
CONTROL+[
CONTROL+OPTION+RETURN
CONTROL+SHIFT+{
Keyboarding examples
What: Hold down the "ctrl" key and press the arrow keys to move to the end of a continuous block of data
jump jump jump jump jump jump jump

jump jump jump jump
jump jump jump jump
jump jump jump jump
jump jump jump jump
jump jump jump jump
jump jump jump jump
jump jump jump jump
d of a continuous block of data
What: Hold down the "ctrl" key and press the arrow keys to move to the end of a continuous block of d
exercise 1
start finish
# #
# #
# #
# #
# #
# #
# #
# #
# 4 # # # # # 3
# #
# #
# #
# #
# #
# #
1 # # # # # # # # 2
4
#
#
#
#
#
#
#
#
3
of a continuous block of data
exercise 2
star# # # # # # # 1 12 # # # # # # # # # #
# #
# #
# #
# #
# #
# 11 # # 10
# #
# #
# #
# 8 # # 9
# #
# #
# #
# 7 # # 6
# #
# #
# #
# # # # # # # # # # # # # # 5
#
#
#
#
#
#
#
#
# # # # # # # # 2
# finish
What: Hold down the shift + "ctrl" and press the arrow keys to select the cells while moving to the end
Why: Eliminate the need to scroll down a large data set and select a large block of data almost instant
Select the blue shaded cells only
exercise 1 exercise 2
select me! select me! select me!

select me! select me! select me! select me! select me! select me!
cells while moving to the end of a continuous block of data
ge block of data almost instantly
exercise 3
select me!
select me!
select me! select me! select me! select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
select me!
What: Deleteing rows and columns
Excel for Windows users Excel for Mac users

Deleting rows: Alt-E-D-R Deleting rows: SHIFT+Spacebar, th
Edit, Delete, Row Select full row, then delete selection
Deleting columns: Alt-E-D-C Deleting columns: CTRL+Spacebar,

Edit, Delete, Column Select full row, then delete selection
exercise 1: delete the red columns and rows
a b c d e f g
0.69 0.94 0.39 0.93 0.25 0.19 0.65
0.59 0.52 0.81 0.57 0.84 0.51 0.46
0.70 0.37 0.17 0.20 0.21 0.09 0.57
0.22 0.71 0.06 0.62 0.84 0.29 0.44
0.90 0.70 0.81 0.71 0.40 0.86 0.25
0.80 0.14 0.03 0.81 0.69 0.82 0.20
0.13 0.23 0.66 0.45 0.32 0.14 0.23
0.51 0.43 0.67 0.14 0.47 0.10 0.51
0.68 0.97 0.48 0.84 0.48 0.76 0.44
0.11 0.34 0.35 0.19 0.33 0.26 0.25
0.72 0.64 0.47 0.20 0.18 0.54 0.37
0.86 0.96 0.38 0.74 0.70 0.72 0.79
0.32 0.27 0.66 0.13 0.40 0.73 0.25
0.11 0.86 0.64 0.54 0.81 0.89 0.48
0.29 0.96 0.86 0.05 0.72 0.26 0.47
0.22 0.12 0.78 0.80 0.15 0.66 0.71
0.54 0.89 0.40 0.64 0.24 1.00 0.55
0.95 0.67 0.02 0.77 0.38 0.55 0.59
0.64 0.44 0.82 0.64 0.82 0.49 0.08
0.39 0.29 0.42 0.54 0.02 0.15 0.85
0.36 0.25 0.03 0.87 0.75 0.89 0.62
0.26 0.93 0.53 0.69 0.55 0.47 0.29
0.44 0.58 0.26 0.78 0.16 0.50 0.52
0.31 0.87 0.60 0.75 0.71 0.37 0.72
0.45 0.93 0.65 0.17 0.66 0.86 0.45
0.81 0.10 0.68 0.03 0.00 0.78 0.31
0.66 0.38 0.95 1.00 0.98 0.59 0.48
0.82 0.93 0.04 0.70 0.41 0.34 0.45
0.81 0.06 0.89 0.87 0.93 0.03 0.97
0.46 0.61 0.40 0.69 0.49 0.92 0.43
0.43 0.11 0.08 0.38 0.96 0.62 0.94
0.90 0.80 0.98 0.57 0.40 0.07 0.19
0.10 0.16 0.74 0.70 0.78 0.21 0.51
0.92 0.90 0.80 0.12 0.05 0.41 0.18
0.64 0.95 0.02 0.41 0.30 0.74 0.15
0.83 0.88 0.10 0.34 0.29 0.14 0.22
0.31 0.89 0.19 0.08 0.50 0.70 0.33
0.57 0.44 0.43 0.94 0.36 0.83 0.74
0.79 0.40 0.23 0.76 0.47 0.79 0.87
0.91 0.09 0.15 0.63 0.93 0.41 1.00
0.60 0.83 0.26 0.49 0.92 0.41 0.84
0.71 0.67 0.59 0.37 0.29 0.67 0.92
0.92 0.65 0.55 0.34 0.02 0.95 0.54
0.80 0.52 0.83 0.72 0.77 0.46 0.30
0.54 0.37 0.45 0.38 0.43 0.99 0.18
0.18 0.74 0.88 0.27 0.58 0.23 0.91
0.48 0.06 0.89 0.23 0.69 0.57 0.58
0.91 0.89 0.19 0.62 0.67 0.15 0.98
0.78 0.31 0.57 0.79 0.62 0.21 0.59
0.56 0.96 0.23 0.40 0.30 0.61 0.95
0.61 0.05 0.77 0.47 0.73 0.95 0.94
0.27 0.23 0.47 0.93 0.22 0.35 0.88
0.69 0.17 0.11 0.55 0.54 0.69 0.96
0.39 0.36 0.88 0.54 0.22 0.54 0.72
0.78 0.92 0.65 0.96 0.38 0.87 0.01
0.25 0.26 0.15 0.97 0.31 0.33 0.33
0.35 0.50 0.45 0.93 0.45 0.70 0.91
0.76 0.76 0.75 0.95 0.43 0.68 0.96
0.01 0.02 0.35 0.24 0.70 0.77 0.58
0.58 0.80 1.00 0.66 0.86 0.35 0.69
0.16 0.55 0.80 0.82 0.61 0.86 0.33
0.97 0.75 0.02 0.33 0.39 0.41 0.91
0.96 0.78 0.36 0.53 0.84 0.94 0.67
0.76 0.69 0.72 0.27 0.36 0.10 0.64
0.73 0.44 0.62 0.87 0.95 0.49 0.45
0.35 0.41 0.35 0.14 0.54 0.76 0.16
0.23 0.59 0.48 0.36 0.87 0.20 0.29
0.92 0.94 0.34 0.04 0.57 0.63 0.33
0.94 0.48 0.25 0.37 0.01 0.98 0.63
0.40 0.95 0.00 0.12 0.41 0.03 0.23
0.33 0.83 0.76 0.06 0.57 0.70 0.86
0.94 0.08 0.21 0.17 0.69 0.26 0.00
0.09 0.15 0.91 0.95 0.02 0.71 0.18
0.49 0.28 0.01 0.35 0.73 0.07 0.43
0.55 0.06 0.29 0.50 0.42 0.53 0.86
0.38 0.01 0.98 0.74 0.54 0.52 0.00
0.72 0.77 0.98 0.01 0.67 0.42 0.44
0.52 0.56 0.55 0.56 0.39 0.93 0.95
0.69 0.72 0.28 0.32 0.55 0.85 0.05
0.78 0.08 0.42 0.28 0.09 0.85 0.26
0.96 0.75 0.89 0.40 0.73 0.38 0.18
0.13 0.32 0.91 0.49 0.45 0.22 0.63
0.85 0.15 0.27 0.15 0.42 0.58 0.40
0.53 0.90 0.87 0.77 0.43 0.64 0.37
0.68 0.10 0.13 0.44 0.24 0.33 0.89
0.86 0.18 0.76 0.91 0.99 0.57 0.56
0.92 0.12 0.66 0.70 0.25 0.29 0.56
0.12 0.12 0.79 0.77 0.91 0.24 0.65
0.40 0.96 0.37 0.17 0.99 0.53 0.77
0.39 0.39 0.75 0.59 0.21 1.00 0.36
0.99 0.02 0.53 0.42 0.60 0.19 0.20
0.64 0.40 0.39 0.23 0.35 0.95 0.41
0.53 0.02 0.04 0.47 0.06 0.43 0.28
0.35 0.05 0.71 0.11 0.03 0.82 0.05
0.72 0.36 0.34 0.11 0.12 0.03 0.19
0.26 0.72 0.70 0.03 0.85 0.65 0.98
0.00 0.30 0.15 0.12 0.88 0.54 0.89
0.09 0.92 0.26 0.86 0.12 0.77 0.50
0.97 0.07 0.51 0.64 0.25 0.51 0.92
0.78 0.37 0.61 0.91 0.54 0.44 0.38
0.16 0.61 0.60 0.18 0.27 0.50 0.65
0.74 0.55 0.82 0.33 0.53 0.56 0.71
0.28 0.20 0.95 0.05 0.48 0.12 0.01
0.40 0.28 0.89 0.00 0.07 0.05 0.51
0.30 0.08 0.48 0.96 0.42 0.34 0.68
0.43 0.50 0.94 0.19 0.96 0.32 0.22
0.41 0.22 0.81 0.55 0.18 0.68 0.81
0.80 0.37 0.18 0.79 0.37 0.59 0.80
0.91 0.24 0.31 0.31 0.47 0.02 0.05
0.87 0.61 0.95 0.39 0.33 0.05 0.28
0.32 0.39 0.19 0.31 0.82 0.72 0.78
0.89 0.13 0.25 0.88 0.34 0.51 0.71
0.04 0.50 0.74 0.34 0.22 0.76 0.57
0.76 0.86 0.83 0.08 0.99 0.58 0.55
0.97 0.54 0.53 0.37 0.98 0.85 0.81
0.08 0.11 0.81 0.91 0.43 0.91 0.42
0.35 0.71 0.66 0.71 0.04 0.52 0.82
0.21 0.26 0.25 0.96 0.66 0.79 0.69
0.41 0.60 0.24 0.35 0.96 0.75 0.74
0.92 0.14 0.59 0.28 0.04 0.23 0.23
0.44 0.24 0.44 0.24 0.25 0.03 0.99
0.62 0.75 0.01 0.78 0.03 0.51 0.37
0.16 0.49 0.37 0.24 0.07 0.54 0.94
0.11 0.68 0.29 0.72 0.86 0.90 0.16
HIFT+Spacebar, then CTRL-K
elete selection
s: CTRL+Spacebar, then CTRL-K

elete selection
What: Inserting rows and columns
Excel for Windows users Excel for Mac users

Inserting rows: Alt-I-R Inserting rows: SHIFT+Spaceb
Insert, Row Select full row, then delete selection
Inserting columns: Alt-I-C Inserting columns: CTRL+Spa

Insert, Column Select full row, then delete selection
exercise 1: Insert a column after every blue column and a row after eve
a b c d e f g h i
0.69 0.94 0.39 0.93 0.25 0.19 0.65 0.77 0.20
0.59 0.52 0.81 0.57 0.84 0.51 0.46 0.94 0.06
0.70 0.37 0.17 0.20 0.21 0.09 0.57 0.23 0.02
0.22 0.71 0.06 0.62 0.84 0.29 0.44 0.90 0.72
0.90 0.70 0.81 0.71 0.40 0.86 0.25 0.27 0.17
0.80 0.14 0.03 0.81 0.69 0.82 0.20 0.11 0.43
0.13 0.23 0.66 0.45 0.32 0.14 0.23 0.04 0.56
0.51 0.43 0.67 0.14 0.47 0.10 0.51 0.77 0.83
0.68 0.97 0.48 0.84 0.48 0.76 0.44 0.20 0.12
0.11 0.34 0.35 0.19 0.33 0.26 0.25 0.05 0.68
0.72 0.64 0.47 0.20 0.18 0.54 0.37 0.17 0.99
0.86 0.96 0.38 0.74 0.70 0.72 0.79 0.61 0.18
0.32 0.27 0.66 0.13 0.40 0.73 0.25 0.79 0.65
0.11 0.86 0.64 0.54 0.81 0.89 0.48 0.34 0.01
0.29 0.96 0.86 0.05 0.72 0.26 0.47 0.46 0.36
0.22 0.12 0.78 0.80 0.15 0.66 0.71 0.78 0.94
0.54 0.89 0.40 0.64 0.24 1.00 0.55 0.23 0.94
0.64 0.44 0.82 0.64 0.82 0.49 0.08 0.53 0.33
0.39 0.29 0.42 0.54 0.02 0.15 0.85 0.72 0.95
0.36 0.25 0.03 0.87 0.75 0.89 0.62 0.97 0.84
0.26 0.93 0.53 0.69 0.55 0.47 0.29 0.67 0.26
0.44 0.58 0.26 0.78 0.16 0.50 0.52 0.21 0.53
0.31 0.87 0.60 0.75 0.71 0.37 0.72 0.92 0.71
0.45 0.93 0.65 0.17 0.66 0.86 0.45 0.11 0.29
0.81 0.10 0.68 0.03 0.00 0.78 0.31 0.28 0.65
0.66 0.38 0.95 1.00 0.98 0.59 0.48 0.41 0.62
0.82 0.93 0.04 0.70 0.41 0.34 0.45 0.25 0.08
0.81 0.06 0.89 0.87 0.93 0.03 0.97 0.80 0.66
0.46 0.61 0.40 0.69 0.49 0.92 0.43 0.85 0.51
0.43 0.11 0.08 0.38 0.96 0.62 0.94 0.16 0.72
0.90 0.80 0.98 0.57 0.40 0.07 0.19 0.83 0.57
0.10 0.16 0.74 0.70 0.78 0.21 0.51 0.04 0.54
0.92 0.90 0.80 0.12 0.05 0.41 0.18 0.51 0.21
0.64 0.95 0.02 0.41 0.30 0.74 0.15 0.30 0.26
0.83 0.88 0.10 0.34 0.29 0.14 0.22 0.79 0.34
0.31 0.89 0.19 0.08 0.50 0.70 0.33 0.07 0.03
0.57 0.44 0.43 0.94 0.36 0.83 0.74 0.39 0.93
0.79 0.40 0.23 0.76 0.47 0.79 0.87 0.87 0.02
0.91 0.09 0.15 0.63 0.93 0.41 1.00 0.32 0.10
0.60 0.83 0.26 0.49 0.92 0.41 0.84 0.78 0.90
0.71 0.67 0.59 0.37 0.29 0.67 0.92 0.59 0.80
0.92 0.65 0.55 0.34 0.02 0.95 0.54 0.44 0.96
0.80 0.52 0.83 0.72 0.77 0.46 0.30 0.00 0.09
0.54 0.37 0.45 0.38 0.43 0.99 0.18 0.30 0.15
0.18 0.74 0.88 0.27 0.58 0.23 0.91 0.89 0.04
0.48 0.06 0.89 0.23 0.69 0.57 0.58 0.40 0.24
0.91 0.89 0.19 0.62 0.67 0.15 0.98 0.22 0.90
0.78 0.31 0.57 0.79 0.62 0.21 0.59 0.69 0.16
0.56 0.96 0.23 0.40 0.30 0.61 0.95 0.72 0.68
0.61 0.05 0.77 0.47 0.73 0.95 0.94 0.52 0.10
0.27 0.23 0.47 0.93 0.22 0.35 0.88 0.35 0.25
0.69 0.17 0.11 0.55 0.54 0.69 0.96 0.41 0.81
0.39 0.36 0.88 0.54 0.22 0.54 0.72 0.34 0.68
0.78 0.92 0.65 0.96 0.38 0.87 0.01 0.74 0.36
0.25 0.26 0.15 0.97 0.31 0.33 0.33 0.84 0.26
0.35 0.50 0.45 0.93 0.45 0.70 0.91 0.49 0.70
0.76 0.76 0.75 0.95 0.43 0.68 0.96 0.57 0.71
0.01 0.02 0.35 0.24 0.70 0.77 0.58 0.60 0.15
0.58 0.80 1.00 0.66 0.86 0.35 0.69 0.44 0.86
0.16 0.55 0.80 0.82 0.61 0.86 0.33 0.63 0.14
0.97 0.75 0.02 0.33 0.39 0.41 0.91 0.27 0.65
0.96 0.78 0.36 0.53 0.84 0.94 0.67 0.07 0.14
0.76 0.69 0.72 0.27 0.36 0.10 0.64 0.77 0.20
0.73 0.44 0.62 0.87 0.95 0.49 0.45 0.62 0.61
0.35 0.41 0.35 0.14 0.54 0.76 0.16 0.08 0.52
0.23 0.59 0.48 0.36 0.87 0.20 0.29 0.43 0.75
0.92 0.94 0.34 0.04 0.57 0.63 0.33 0.58 0.79
0.94 0.48 0.25 0.37 0.01 0.98 0.63 0.59 0.36
0.40 0.95 0.00 0.12 0.41 0.03 0.23 0.39 0.16
0.33 0.83 0.76 0.06 0.57 0.70 0.86 0.25 0.33
0.94 0.08 0.21 0.17 0.69 0.26 0.00 0.70 0.99
0.09 0.15 0.91 0.95 0.02 0.71 0.18 0.36 0.75
0.49 0.28 0.01 0.35 0.73 0.07 0.43 0.53 0.62
0.55 0.06 0.29 0.50 0.42 0.53 0.86 0.60 0.92
0.38 0.01 0.98 0.74 0.54 0.52 0.00 0.52 0.23
0.72 0.77 0.98 0.01 0.67 0.42 0.44 0.33 0.87
0.52 0.56 0.55 0.56 0.39 0.93 0.95 0.64 0.39
0.69 0.72 0.28 0.32 0.55 0.85 0.05 0.24 0.26
0.78 0.08 0.42 0.28 0.09 0.85 0.26 0.01 0.61
0.96 0.75 0.89 0.40 0.73 0.38 0.18 0.64 0.69
0.13 0.32 0.91 0.49 0.45 0.22 0.63 0.55 0.05
0.85 0.15 0.27 0.15 0.42 0.58 0.40 0.15 0.40
0.53 0.90 0.87 0.77 0.43 0.64 0.37 0.80 0.41
0.68 0.10 0.13 0.44 0.24 0.33 0.89 0.06 0.12
0.86 0.18 0.76 0.91 0.99 0.57 0.56 0.75 0.75
0.92 0.12 0.66 0.70 0.25 0.29 0.56 0.15 0.74
0.12 0.12 0.79 0.77 0.91 0.24 0.65 0.87 0.35
0.40 0.96 0.37 0.17 0.99 0.53 0.77 0.34 0.43
0.39 0.39 0.75 0.59 0.21 1.00 0.36 0.26 0.21
0.99 0.02 0.53 0.42 0.60 0.19 0.20 0.26 0.78
0.64 0.40 0.39 0.23 0.35 0.95 0.41 0.66 0.84
0.53 0.02 0.04 0.47 0.06 0.43 0.28 0.95 0.40
0.35 0.05 0.71 0.11 0.03 0.82 0.05 0.25 0.53
0.72 0.36 0.34 0.11 0.12 0.03 0.19 0.11 0.85
0.26 0.72 0.70 0.03 0.85 0.65 0.98 0.94 0.68
0.00 0.30 0.15 0.12 0.88 0.54 0.89 0.89 0.65
0.09 0.92 0.26 0.86 0.12 0.77 0.50 0.20 0.25
0.97 0.07 0.51 0.64 0.25 0.51 0.92 0.43 0.63
0.78 0.37 0.61 0.91 0.54 0.44 0.38 0.61 0.88
0.16 0.61 0.60 0.18 0.27 0.50 0.65 0.12 0.96
0.74 0.55 0.82 0.33 0.53 0.56 0.71 0.83 0.51
0.28 0.20 0.95 0.05 0.48 0.12 0.01 0.88 0.10
0.40 0.28 0.89 0.00 0.07 0.05 0.51 0.16 0.60
0.30 0.08 0.48 0.96 0.42 0.34 0.68 0.74 0.28
0.43 0.50 0.94 0.19 0.96 0.32 0.22 0.85 0.58
0.41 0.22 0.81 0.55 0.18 0.68 0.81 0.38 0.29
0.80 0.37 0.18 0.79 0.37 0.59 0.80 0.83 0.17
0.91 0.24 0.31 0.31 0.47 0.02 0.05 0.94 0.27
0.87 0.61 0.95 0.39 0.33 0.05 0.28 0.43 0.32
0.32 0.39 0.19 0.31 0.82 0.72 0.78 0.30 0.50
0.89 0.13 0.25 0.88 0.34 0.51 0.71 0.89 0.52
0.04 0.50 0.74 0.34 0.22 0.76 0.57 0.59 0.46
0.76 0.86 0.83 0.08 0.99 0.58 0.55 0.60 0.10
0.97 0.54 0.53 0.37 0.98 0.85 0.81 0.53 0.99
0.08 0.11 0.81 0.91 0.43 0.91 0.42 0.28 0.00
0.35 0.71 0.66 0.71 0.04 0.52 0.82 0.11 0.92
ws: SHIFT+Spacebar, then CTRL-I
hen delete selection
umns: CTRL+Spacebar, then CTRL-I

hen delete selection
nd a row after every blue row
j k l m n o p
0.84 0.23 0.23 0.56 0.18 0.32 0.62
0.22 0.75 0.21 0.25 0.39 0.18 0.16
0.59 0.71 0.94 0.79 0.61 0.54 0.72
0.26 0.01 0.83 0.91 0.01 0.17 0.50
0.96 0.16 0.14 0.83 0.30 0.33 0.24
0.49 0.30 0.49 0.62 0.56 0.79 0.01
0.46 0.43 0.81 0.47 0.23 0.09 0.78
0.10 0.37 0.45 0.27 0.10 0.06 0.25
0.71 0.55 0.69 0.41 0.18 0.93 0.86
0.04 0.36 0.42 0.19 0.04 0.73 0.22
0.58 0.10 0.92 0.29 0.22 0.33 0.25
0.75 0.44 0.02 0.12 0.29 0.29 0.88
0.38 0.52 0.71 0.00 0.14 0.98 0.37
0.70 0.89 0.47 0.08 0.43 0.40 0.26
0.63 0.24 0.35 0.51 0.34 0.22 0.40
0.25 0.07 0.58 0.80 0.49 0.69 0.06
0.84 0.65 0.22 0.81 0.45 0.64 0.07
0.36 0.45 0.65 0.50 0.97 0.75 0.47
0.66 0.74 0.35 0.47 0.11 0.80 0.18
0.04 0.41 0.92 0.98 0.26 0.44 0.80
0.98 0.29 0.07 0.77 0.89 0.36 0.38
0.91 0.03 0.04 0.14 0.96 0.00 0.23
0.55 0.98 0.81 0.66 0.93 0.38 0.57
0.26 0.45 0.32 0.31 0.16 0.10 0.73
0.48 0.67 0.81 0.38 0.46 0.45 0.64
0.78 0.10 0.36 0.97 0.15 0.93 0.13
0.15 0.44 0.09 0.27 0.86 0.06 0.73
0.50 0.61 0.20 0.54 0.96 0.38 0.60
0.42 0.51 0.64 0.52 0.60 0.88 0.80
0.01 0.27 0.64 0.14 0.14 1.00 0.35
0.54 0.02 0.10 0.01 0.12 0.35 0.75
0.62 0.90 0.70 0.60 0.40 0.03 0.97
0.69 0.96 0.60 0.01 0.81 0.20 0.70
0.53 0.80 0.08 0.72 0.68 0.60 0.59
0.75 0.89 0.68 0.68 0.76 0.17 0.27
0.44 0.82 0.69 0.51 0.83 0.87 0.08
0.60 0.57 0.46 0.48 0.96 0.82 0.46
0.84 0.73 0.54 0.12 0.13 0.92 0.02
0.97 0.63 0.34 0.48 0.26 0.33 0.10
0.25 0.17 0.97 0.16 0.44 0.25 0.67
0.50 0.42 0.81 0.18 0.56 0.65 0.39
0.35 0.48 0.62 0.81 0.31 0.10 0.87
0.38 0.90 0.50 0.44 0.40 0.86 0.41
0.36 0.19 0.44 0.58 0.82 0.01 0.17
0.99 0.09 0.81 0.57 0.10 0.47 0.39
0.16 0.55 0.04 0.13 0.40 0.96 0.11
0.38 0.44 0.87 0.12 0.79 0.65 0.11
0.63 0.19 0.18 0.15 0.77 0.47 0.41
0.48 0.63 0.52 0.81 0.91 0.43 0.52
0.07 0.77 0.94 0.97 0.40 0.64 0.82
0.58 0.56 0.21 0.09 0.86 0.55 0.67
0.48 0.43 0.41 0.99 0.46 0.77 0.79
0.32 0.48 0.05 0.93 0.09 0.31 0.69
0.90 0.03 0.57 0.56 0.12 0.69 0.14
0.61 0.53 0.94 0.16 0.78 0.96 0.41
0.01 0.80 0.65 0.75 0.21 0.25 0.79
0.53 0.36 0.45 0.12 0.28 0.89 0.37
0.94 0.35 0.23 0.51 0.17 0.55 0.25
0.35 0.90 0.83 0.40 0.82 0.72 0.25
0.17 0.97 0.09 0.76 0.20 0.37 0.35
0.18 0.77 0.96 0.62 0.52 0.49 0.71
0.26 0.86 0.19 0.45 0.16 0.21 0.76
0.84 0.62 0.22 0.81 0.39 0.38 0.70
0.40 0.54 0.03 0.87 0.83 0.99 0.37
0.55 0.45 0.57 0.83 0.04 0.61 0.74
0.65 0.54 0.78 0.99 0.31 0.36 0.41
0.08 0.97 0.44 0.56 0.96 0.39 0.38
0.28 0.29 0.93 0.93 0.37 0.94 0.32
0.38 0.44 0.45 0.91 0.63 0.56 0.29
0.24 0.41 0.91 0.78 0.07 0.44 0.99
0.36 0.88 0.52 0.42 0.01 0.83 0.70
0.77 0.08 0.62 0.85 0.99 0.03 0.89
0.60 0.95 0.92 0.58 0.41 0.44 0.49
0.32 0.51 0.73 0.43 0.18 0.28 0.97
0.56 0.02 0.20 0.86 1.00 0.02 0.05
0.59 0.47 0.35 0.79 1.00 0.94 0.42
0.11 0.49 0.17 0.68 0.82 0.51 0.86
0.02 0.03 0.85 0.90 0.10 0.56 0.75
0.88 0.69 0.89 0.05 0.82 0.40 0.22
0.78 0.60 0.17 0.78 0.76 0.22 0.95
0.01 0.76 0.45 0.14 0.73 0.23 0.62
0.46 0.83 0.08 0.02 0.97 0.58 0.98
0.80 0.43 0.03 0.66 0.75 0.52 0.36
0.21 0.88 0.30 0.06 0.17 0.98 0.25
0.81 0.12 0.52 0.98 0.01 0.38 0.36
0.63 0.35 0.96 0.88 0.44 0.38 0.97
0.56 0.74 0.05 1.00 0.70 0.75 0.42
0.62 0.02 0.14 0.84 0.04 0.61 0.39
0.73 0.05 0.82 0.68 0.83 0.55 0.02
0.13 0.95 0.70 0.13 0.49 0.79 0.82
0.89 0.51 0.31 0.42 0.79 0.53 0.41
0.70 0.08 0.29 0.43 0.31 0.39 0.65
0.17 0.48 0.51 0.06 0.75 0.08 0.05
0.99 0.24 0.34 0.51 0.86 0.38 0.67
0.19 0.15 0.51 0.25 0.62 0.92 0.42
0.80 0.85 0.08 0.92 0.82 0.48 0.35
0.21 0.67 0.67 0.36 0.58 0.83 0.27
0.42 0.50 0.42 0.07 0.18 0.31 0.18
0.86 0.38 0.05 0.18 0.29 0.79 0.54
0.50 0.49 0.86 0.86 0.10 0.60 0.73
0.28 0.75 0.79 0.37 0.01 0.91 0.31
0.39 0.59 0.75 0.67 0.58 0.29 0.52
0.75 0.58 0.25 0.80 0.30 0.05 0.70
0.27 0.86 0.93 0.08 0.42 0.52 0.57
0.40 0.55 0.95 0.69 0.10 0.88 0.43
0.14 0.75 0.05 0.90 0.91 0.76 0.20
0.64 0.59 0.11 0.32 0.27 0.70 0.07
0.56 0.07 0.91 0.65 0.77 0.32 0.88
0.93 0.22 0.77 0.51 0.85 0.40 0.31
0.48 0.47 0.63 0.01 0.68 0.01 0.66
0.68 0.41 0.81 0.99 0.67 0.22 0.80
0.42 0.08 0.83 0.09 0.60 0.17 0.69
0.22 0.86 0.90 0.37 0.19 0.22 0.78
0.76 0.91 0.13 0.97 0.04 0.31 0.03
0.10 0.70 0.86 0.47 0.04 0.38 0.54
0.90 0.82 0.72 0.93 0.63 0.90 0.94
Absolute vs. Relative references
To create an absolute reference, you insert a dollar sign ($) before the column and row of the cel
For example, the cell reference $B$8 is an absolute reference, whereas the cell reference B8 is a
If you copy a formula that contains the absolute reference $B$8 to another cell, the cell referenc
On the other hand, if you copy a formula containing the relative reference B8 to another cell, the
Relative formula result

1 =C10 1 Both the row and colums ch
2 2 In this case, as the row chan
3 3
4 4
5 5
Absolute formula result

1 =$C$10 0
2 0 When copyed down, the ref
3 0
4 0
5 0
To create a reference to another sheet, include the sheet name, followed by an excalimation mar
e column and row of the cell reference.
as the cell reference B8 is a relative reference.
nother cell, the cell reference $B$8 does not change.
rence B8 to another cell, the reference B8 changes.
Both the row and colums change based on their relative postions
In this case, as the row changes, so does the row's relative reference
When copyed down, the reference stays the same
wed by an excalimation mark (!), then the cell range

Find and Replace
What: How to use the Find and Replace functionality
Why: Can use this to quickly manipulate data
start by going to either:

Edit -> Find
Edit -> Replace
Tips:
If a highlighted region is selected, Excel will only search that region, otherwise, Excel will search
Wildcard characters
Use
? (question mark)
* (asterisk)
~ (tilde) followed by ?, *, or ~
important:
Wildcards work the same way for data filters
eplace functionality
nipulate data
elected, Excel will only search that region, otherwise, Excel will search the entire sheet by default
racters
To find
Any single character
For example, sm?th finds "smith" and "smyth"
Any number of characters
For example, *east finds "Northeast" and "Southeast"
A question mark, asterisk, or tilde
For example, fy91~? finds "fy91?"
way for data filters

Dates and Time formats in Excel
What: Excel has specific methods for storing Dates and times
Why: Understanding how to manipulate date formats is a common necessity when creating reports in
Source: http://www.excelwiki.com/Excel/DateAndTimeBasics
How Excel stores dates and times.

The basic unit of time in Excel is a day.
A day equals 1, half a day is .5 and an hour time interval is therefore 1/24=0.041666667
Dates are stored as serial values with time starting on January 1, 1900.
January 1,1900 equals 1 and December 1, 2005 equals 38,687 as it 38,687 days after January 1
One hour past midnight is serial value 38687.041666667 and Noon on December 1, 2005 is 386
Storing dates as sequential numbers makes it easy to add, subtract and determine time interval
Dates entered without a specified year are assumed to be in the current year. A date entered as
Dates entered with a 2 digit year are stored as pre-2000 dates if the year is between 30 and 99
28/02/05 is in year 2005, 28/02/98 is in 1998, 28/02/29 is in 2029 and 28/02/30 is in 1930.
The 1904 Date System

Macintosh computers typically use the 1904 date system with January 2, 1904 equal to 1.
This can cause date discrepancies if files are transferred from Mac to Windows or vice versa.
Date values will change by 4 years and 1 day.
Excel users in Windows may also inadvertently be using the 1904 date system and
this will also cause dates to change if the file is sent to a user on the more common 1900 date s
Click on Tools-Options-Calculation and ensure that the 1904 date system checkbox is unchec
How Excel displays dates and times.

Dates are stored in Excel cells as serial values but can be displayed in various date formats by c
Each of the dates at the right has an underlying serial value of 38598.
Note that the time value is midnight (at the start of the day).
The examples on the right use the US date format mm/dd/yyyy
The date format can be changed to the UK date format (dd/mm/yyyy) by changing the option in
Excel will recognize an input that is in a date format.

If a user inputs Dec 1, 2005 or 10 May 2004 Excel will recognize this as a date,
store it in the cell as a serial value, and display it in a date format.
Excel sometimes makes mistakes. A user may enter 3-4, meaning a value between 3 and 4,
but Excel will interpret this as March 4th and store the input as 38415. This can be remedied my
essity when creating reports in excel
ore 1/24=0.041666667
s it 38,687 days after January 1,1900.

on on December 1, 2005 is 38687.5.
act and determine time intervals between two dates or times.
current year. A date entered as Feb-28 (in 2005) is stored as 28/2/2005 or date serial 38411.
the year is between 30 and 99 or as post-2000 dates if the year is between 0 and 29.
9 and 28/02/30 is in 1930.
nuary 2, 1904 equal to 1.

c to Windows or vice versa.
4 date system and

the more common 1900 date system.
ate system checkbox is unchecked.
ed in various date formats by clicking Format-Cells-Number-Date-Type.
9/3/2005
Saturday, September 03, 2005
9/3
9/3/05
yyy) by changing the option in the Locale box. 3-Sep
3-Sep-05
03-Sep-05
e this as a date, 9/3/05 12:00 AM
9/3/05 0:00
9/3/2005
g a value between 3 and 4,
8415. This can be remedied my placing a quote before the 3-4 input. i.e. '3-4
Function basics
What: Excel Function basics and syntax
Why: Understand what all those different brackets and punctuation means
Function Name optional argument
=sum(number1,[number2],)
Arguments
Functions always begin with a "="

Arguments separated by a comma
Optional arguments are enclosed in [] brackets
Type "=Function Name(" to get the signature
To find a list of all excel functions, go to Insert-->Function
Character Used to:

$ Turns a relative reference to an absolute reference
' If a sheet name has a space between it, Excel will automatically put a ' ' around the shee
! Placed after a string to signify a reference to a sheet with that name
{} Enclose "array" formulas. These braces are added when you hit ctrl+shift+enter; you mu
atically put a ' ' around the sheet name when it is referenced from another sheet
that name
you hit ctrl+shift+enter; you must do this every time you enter or change an array formula
Error Checking
Errors happen, but if you know why they are happening, they can help you to understand your data
There are 7 different types of errors in Excel:
#NULL! Occurs when you specify an intersection of two areas that do not intersect. The intersection op
#DIV/0! Occurs when a number is divided by zero (0).
#VALUE! Occurs when the wrong type of argument is used.
#REF! Occurs when a cell reference is not valid
#NAME? Occurs when Microsoft Excel doesn't recognize text in a formula.
#NUM! Occurs with invalid numeric values in a formula or function.
#N/A Occurs when a value is not available to a function or formula.
to find if a value in a cell is an error, use the ISERROR() function

to create a #N/A, use the NA() function
Example
Standard problem:
1 25 1 25
2 30 2 30
3 #N/A 3 #N/A
4 15 4 15
total #N/A total #N/A
The error messes up computations

nd your data
ct. The intersection operator is a space between references.
error check formula used:

25 =IF(ISERROR(G18),"",G18)
30
15
70
Can now find the total despite the bad data

=RIGHT(text, [num-characters])
Takes num-characters from the right side of text
Text Formula
Bill Clinton =RIGHT(B9,4)
Bill Clinton =RIGHT(B10,1)
=LEFT(text, [num-characters])
Takes num-characters from the left side of text
Text Formula
Bill Clinton =LEFT(B16,4)
Bill Clinton =LEFT(B17,1)
=MID(text, start-position, num-characters)

Takes num-characters from text starting at start-position
Use a large number (like 999) for num-characters if you want to get all the remaining
characters in a string
Text Formula
Bill Clinton =MID(B24,4,4)
Bill Clinton =MID(B25,FIND(" ",B25),999)
=text1&text2
Glues text1 and text2 together
Text Formula
Bill Clinton =B31&" was a US president"
=FIND(text-to-find, within-text, [start-position])

Finds text-to-find within within-text and returns the character location of the text
For basic cases, you don't need start-position
Text Formula
Bill Clinton =FIND("l",B38)
Bill Clinton =FIND("n",B39,10)
Bill Clinton =FIND("X",B40)
xt
Result
nton
n
Result
Bill
B
rt-position
ers if you want to get all the remaining
Result
l Cl
Clinton
Result
Bill Clinton was a US president
s the character location of the text
Result
3
12
#VALUE!
List of all text functions
Function
ASC
BAHTTEXT
CHAR
CLEAN
CODE
CONCATENATE
DOLLAR
EXACT
FIND
FIXED
JIS
LEFT
LEN
LOWER
MID
PHONETIC
PROPER
REPLACE
REPT
RIGHT
SEARCH
SUBSTITUTE
T
TEXT
TRIM
UPPER
VALUE
&
'
""
all text functions
Description
Changes full-width (double-byte) English letters or katakana within a character string to half-
width (single-byte)
Converts a number characters
to text, using the (baht) currency format
Returns the character specified by the code number
Removes all nonprintable characters from text
Returns a numeric code for the first character in a text string
Joins several text items into one text item
Converts a number to text, using the $ (dollar) currency format
Checks to see if two text values are identical
Finds one text value within another (case-sensitive)
Formats a number as text with a fixed number of decimals
Changes half-width (single-byte) English letters or katakana within a character string to full-
width
Returns(double-byte)
the leftmostcharacters
characters from a text value
Returns the number of characters in a text string
Converts text to lowercase
Returns a specific number of characters from a text string starting at the position you specify
Extracts the phonetic (furigana) characters from a text string
Capitalizes the first letter in each word of a text value
Replaces characters within text
Repeats text a given number of times
Returns the rightmost characters from a text value
Finds one text value within another (not case-sensitive)
Substitutes new text for old text in a text string
Converts its arguments to text
Formats a number and converts it to text
Removes spaces from text, except for single spaces between words
Converts text to uppercase
Converts a text argument to a number
Adds strings of text together

Anything following the ' sign is converted to a string. Useful if trying to write the equals sign
Use double quotes (" ") to signify a string. Example: 45 is a number, but "45" is a string of characters
ng of characters
Text Manipulation
exercise 1: Capture the year from the election column

exercise 2: Use a text formula to get rid of the * in front of the president's name
exercise 3: Create a sentence that looks like this "Bill Clinton won the 1996 election wi
(Answers below the fold)
President Vote % Election Exercise 1

* James K. Polk 49.3% 1844 election
* Zachary Taylor 47.3% 1848 election
* James Buchanan 45.3% 1856 election
* Abraham Lincoln 39.9% 1860 election
* James Garfield 48.3% 1880 election
* Grover Cleveland 48.8% 1884 election
* Grover Cleveland 46.0% 1892 election
* Woodrow Wilson 41.8% 1912 election
* Woodrow Wilson 49.3% 1916 election
* Harry S. Truman 49.7% 1948 election
* John F. Kennedy 49.7% 1960 election
* Richard Nixon 43.2% 1968 election
* Bill Clinton 42.9% 1992 election
* Bill Clinton 49.2% 1996 election
Answers
Exercise 1 Exercise 2 Exercise 3
=LEFT(D8,4) =MID(B8,3,50) =F8&" won the "&E8&" election with "&C8&" of the popul
esident's name
n the 1996 election with 49.2% of the popular vote"
Exercise 2 Exercise 3
with "&C8&" of the popular vote"
Text Manipulation
(Answers to the right)
exercise 1: create a column containing the full name:
First Name Last Name Name

Tammy Smith
Ashley Hall
Brandon Johnson
Jeremy Halverson
exercise 2: Split the name into 2 different columns:
Name First Name Last Name

Tammy Smith
Ashley Hall
Brandon Johnson
Jeremy Halverson
exercise 3: Split the name into 2 different columns:
Name First Name Last Name

Smith, Tammy
Hall, Ashley
Johnson, Brandon
Halverson, Jeremy
exercise 4: Create a column that contains the initials of the given names:
Name Initials
Tammy Smith
Ashley Hall
Brandon Johnson
Jeremy Halverson
of the given names:
Answers
=B5&" "&C5
=LEFT(B14,FIND(" ",B14)-1) =MID(B14,FIND(" ",B14)+1,20)
=MID(B23,FIND(" ",B23)+1,20) =LEFT(B23,FIND(",",B23)-1)
=LEFT(B32,1)&MID(B32,FIND(" ",B32)+1,1)
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Value you are trying to match

Table of data you are looking in to find the match
The column of the value you want to return
Should always be "FALSE"
The table_array should always be an absolute reference ($ before column and row val
The col_index_num is relative to the table_array, not the column of the Excel workshe
Team Team
BAL ARI
ATL
BAL
BUF
CAR
CHI
Example: This vlookup returns the conference Baltimore belongs to:
=VLOOKUP(D14,$F$14:$G$19,2,FALSE)
remember that these dollar signs nail down the

since this is a relative reference, it will change as you copy and paste this formula down or acros
m,range_lookup)
uld always be "FALSE"
fore column and row values)

mn of the Excel worksheet
Conference
National
National
American
American
National
National
e dollar signs nail down the row and column references

e this formula down or across a block of data
V-Lookups
exercise 1: Find the corresponding conference each player is in
Table 1:
Player Position Team Catches Yards
Santana Moss WR WAS 67 950
Drew Bennett WR TEN 72 1114
Michael Clayton WR TB 84 1253
h player is in
Table 2:
Conference? Team Conference
ARI National
ATL National
BAL American
BUF American
CAR National
CHI National
CIN American
CLE American
DAL National
DEN American
DET National
GB National
HOU American
IND American
JAC American
KC American
MIA American
MIN National
NE American
NO National
NYG National
NYJ American
OAK American
PHI National
PIT American
SD American
SEA National
SF National
STL National
TB National
TEN American
WAS National
V-Lookups
exercise 2: Find the corresponding conference each player is in for all
Table 1:
David Patten WR WAS 68 853
Darnerien McCaWR WAS 28 355
James Thrash WR WAS 21 256
Taylor Jacobs WR WAS 26 275
Kevin Dyson WR WAS 14 158
Jimmy Farris WR WAS 2 25
Chris Cooley TE WAS 45 408
Robert Royal TE WAS 8 83
Jabari Holloway TE WAS 5 52
Brian Kozlowski TE WAS 6 58
Mike Sellers TE WAS 2 29
Billy Baber TE WAS 2 21
Tyrone Calico WR TEN 55 852
Brandon Jones WR TEN 38 436
Courtney Roby WR TEN 32 393
Roydell WilliamsWR TEN 11 94
Ben Troupe TE TEN 43 411
Erron Kinney TE TEN 38 335
Bo Scaife TE TEN 9 84
Ben Hall TE TEN 0 0
Steve Cucci TE TEN 0 0
Joey Galloway WR TB 54 831
Ike Hilliard WR TB 47 501
Edell Shepherd WR TB 2 19
Anthony DiCos WR TB 0 0
Anthony Becht TE TB 28 245
Alex Smith TE TB 17 189
Will Heller TE TB 7 56
Dave Moore TE TB 4 40
Nate Lawrie TE TB 2 23
Torry Holt WR STL 96 1384
Isaac Bruce WR STL 75 1060
Kevin Curtis WR STL 60 727
Shaun McDonal WR STL 41 473
Dane Looker WR STL 17 192
Mike Furrey WR STL 10 98
Brandon ManumTE STL 18 190
Roland Williams TE STL 4 28
Dauntae Finger TE STL 0 0
Mike Brake TE STL 0 0
Erik Jensen TE STL 0 0
Brandon Lloyd WR SF 68 935
Johnnie Morton WR SF 54 703
Arnaz Battle WR SF 44 468
Rashaun Woods WR SF 31 336
Jason McAddley WR SF 6 83
P.J. Fleck WR SF 9 74
Marcus Maxwell WR SF 4 38
Javin Hunter WR SF 2 12
Eric Johnson TE SF 66 649
Steve Bush TE SF 12 74
Aaron Walker TE SF 9 111
Neil Johnson TE SF 0 0
Patrick Estes TE SF 0 0
Darrell Jackson WR SEA 83 1232
Bobby Engram WR SEA 51 645
Jerome Pathon WR SEA 32 449
Bobby Shaw WR SEA 35 439
Joe Jurevicius WR SEA 36 422
Jerheme Urban WR SEA 12 232
Alex Bannister WR SEA 4 46
Jerramy StevensTE SEA 38 397
Itula Mili TE SEA 24 268
Ryan Hannam TE SEA 8 116
Brock Edwards TE SEA 0 0
Calen Powell TE SEA 0 0
Keenan McCardeWR SD 71 898
Reche Caldwell WR SD 51 796
Eric Parker WR SD 36 541
Kassim Osgood WR SD 19 396
Vincent Jackson WR SD 11 123
Malcom Floyd WR SD 3 49
Antonio Gates TE SD 78 879
Ryan Krause TE SD 6 89
Justin Peelle TE SD 10 81
Cody McCarty TE SD 0 0
Danny Young TE SD 0 0
Hines Ward WR PIT 91 1107
Antwaan RandleWR PIT 68 897
Cedrick Wilson WR PIT 50 622
Chris Doering WR PIT 12 144
Lee Mays WR PIT 11 135
Fred Gibson WR PIT 4 34
Heath Miller TE PIT 38 373
Jerame Tuman TE PIT 9 101
Walter Rasby TE PIT 6 58
Matt Cushing TE PIT 1 9
Matt Kranchick TE PIT 0 0
Terrell Owens WR PHI 86 1261
Greg Lewis WR PHI 66 764
Reggie Brown WR PHI 37 467
Todd Pinkston WR PHI 0 0
Billy McMullen WR PHI 4 29
L.J. Smith TE PHI 42 487
Chad Lewis TE PHI 25 255
James Whalen TE PHI 17 145
Mike Bartrum TE PHI 4 32
Andy Thorn TE PHI 0 0
Randy Moss WR OAK 93 1311
Jerry Porter WR OAK 72 1023
Ronald Curry WR OAK 37 486
Doug Gabriel WR OAK 16 270
Alvis Whitted WR OAK 17 250
John Stone WR OAK 3 54
Johnnie Morant WR OAK 2 45
Randal Williams WR OAK 1 17
Teyo Johnson TE OAK 25 266
Courtney Ander TE OAK 23 315
Josh Norman TE OAK 7 91
Rickey Dudley TE OAK 9 94
John Paul FoschiTE OAK 0 0
Laveranues Col WR NYJ 86 1196
Justin McCarein WR NYJ 64 879
Wayne Chrebet WR NYJ 32 413
Jerricho CotcherWR NYJ 31 306
Jonathan Carter WR NYJ 12 209
Doug Jolley TE NYJ 32 340
Chris Baker TE NYJ 28 278
Joel Dreessen TE NYJ 0 0
Matthew Chila TE NYJ 0 0
James Dearth TE NYJ 0 0
Plaxico Burress WR NYG 74 1057
Amani Toomer WR NYG 68 973
Tim Carter WR NYG 32 440
David Tyree WR NYG 20 274
Willie Ponder WR NYG 18 197
Jamaar Taylor WR NYG 8 182
Zuriel Smith WR NYG 2 23
Jeremy Shockey TE NYG 64 697
Visanthe ShiancTE NYG 8 42
Chris Luzar TE NYG 1 12
Darius Williams TE NYG 0 0
Beau Fullerton TE NYG 0 0
Joe Horn WR NO 91 1289
Donte' StallwortWR NO 59 861
Az-Zahir Hakim WR NO 33 432
Devery HendersWR NO 34 376
Talman Gardner WR NO 18 234
Michael Lewis WR NO 11 221
Nate Poole WR NO 12 142
Boo Williams TE NO 28 298
Ernie Conwell TE NO 24 284
Shad Meier TE NO 21 167
Zach Hilton TE NO 0 0
Lamont Hall TE NO 2 4
Deion Branch WR NE 74 1009
David Givens WR NE 63 933
David Terrell WR NE 37 475
Bethel Johnson WR NE 26 384
Troy Brown WR NE 18 181
Tim Dwight WR NE 13 169
Cedric James WR NE 0 10
Ben Watson TE NE 39 357
Daniel Graham TE NE 27 293
Christian Fauria TE NE 14 146
Jed Weaver TE NE 9 111
John Lumpkin TE NE 0 0
Nate Burleson WR MIN 74 1132
Troy Williamson WR MIN 58 794
Marcus Robinso WR MIN 48 654
Travis Taylor WR MIN 56 713
Kelly Campbell WR MIN 18 336
Daryl Jones WR MIN 3 30
Avion Black WR MIN 2 17
Keenan Howry WR MIN 4 18
Jermaine WigginTE MIN 35 347
Jimmy KleinsassTE MIN 29 287
Richard Owens TE MIN 9 76
Sean Berton TE MIN 6 57
Jeff Dugan TE MIN 0 0
Chris Chambers WR MIA 74 1038
Marty Booker WR MIA 63 805
David Boston WR MIA 42 510
Derrius Thomps WR MIA 24 348
Bryan Gilmore WR MIA 12 158
Danny Farmer WR MIA 3 38
Kendall Newson WR MIA 1 28
Randy McMichaeTE MIA 65 682
Donald Lee TE MIA 12 116
Alex Holmes TE MIA 0 0
Ed Perry TE MIA 0 0
Jason Rader TE MIA 0 0
Eddie Kennison WR KC 59 996
Samie Parker WR KC 45 685
Marc Boerigter WR KC 22 434
Freddie Mitchell WR KC 26 376
Dante Hall WR KC 24 276
Craphonso Thor WR KC 19 241
Chris Horn WR KC 18 205
Tony Gonzalez TE KC 75 933
Kris Wilson TE KC 21 201
Jason Dunn TE KC 16 114
Edwin Thompso TE KC 0 0
Mike Kallfelz TE KC 0 0
Jimmy Smith WR JAC 79 1151
Reggie Williams WR JAC 58 737
Matt Jones WR JAC 33 349
Ernest Wilford WR JAC 27 379
Troy Edwards WR JAC 32 372
Cortez Hankton WR JAC 16 149
Chris Cole WR JAC 2 18
George Wrighst TE JAC 18 165
Kyle Brady TE JAC 20 197
Todd Yoder TE JAC 15 160
Brian Jones TE JAC 6 87
Cam Quayle TE JAC 0 0
Marvin Harrison WR IND 92 1194
Reggie Wayne WR IND 75 1159
Brandon Stokle WR IND 63 986
Troy Walters WR IND 11 134
Aaron MooreheaWR IND 5 62
Brad Pyatt WR IND 3 10
Dallas Clark TE IND 41 535
Ben Hartsock TE IND 4 33
Ben Utecht TE IND 0 0
Joey Hawkins TE IND 0 0
Joe Kuykendall TE IND 0 0
Andre Johnson WR HOU 90 1324
Jabar Gaffney WR HOU 53 683
Corey Bradford WR HOU 29 467
Jerome Mathis WR HOU 28 394
Derick ArmstronWR HOU 29 392
Reggie Swinton WR HOU 10 112
Billy Miller TE HOU 22 229
Marcellus RiversTE HOU 9 79
Mark Bruener TE HOU 5 37
Bennie Joppru TE HOU 0 0
Aaron Halterma TE HOU 0 0
Javon Walker WR GB 84 1330
Donald Driver WR GB 76 1060
Robert FergusonWR GB 29 413
Antonio ChatmaWR GB 19 215
Terrence Murph WR GB 16 171
Craig Bragg WR GB 7 101
Andrae Thurma WR GB 11 84
Bubba Franks TE GB 37 331
David Martin TE GB 14 104
Ben Steele TE GB 5 48
Alphonso Collin TE GB 0 0
Steve Fleming TE GB 0 0
Roy Williams WR DET 78 1185
Charles Rogers WR DET 69 762
Mike Williams WR DET 61 789
Tai Streets WR DET 32 349
Kevin Johnson WR DET 37 399
Scott Vines WR DET 3 59
David Kircus WR DET 3 54
Eddie DrummonWR DET 0 0
Marcus Pollard TE DET 35 401
Casey Fitzsimm TE DET 14 119
Justin Swift TE DET 2 17
Khary Jackson TE DET 0 0
Leonard Stephe TE DET 0 4
Ashley Lelie WR DEN 65 1056
Rod Smith WR DEN 78 1055
Darius Watts WR DEN 48 597
Jerry Rice WR DEN 40 543
Nate Jackson WR DEN 8 72
Triandos Luke WR DEN 8 73
Jeb Putzier TE DEN 38 425
Stephen Alexan TE DEN 38 385
Patrick Hape TE DEN 8 38
Dwayne CarswelTE DEN 5 21
Mike Leach TE DEN 0 0
Keyshawn JohnsWR DAL 73 967
Terry Glenn WR DAL 57 854
Quincy Morgan WR DAL 47 611
Patrick Crayton WR DAL 15 202
Terrance CopperWR DAL 10 126
Ahmad Merritt WR DAL 6 50
Jason Witten TE DAL 79 863
Dan Campbell TE DAL 16 142
Tony Curtis TE DAL 0 0
Sean Ryan TE DAL 0 0
Brett Pierce TE DAL 0 0
Antonio Bryant WR CLE 66 977
Braylon Edward WR CLE 67 827
Andre Davis WR CLE 50 754
Dennis NorthcutWR CLE 47 641
Frisman JacksonWR CLE 9 122
Steve Heiden TE CLE 26 219
Aaron Shea TE CLE 30 258
Keith Heinrich TE CLE 4 28
Keith Willis TE CLE 0 0
Ivory McCoy TE CLE 0 0
Chad Johnson WR CIN 89 1328
T.J. Houshmand WR CIN 74 956
Peter Warrick WR CIN 50 548
Kelley Washingt WR CIN 37 327
Chris Henry WR CIN 17 184
Kevin Walter WR CIN 12 84
Cliff Russell WR CIN 2 17
Matt Schobel TE CIN 22 223
Reggie Kelly TE CIN 15 114
Tony Stewart TE CIN 11 96
Brad St. Louis TE CIN 0 0
Michael Woolrid TE CIN 0 0
Muhsin Muham WR CHI 66 971
Justin Gage WR CHI 41 499
Bernard Berrian WR CHI 25 371
Bobby Wade WR CHI 28 324
Eddie Berlin WR CHI 12 188
Mark Bradley WR CHI 21 198
Ron Johnson WR CHI 4 42
Desmond Clark TE CHI 26 290
Dustin Lyman TE CHI 13 100
Darnell Sanders TE CHI 6 37
John Gilmore TE CHI 2 24
John Owens TE CHI 2 14
Steve Smith WR CAR 81 1124
Keary Colbert WR CAR 59 942
Rod Gardner WR CAR 57 714
Ricky Proehl WR CAR 37 474
Drew Carter WR CAR 29 316
Karl Hankton WR CAR 10 165
Kris Mangum TE CAR 33 340
Mike Seidman TE CAR 14 122
Chad Mustard TE CAR 4 38
Michael Gaines TE CAR 4 37
Dan Curley TE CAR 0 0
Lee Evans WR BUF 67 927
Eric Moulds WR BUF 71 1003
Roscoe Parrish WR BUF 47 526
Sam Aiken WR BUF 14 184
Josh Reed WR BUF 18 208
Mark Campbell TE BUF 28 264
Tim Euhus TE BUF 13 118
Rod Trafford TE BUF 4 31
Kevin Everett TE BUF 0 0
Brad Cieslak TE BUF 0 0
Derrick Mason WR BAL 70 1034
Mark Clayton WR BAL 56 789
Clarence Moore WR BAL 38 469
Randy Hymes WR BAL 20 268
Devard Darling WR BAL 18 199
Patrick Johnson WR BAL 4 60
Todd Heap TE BAL 61 721
Terry Jones TE BAL 18 146
Daniel Wilcox TE BAL 11 99
Darnell Dinkins TE BAL 4 39
Trent Smith TE BAL 0 0
Michael Jenkins WR ATL 58 774
Peerless Price WR ATL 44 577
Dez White WR ATL 39 482
Brian Finneran WR ATL 28 390
Roddy White WR ATL 21 267
Alge Crumpler TE ATL 52 713
Dwayne BlakleyTE ATL 3 28
Mark Anelli TE ATL 0 0
Derek Rackley TE ATL 0 0
David Rackley TE ATL 0 0
Larry Fitzgerald WR ARI 78 1164
Anquan Boldin WR ARI 74 1083
Bryant Johnson WR ARI 46 537
Charles Lee WR ARI 18 245
Lawrence HamilWR ARI 12 131
Reggie Newhou WR ARI 9 78
Eric Edwards TE ARI 30 306
Adam Bergen TE ARI 0 0
Robert Blizzard TE ARI 0 0
Andy Stokes TE ARI 0 0
John Bronson TE ARI 0 0
h player is in for all 375 players
Table 2:
Conference? Team Conference
ARI National
ATL National
BAL American
BUF American
CAR National
CHI National
CIN American
CLE American
DAL National
DEN American
DET National
GB National
HOU American
IND American
JAC American
KC American
MIA American
MIN National
NE American
NO National
NYG National
NYJ American
OAK American
PHI National
PIT American
SD American
SEA National
SF National
STL National
TB National
TEN American
WAS National
V-Lookups
exercise 3: Find the corresponding conference each player is
Table 1:
Darnerien McCaWR WAS 28 355
Roydell WilliamsWR TEN 11 94
Ben Hall TE TEN 0 0
Anthony DiCos WR TB 0 0
Shaun McDonal WR STL 41 473
Brandon ManumTE STL 18 190
Jerramy StevensTE SEA 38 397
Keenan McCardeWR SD 71 898
Antwaan RandleWR PIT 68 897
Courtney Ander TE OAK 23 315
John Paul FoschiTE OAK 0 0
Laveranues Col WR NYJ 86 1196
Justin McCarein WR NYJ 64 879
Jerricho CotcherWR NYJ 31 306
Visanthe ShiancTE NYG 8 42
Donte' StallwortWR NO 59 861
Devery HendersWR NO 34 376
Marcus Robinso WR MIN 48 654
Jermaine WigginTE MIN 35 347
Jimmy KleinsassTE MIN 29 287
Derrius Thomps WR MIA 24 348
Randy McMichaeTE MIA 65 682
Ed Perry TE MIA 0 0
Craphonso Thor WR KC 19 241
Edwin Thompso TE KC 0 0
George Wrighst TE JAC 18 165
Brandon Stokle WR IND 63 986
Aaron MooreheaWR IND 5 62
Derick ArmstronWR HOU 29 392
Marcellus RiversTE HOU 9 79
Aaron Halterma TE HOU 0 0
Robert FergusonWR GB 29 413
Antonio ChatmaWR GB 19 215
Terrence Murph WR GB 16 171
Andrae Thurma WR GB 11 84
Alphonso Collin TE GB 0 0
Eddie DrummonWR DET 0 0
Casey Fitzsimm TE DET 14 119
Leonard Stephe TE DET 0 4
Stephen Alexan TE DEN 38 385
Dwayne CarswelTE DEN 5 21
Keyshawn JohnsWR DAL 73 967
Terrance CopperWR DAL 10 126
Braylon Edward WR CLE 67 827
Dennis NorthcutWR CLE 47 641
Frisman JacksonWR CLE 9 122
T.J. Houshmand WR CIN 74 956
Kelley Washingt WR CIN 37 327
Michael Woolrid TE CIN 0 0
Muhsin Muham WR CHI 66 971
Dwayne BlakleyTE ATL 3 28
Lawrence HamilWR ARI 12 131
Reggie Newhou WR ARI 9 78
nce each player is in using "Table 2" from the previous worksheet
Conference?
Data Filters
Alt + D + F + F (Data - Filter - AutoFilter)

(Windows-based Excel only)
Keyboard shortcut: Hold down the Alt key (use Option key for Excel for Mac) to access the data fi
Click on the data filter to show a list of all unique entries in that column
Filtered columns have a blue arrow column1 column2

Filtered rows have blue numbers 86 4
All rows not matching the selected criteria are hidden 76 7
9 3
23 2
52 6
Mac) to access the data filter
column3
0.28
0.39
0.42
0.63
0.43
Data Filters
exercise 1: Filter out all blank rows from this data set
Darnerien McCants WR WAS 28 355


Ben Hall TE TEN 0 0
Anthony DiCosmo WR TB 0 0

Shaun McDonald WR STL 41 473
Brandon Manumaleuna TE STL 18 190

Jerramy Stevens TE SEA 38 397
Keenan McCardell WR SD 71 898
Antwaan Randle-El WR PIT 68 897

Courtney Anderson TE OAK 23 315
John Paul Foschi TE OAK 0 0
Laveranues Coles WR NYJ 86 1196

Jerricho Cotchery WR NYJ 31 306

Visanthe Shiancoe TE NYG 8 42
Donte' Stallworth WR NO 59 861
Devery Henderson WR NO 34 376

Marcus Robinson WR MIN 48 654
Jermaine Wiggins TE MIN 35 347
Jimmy Kleinsasser TE MIN 29 287

Randy McMichael TE MIA 65 682
Ed Perry TE MIA 0 0
Craphonso Thorpe WR KC 19 241
George Wrighster TE JAC 18 165
Brandon Stokley WR IND 63 986
Aaron Moorehead WR IND 5 62

Derick Armstrong WR HOU 29 392
Marcellus Rivers TE HOU 9 79
Aaron Halterman TE HOU 0 0
Robert Ferguson WR GB 29 413
Antonio Chatman WR GB 19 215

Andrae Thurman WR GB 11 84
Alphonso Collins TE GB 0 0
Eddie Drummond WR DET 0 0
Casey Fitzsimmons TE DET 14 119
Leonard Stephens TE DET 0 4

Stephen Alexander TE DEN 38 385
Dwayne Carswell TE DEN 5 21
Keyshawn Johnson WR DAL 73 967


Braylon Edwards WR CLE 67 827
Dennis Northcutt WR CLE 47 641
Frisman Jackson WR CLE 9 122
T.J. Houshmandzadeh WR CIN 74 956
Kelley Washington WR CIN 37 327

Michael Woolridge TE CIN 0 0
Muhsin Muhammad WR CHI 66 971



Dwayne Blakley TE ATL 3 28


Lawrence Hamilton WR ARI 12 131
Reggie Newhouse WR ARI 9 78
his data set
Data Filters
exercise 2: Find all the players whose first name is "Alex"
Wildcards:
Use To find
? (question mark) Any single character
For example, sm?th finds "smith" and "smyth"
* (asterisk) Any number of characters
For example, *east finds "Northeast" and "Southeast"
~ (tilde) followed by ?, *, or ~ A question mark, asterisk, or tilde
For example, fy91~? finds "fy91?"
Player Position
Aaron Halterman TE
Aaron Moorehead WR
Aaron Shea TE
Aaron Walker TE
Adam Bergen TE
Ahmad Merritt WR
Alex Bannister WR
Alex Holmes TE
Alex Smith TE
Alge Crumpler TE
Alphonso Collins TE
Amani Toomer WR
Andrae Thurman WR
Andre Davis WR
Andre Johnson WR
Andy Stokes TE
Andy Thorn TE
Anquan Boldin WR
Anthony Becht TE
Anthony DiCosmo WR
Antonio Bryant WR
Antonio Chatman WR
Antonio Gates TE
Antwaan Randle-El WR
Arnaz Battle WR
Ashley Lelie WR
Avion Black WR
Beau Fullerton TE
Ben Hall TE
Ben Hartsock TE
Ben Steele TE
Ben Troupe TE
Ben Utecht TE
Ben Watson TE
Bennie Joppru TE
Bernard Berrian WR
Bethel Johnson WR
Billy Baber TE
Billy McMullen WR
Billy Miller TE
Bo Scaife TE
Bobby Engram WR
Bobby Shaw WR
Bobby Wade WR
Boo Williams TE
Brad Cieslak TE
Brad Pyatt WR
Brad St. Louis TE
Brandon Jones WR
Brandon Lloyd WR
Brandon Manumaleuna TE
Brandon Stokley WR
Braylon Edwards WR
Brett Pierce TE
Brian Finneran WR
Brian Jones TE
Brian Kozlowski TE
Brock Edwards TE
Bryan Gilmore WR
Bryant Johnson WR
Bubba Franks TE
Calen Powell TE
Cam Quayle TE
Casey Fitzsimmons TE
Cedric James WR
Chad Johnson WR
Chad Lewis TE
Chad Mustard TE
Charles Lee WR
Charles Rogers WR
Chris Baker TE
Chris Chambers WR
Chris Cole WR
Chris Cooley TE
Chris Doering WR
Chris Henry WR
Chris Horn WR
Chris Luzar TE
Christian Fauria TE
Clarence Moore WR
Cliff Russell WR
Cody McCarty TE
Corey Bradford WR
Cortez Hankton WR
Courtney Anderson TE
Courtney Roby WR
Craig Bragg WR
Craphonso Thorpe WR
Dallas Clark TE
Dan Campbell TE
Dan Curley TE
Dane Looker WR
Daniel Graham TE
Daniel Wilcox TE
Danny Farmer WR
Danny Young TE
Dante Hall WR
Darius Watts WR
Darius Williams TE
Darnell Dinkins TE
Darnell Sanders TE
Darnerien McCants WR
Darrell Jackson WR
Daryl Jones WR
Dauntae Finger TE
Dave Moore TE
David Boston WR
David Givens WR
David Kircus WR
David Martin TE
David Patten WR
David Rackley TE
David Terrell WR
David Tyree WR
Deion Branch WR
Dennis Northcutt WR
Derek Rackley TE
Derick Armstrong WR
Derrick Mason WR
Derrius Thompson WR
Desmond Clark TE
Devard Darling WR
Devery Henderson WR
Dez White WR
Donald Driver WR
Donald Lee TE
Donte' Stallworth WR
Doug Gabriel WR
Doug Jolley TE
Drew Bennett WR
Drew Carter WR
Dustin Lyman TE
Dwayne Blakley TE
Dwayne Carswell TE
Ed Perry TE
Eddie Berlin WR
Eddie Drummond WR
Eddie Kennison WR
Edell Shepherd WR
Edwin Thompson TE
Eric Edwards TE
Eric Johnson TE
Eric Moulds WR
Eric Parker WR
Ernie Conwell TE
Erron Kinney TE
Fred Gibson WR
Freddie Mitchell WR
Frisman Jackson WR
George Wrighster TE
Greg Lewis WR
Heath Miller TE
Hines Ward WR
Ike Hilliard WR
Isaac Bruce WR
Itula Mili TE
Ivory McCoy TE
Jabar Gaffney WR
Jabari Holloway TE
Jamaar Taylor WR
James Dearth TE
James Thrash WR
James Whalen TE
Jason Dunn TE
Jason McAddley WR
Jason Rader TE
Jason Witten TE
Javin Hunter WR
Javon Walker WR
Jeb Putzier TE
Jed Weaver TE
Jeff Dugan TE
Jerame Tuman TE
Jeremy Shockey TE
Jerheme Urban WR
Jermaine Wiggins TE
Jerome Mathis WR
Jerome Pathon WR
Jerramy Stevens TE
Jerricho Cotchery WR
Jerry Porter WR
Jerry Rice WR
Jimmy Farris WR
Jimmy Kleinsasser TE
Jimmy Smith WR
Joe Horn WR
Joe Jurevicius WR
Joe Kuykendall TE
Joel Dreessen TE
Joey Galloway WR
Joey Hawkins TE
John Bronson TE
John Gilmore TE
John Lumpkin TE
John Owens TE
John Paul Foschi TE
John Stone WR
Johnnie Morant WR
Johnnie Morton WR
Jonathan Carter WR
Josh Norman TE
Josh Reed WR
Justin Gage WR
Justin McCareins WR
Justin Peelle TE
Justin Swift TE
Karl Hankton WR
Kassim Osgood WR
Keary Colbert WR
Keenan Howry WR
Keenan McCardell WR
Keith Heinrich TE
Keith Willis TE
Kelley Washington WR
Kelly Campbell WR
Kendall Newson WR
Kevin Curtis WR
Kevin Dyson WR
Kevin Everett TE
Kevin Johnson WR
Kevin Walter WR
Keyshawn Johnson WR
Khary Jackson TE
Kris Wilson TE
Kyle Brady TE
L.J. Smith TE
Lamont Hall TE
Larry Fitzgerald WR
Laveranues Coles WR
Lawrence Hamilton WR
Lee Evans WR
Lee Mays WR
Leonard Stephens TE
Malcom Floyd WR
Marc Boerigter WR
Marcellus Rivers TE
Marcus Maxwell WR
Marcus Pollard TE
Marcus Robinson WR
Mark Anelli TE
Mark Bradley WR
Mark Bruener TE
Mark Campbell TE
Marty Booker WR
Marvin Harrison WR
Matt Cushing TE
Matt Jones WR
Matt Kranchick TE
Matt Schobel TE
Matthew Chila TE
Michael Clayton WR
Michael Gaines TE
Michael Jenkins WR
Michael Lewis WR
Michael Woolridge TE
Mike Bartrum TE
Mike Brake TE
Mike Furrey WR
Mike Kallfelz TE
Mike Leach TE
Mike Seidman TE
Mike Sellers TE
Mike Williams WR
Nate Burleson WR
Nate Jackson WR
Nate Lawrie TE
Nate Poole WR
Neil Johnson TE
P.J. Fleck WR
Patrick Crayton WR
Patrick Estes TE
Patrick Hape TE
Patrick Johnson WR
Peerless Price WR
Peter Warrick WR
Plaxico Burress WR
Quincy Morgan WR
Randal Williams WR
Randy Hymes WR
Randy McMichael TE
Randy Moss WR
Rashaun Woods WR
Reche Caldwell WR
Reggie Brown WR
Reggie Kelly TE
Reggie Newhouse WR
Reggie Swinton WR
Reggie Wayne WR
Reggie Williams WR
Richard Owens TE
Rickey Dudley TE
Ricky Proehl WR
Robert Blizzard TE
Robert Ferguson WR
Robert Royal TE
Rod Gardner WR
Rod Smith WR
Rod Trafford TE
Roddy White WR
Roland Williams TE
Ron Johnson WR
Ronald Curry WR
Roscoe Parrish WR
Roy Williams WR
Roydell Williams WR
Ryan Hannam TE
Ryan Krause TE
Sam Aiken WR
Samie Parker WR
Santana Moss WR
Scott Vines WR
Sean Berton TE
Sean Ryan TE
Shad Meier TE
Shaun McDonald WR
Stephen Alexander TE
Steve Bush TE
Steve Cucci TE
Steve Fleming TE
Steve Heiden TE
Steve Smith WR
T.J. Houshmandzadeh WR
Tai Streets WR
Talman Gardner WR
Taylor Jacobs WR
Terrell Owens WR
Terrence Murphy WR
Terry Glenn WR
Terry Jones TE
Teyo Johnson TE
Tim Carter WR
Tim Dwight WR
Tim Euhus TE
Todd Heap TE
Todd Yoder TE
Tony Curtis TE
Tony Gonzalez TE
Tony Stewart TE
Torry Holt WR
Travis Taylor WR
Triandos Luke WR
Troy Brown WR
Troy Edwards WR
Troy Walters WR
Troy Williamson WR
Tyrone Calico WR
Vincent Jackson WR
Visanthe Shiancoe TE
Walter Rasby TE
Wayne Chrebet WR
Will Heller TE
Willie Ponder WR
Zuriel Smith WR
me is "Alex"
Team Catches Yards

HOU 0 0
IND 5 62
CLE 30 258
SF 9 111
ARI 0 0
DAL 6 50
SEA 4 46
MIA 0 0
TB 17 189
ATL 52 713
GB 0 0
NYG 68 973
GB 11 84
CLE 50 754
HOU 90 1324
ARI 0 0
PHI 0 0
ARI 74 1083
TB 28 245
TB 0 0
CLE 66 977
GB 19 215
SD 78 879
PIT 68 897
SF 44 468
DEN 65 1056
MIN 2 17
NYG 0 0
TEN 0 0
IND 4 33
GB 5 48
TEN 43 411
IND 0 0
NE 39 357
HOU 0 0
CHI 25 371
NE 26 384
WAS 2 21
PHI 4 29
HOU 22 229
TEN 9 84
SEA 51 645
SEA 35 439
CHI 28 324
NO 28 298
BUF 0 0
IND 3 10
CIN 0 0
TEN 38 436
SF 68 935
STL 18 190
IND 63 986
CLE 67 827
DAL 0 0
ATL 28 390
JAC 6 87
WAS 6 58
SEA 0 0
MIA 12 158
ARI 46 537
GB 37 331
SEA 0 0
JAC 0 0
DET 14 119
NE 0 10
CIN 89 1328
PHI 25 255
CAR 4 38
ARI 18 245
DET 69 762
NYJ 28 278
MIA 74 1038
JAC 2 18
WAS 45 408
PIT 12 144
CIN 17 184
KC 18 205
NYG 1 12
NE 14 146
BAL 38 469
CIN 2 17
SD 0 0
HOU 29 467
JAC 16 149
OAK 23 315
TEN 32 393
GB 7 101
KC 19 241
IND 41 535
DAL 16 142
CAR 0 0
STL 17 192
NE 27 293
BAL 11 99
MIA 3 38
SD 0 0
KC 24 276
DEN 48 597
NYG 0 0
BAL 4 39
CHI 6 37
WAS 28 355
SEA 83 1232
MIN 3 30
STL 0 0
TB 4 40
MIA 42 510
NE 63 933
DET 3 54
GB 14 104
WAS 68 853
ATL 0 0
NE 37 475
NYG 20 274
NE 74 1009
CLE 47 641
ATL 0 0
HOU 29 392
BAL 70 1034
MIA 24 348
CHI 26 290
BAL 18 199
NO 34 376
ATL 39 482
GB 76 1060
MIA 12 116
NO 59 861
OAK 16 270
NYJ 32 340
TEN 72 1114
CAR 29 316
CHI 13 100
ATL 3 28
DEN 5 21
MIA 0 0
CHI 12 188
DET 0 0
KC 59 996
TB 2 19
KC 0 0
ARI 30 306
SF 66 649
BUF 71 1003
SD 36 541
NO 24 284
TEN 38 335
PIT 4 34
KC 26 376
CLE 9 122
JAC 18 165
PHI 66 764
PIT 38 373
PIT 91 1107
TB 47 501
STL 75 1060
SEA 24 268
CLE 0 0
HOU 53 683
WAS 5 52
NYG 8 182
NYJ 0 0
WAS 21 256
PHI 17 145
KC 16 114
SF 6 83
MIA 0 0
DAL 79 863
SF 2 12
GB 84 1330
DEN 38 425
NE 9 111
MIN 0 0
PIT 9 101
NYG 64 697
SEA 12 232
MIN 35 347
HOU 28 394
SEA 32 449
SEA 38 397
NYJ 31 306
OAK 72 1023
DEN 40 543
WAS 2 25
MIN 29 287
JAC 79 1151
NO 91 1289
SEA 36 422
IND 0 0
NYJ 0 0
TB 54 831
IND 0 0
ARI 0 0
CHI 2 24
NE 0 0
CHI 2 14
OAK 0 0
OAK 3 54
OAK 2 45
SF 54 703
NYJ 12 209
OAK 7 91
BUF 18 208
CHI 41 499
NYJ 64 879
SD 10 81
DET 2 17
CAR 10 165
SD 19 396
CAR 59 942
MIN 4 18
SD 71 898
CLE 4 28
CLE 0 0
CIN 37 327
MIN 18 336
MIA 1 28
STL 60 727
WAS 14 158
BUF 0 0
DET 37 399
CIN 12 84
DAL 73 967
DET 0 0
KC 21 201
JAC 20 197
PHI 42 487
NO 2 4
ARI 78 1164
NYJ 86 1196
ARI 12 131
BUF 67 927
PIT 11 135
DET 0 4
SD 3 49
KC 22 434
HOU 9 79
SF 4 38
DET 35 401
MIN 48 654
ATL 0 0
CHI 21 198
HOU 5 37
BUF 28 264
MIA 63 805
IND 92 1194
PIT 1 9
JAC 33 349
PIT 0 0
CIN 22 223
NYJ 0 0
TB 84 1253
CAR 4 37
ATL 58 774
NO 11 221
CIN 0 0
PHI 4 32
STL 0 0
STL 10 98
KC 0 0
DEN 0 0
CAR 14 122
WAS 2 29
DET 61 789
MIN 74 1132
DEN 8 72
TB 2 23
NO 12 142
SF 0 0
SF 9 74
DAL 15 202
SF 0 0
DEN 8 38
BAL 4 60
ATL 44 577
CIN 50 548
NYG 74 1057
DAL 47 611
OAK 1 17
BAL 20 268
MIA 65 682
OAK 93 1311
SF 31 336
SD 51 796
PHI 37 467
CIN 15 114
ARI 9 78
HOU 10 112
IND 75 1159
JAC 58 737
MIN 9 76
OAK 9 94
CAR 37 474
ARI 0 0
GB 29 413
WAS 8 83
CAR 57 714
DEN 78 1055
BUF 4 31
ATL 21 267
STL 4 28
CHI 4 42
OAK 37 486
BUF 47 526
DET 78 1185
TEN 11 94
SEA 8 116
SD 6 89
BUF 14 184
KC 45 685
WAS 67 950
DET 3 59
MIN 6 57
DAL 0 0
NO 21 167
STL 41 473
DEN 38 385
SF 12 74
TEN 0 0
GB 0 0
CLE 26 219
CAR 81 1124
CIN 74 956
DET 32 349
NO 18 234
WAS 26 275
PHI 86 1261
GB 16 171
DAL 57 854
BAL 18 146
OAK 25 266
NYG 32 440
NE 13 169
BUF 13 118
BAL 61 721
JAC 15 160
DAL 0 0
KC 75 933
CIN 11 96
STL 96 1384
MIN 56 713
DEN 8 73
NE 18 181
JAC 32 372
IND 11 134
MIN 58 794
TEN 55 852
SD 11 123
NYG 8 42
PIT 6 58
NYJ 32 413
TB 7 56
NYG 18 197
NYG 2 23
Data Filters
exercise 3: Use a vlookup and data filters to find all players who
Aaron Halterman TE HOU 0 0
Aaron Moorehead WR IND 5 62
Alphonso Collins TE GB 0 0

Andrae Thurman WR GB 11 84
Anthony DiCosmo WR TB 0 0
Antonio Chatman WR GB 19 215
Antwaan Randle-El WR PIT 68 897

Ben Hall TE TEN 0 0
Brandon Manumaleuna TE STL 18 190
Brandon Stokley WR IND 63 986
Braylon Edwards WR CLE 67 827
Casey Fitzsimmons TE DET 14 119

Courtney Anderson TE OAK 23 315
Craphonso Thorpe WR KC 19 241
Darnerien McCants WR WAS 28 355
Dennis Northcutt WR CLE 47 641
Derick Armstrong WR HOU 29 392
Derrius Thompson WR MIA 24 348
Devery Henderson WR NO 34 376
Donte' Stallworth WR NO 59 861
Dwayne Blakley TE ATL 3 28
Dwayne Carswell TE DEN 5 21
Ed Perry TE MIA 0 0
Eddie Drummond WR DET 0 0
Edwin Thompson TE KC 0 0

Frisman Jackson WR CLE 9 122
George Wrighster TE JAC 18 165
Jermaine Wiggins TE MIN 35 347
Jerramy Stevens TE SEA 38 397
Jerricho Cotchery WR NYJ 31 306
Jimmy Kleinsasser TE MIN 29 287
John Paul Foschi TE OAK 0 0
Justin McCareins WR NYJ 64 879
Keenan McCardell WR SD 71 898
Kelley Washington WR CIN 37 327
Keyshawn Johnson WR DAL 73 967

Laveranues Coles WR NYJ 86 1196
Lawrence Hamilton WR ARI 12 131
Leonard Stephens TE DET 0 4
Marcellus Rivers TE HOU 9 79
Marcus Robinson WR MIN 48 654

Michael Woolridge TE CIN 0 0

Randy McMichael TE MIA 65 682
Reggie Newhouse WR ARI 9 78
Robert Ferguson WR GB 29 413
Roydell Williams WR TEN 11 94
Shaun McDonald WR STL 41 473
Stephen Alexander TE DEN 38 385
T.J. Houshmandzadeh WR CIN 74 956

Terrence Murphy WR GB 16 171


Visanthe Shiancoe TE NYG 8 42

o find all players who are in the National conference with at least 1000 Yards
Conference?
t least 1000 Yards
REPT() function
What: By manipulating the text function REPT(), we can create visual displays of numbers without
Why: Quickly displays information visually without much effort
=REPT(text,number_times)
=REPT("X",10) XXXXXXXXXX
=REPT("Oh, No! ",3) Oh, No! Oh, No! Oh, No!
=REPT("|",30) ||||||||||||||||||||||||||||||
||||||||||| <== 8 pt Arial
Example 1:
Name HR BB K short bars longer bars

Nomar Garciaparra 12 28 17 |||||||||||| 12 |||||||||||||||||||||||| 12
Albert Pujols 32 57 26 |||||||||||||||||||||||||||||||| 32 |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 32
Scott Hatteberg 9 43 21 ||||||||| 9 |||||||||||||||||| 9
Lance Berkman 26 50 64 |||||||||||||||||||||||||| 26 |||||||||||||||||||||||||||||||||||||||||||||||||||| 26
Travis Hafner 29 72 74 ||||||||||||||||||||||||||||| 29 |||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 29
Justin Morneau 25 27 60 ||||||||||||||||||||||||| 25 |||||||||||||||||||||||||||||||||||||||||||||||||| 25
Lyle Overbay 16 34 57 |||||||||||||||| 16 |||||||||||||||||||||||||||||||| 16
Paul Konerko 24 39 56 |||||||||||||||||||||||| 24 |||||||||||||||||||||||||||||||||||||||||||||||| 24
Nick Johnson 14 63 53 |||||||||||||| 14 |||||||||||||||||||||||||||| 14
Shea Hillenbrand 12 14 40 |||||||||||| 12 |||||||||||||||||||||||| 12
Adrin Gonzlez 18 21 62 |||||||||||||||||| 18 |||||||||||||||||||||||||||||||||||| 18
Jim Thome 32 61 99 |||||||||||||||||||||||||||||||| 32 |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 32
Kevin Youkilis 11 60 76 ||||||||||| 11 |||||||||||||||||||||| 11
Mike Jacobs 14 30 63 |||||||||||||| 14 |||||||||||||||||||||||||||| 14
Todd Helton 11 59 37 ||||||||||| 11 |||||||||||||||||||||| 11
Todd Walker 5 35 26 ||||| 5 |||||||||| 5
Ryan Howard 31 37 104 ||||||||||||||||||||||||||||||| 31 |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 31
Doug Mientkiewicz 4 35 49 |||| 4 |||||||| 4
Prince Fielder 17 29 82 ||||||||||||||||| 17 |||||||||||||||||||||||||||||||||| 17
Michael Cuddyer 12 37 71 |||||||||||| 12 |||||||||||||||||||||||| 12
Chris Shelton 16 31 92 |||||||||||||||| 16 |||||||||||||||||||||||||||||||| 16
Adam LaRoche 17 33 72 ||||||||||||||||| 17 |||||||||||||||||||||||||||||||||| 17
Mark Teixeira 13 53 75 ||||||||||||| 13 |||||||||||||||||||||||||| 13
=REPT("|",$B41*2)&" "&$B41
Add a common multiplier or divisor to adjust the length o
splays of numbers without charting
Example 2: Example 3:
Name HR BB K BB-K Name HR

Nomar Garciaparra 12 28 17 11 ||||| Nomar Garc 12
Albert Pujols 32 57 26 31 ||||||||||||||| Albert Pujol 32
Scott Hatteberg 9 43 21 22 ||||||||||| Scott Hatte 9
Lance Berkman 26 50 64 -14 ||||||| Lance Berk 26
Travis Hafner 29 72 74 -2 | Travis Hafn 29
Justin Morneau 25 27 60 -33 |||||||||||||||| Justin Morn 25
Lyle Overbay 16 34 57 -23 ||||||||||| Lyle Overba 16
Paul Konerko 24 39 56 -17 |||||||| Paul Konerk 24
Nick Johnson 14 63 53 10 ||||| Nick Johnso 14
Shea Hillenbrand 12 14 40 -26 ||||||||||||| Shea Hillen 12
Adrin Gonzlez 18 21 62 -41 |||||||||||||||||||| Adrin Gonz 18
Jim Thome 32 61 99 -38 ||||||||||||||||||| Jim Thome 32
Kevin Youkilis 11 60 76 -16 |||||||| Kevin Youkil 11
Mike Jacobs 14 30 63 -33 |||||||||||||||| Mike Jacobs 14
Todd Helton 11 59 37 22 ||||||||||| Todd Helton 11
Todd Walker 5 35 26 9 |||| Todd Walker 5
Ryan Howard 31 37 104 -67 |||||||||||||||||||||||||||||||| Ryan Howar 31
Doug Mientkiewicz 4 35 49 -14 ||||||| Doug Mientk 4
Prince Fielder 17 29 82 -53 |||||||||||||||||||||||||| Prince Field 17
Michael Cuddyer 12 37 71 -34 ||||||||||||||||| Michael Cu 12
Chris Shelton 16 31 92 -61 |||||||||||||||||||||||||||||| Chris Shelt 16
Adam LaRoche 17 33 72 -39 ||||||||||||||||||| Adam LaRoc 17
Mark Teixeira 13 53 75 -22 ||||||||||| Mark Teixei 13
Adjust the Text color to red and Right-Align the cells

divisor to adjust the length of the bars
BB K
28 17 o
57 26 o
43 21 o
50 64 o
72 74 o
27 60 o
34 57 o
39 56 o
63 53 o
14 40 o
21 62 o
61 99 o
60 76 o
30 63 o
59 37 o
35 26 o
37 104 o
35 49 o
29 82 o
37 71 o
31 92 o
33 72 o
53 75 o
Conditional Formatting
What? Conditional formatting allows you to define the format of individual cells based on a series of criteria
Why? This feature can be used to make your reports look better or to highlight specific values in a table
How? Select "Format -- Conditional Formatting" from the menu to find this dialog box:
Only up to 3 conditions
can be defined. The
first condition takes
precedence over the
second, etc.
This box previews the

format of the cell when
the condition is met
based on a series of criteria Tip: Use the "Format Painter" function to copy
specific values in a table conditional formatting across cells
Condition 1 is met if
the value of the cell is
between 1 and 3.
Click here to select

the format of the
font, border, and
patterns
Formulas allow you to use

absolute or relative
references to determine
whether the condition is met
Use "Add" to create new

conditions
Conditional Formatting, Example 1
Name HR Here is the conditional formatting behind

Adam Dunn 30
Adam LaRoche 17
Adrin Gonzlez 18
Albert Pujols 32
Brad Wilkerson 15
Carlos Delgado 24
Chris Shelton 16
Conor Jackson 8
Doug Mientkiewicz 4
Jason Giambi 28
Jeff Conine 7
Jim Thome 32
Justin Morneau 25
Kevin Youkilis 11
Lance Berkman 26
Lyle Overbay 16
Mark Teixeira 13
Michael Cuddyer 12
Mike Jacobs 14
Nick Johnson 14
Nick Swisher 21
Nomar Garciaparra 12
Paul Konerko 24
Prince Fielder 17
Richie Sexson 19
Ryan Howard 31
Scott Hatteberg 9
Shea Hillenbrand 12
Todd Helton 11
Todd Walker 5
Travis Hafner 29
nal formatting behind this table
This conditional format shades every

other line grey and will maintain the shading
even if you sort the table.
Try sorting the table to see.
Name AB AVG HR BB K
Adam Dunn 349 246 30 73 113
Adam LaRoche 293 270 17 33 72
Adrin Gonzlez 338 299 18 21 62
Albert Pujols 291 330 32 57 26
Brad Wilkerson 280 239 15 37 99
Carlos Delgado 336 250 24 39 84
Chris Shelton 332 271 16 31 92
Conor Jackson 276 264 8 40 37
Doug Mientkiewicz 307 280 4 35 49
Jason Giambi 294 255 28 68 74
Jeff Conine 280 261 7 26 38
Jim Thome 317 297 32 61 99
Justin Morneau 337 309 25 27 60
Kevin Youkilis 353 289 11 60 76
Lance Berkman 335 316 26 50 64
Lyle Overbay 357 305 16 34 57
Mark Teixeira 383 269 13 53 75
Michael Cuddyer 299 271 12 37 71
Mike Jacobs 296 284 14 30 63
Nick Johnson 324 302 14 63 53
Nick Swisher 337 258 21 62 90
Nomar Garciaparra 305 334 12 28 17
Paul Konerko 348 302 24 39 56
Prince Fielder 358 279 17 29 82
Richie Sexson 361 224 19 35 99
Ryan Howard 342 281 31 37 104
Scott Hatteberg 268 321 9 43 21
Shea Hillenbrand 296 301 12 14 40
Todd Helton 292 284 11 59 37
Todd Walker 297 283 5 35 26
Travis Hafner 325 311 29 72 74
This conditional format puts a bar above

any line where HR changes.
Try to change some numbers in the table and watch what happens.
Name AB AVG HR BB K
Albert Pujols 291 330 32 57 26
Jim Thome 317 297 32 61 99
Ryan Howard 342 281 31 37 104
Adam Dunn 349 246 30 73 113
Travis Hafner 325 311 29 72 74
Jason Giambi 294 255 28 68 74
Lance Berkman 335 316 26 50 64
Justin Morneau 337 309 25 27 60
Paul Konerko 348 302 24 39 56
Carlos Delgado 336 250 24 39 84
Nick Swisher 337 258 21 62 90
Richie Sexson 361 224 19 35 99
Adrin Gonzlez 338 299 18 21 62
Prince Fielder 358 279 17 29 82
Adam LaRoche 293 270 17 33 72
Lyle Overbay 357 305 16 34 57
Chris Shelton 332 271 16 31 92
Brad Wilkerson 280 239 15 37 99
Nick Johnson 324 302 14 63 53
Mike Jacobs 296 284 14 30 63
Mark Teixeira 383 269 13 53 75
Nomar Garciaparra 305 334 12 28 17
Shea Hillenbrand 296 301 12 14 40
Michael Cuddyer 299 271 12 37 71
Kevin Youkilis 353 289 11 60 76
Todd Helton 292 284 11 59 37
Scott Hatteberg 268 321 9 43 21
Conor Jackson 276 264 8 40 37
Jeff Conine 280 261 7 26 38
Todd Walker 297 283 5 35 26
Doug Mientkiewicz 307 280 4 35 49
This conditional format automatically colors the bars based on their percentile.
The top 25% of values are colored green while the bottom 25% are colored red
Name AB AVG HR BB K
Nomar Garciaparra ||||||||||||||| 305 ||||||||||||| 334 |||||| 12 ||||||| 28 || 17
Albert Pujols ||||||||||||| 291 ||||||||||||| 330 ||||||||||||| 32 |||||||||||||| 57 |||| 26
Scott Hatteberg ||||||||||||| 268 ||||||||||||| 321 |||| 9 |||||||||| 43 ||| 21
Lance Berkman |||||||||||||||| 335 ||||||||||||| 316 ||||||||||||| 26 |||||||||||| 50 |||||||||| 64
Travis Hafner |||||||||||||||| 325 |||||||||||| 311 ||||||||||||| 29 |||||||||||| 72 |||||||||||| 74
Justin Morneau |||||||||||||||| 337 |||||||||||| 309 |||||||||||| 25 |||||| 27 |||||||||| 60
Lyle Overbay ||||||||||||| 357 |||||||||||| 305 |||||||| 16 |||||||| 34 ||||||||| 57
Paul Konerko ||||||||||||| 348 |||||||||||| 302 |||||||||||| 24 ||||||||| 39 ||||||||| 56
Nick Johnson |||||||||||||||| 324 |||||||||||| 302 ||||||| 14 |||||||||||| 63 |||||||| 53
Shea Hillenbrand |||||||||||||| 296 |||||||||||| 301 |||||| 12 ||| 14 |||||| 40
Adrin Gonzlez |||||||||||||||| 338 |||||||||||| 299 ||||||||| 18 ||||| 21 |||||||||| 62
Jim Thome ||||||||||||||| 317 |||||||||||| 297 ||||||||||||| 32 |||||||||||| 61 |||||||||||| 99
Kevin Youkilis ||||||||||||| 353 |||||||||||| 289 ||||| 11 |||||||||||| 60 |||||||||||| 76
Mike Jacobs |||||||||||||| 296 ||||||||||| 284 ||||||| 14 ||||||| 30 |||||||||| 63
Todd Helton ||||||||||||| 292 ||||||||||| 284 ||||| 11 |||||||||||| 59 |||||| 37
Todd Walker |||||||||||||| 297 ||||||||||| 283 || 5 |||||||| 35 |||| 26
Ryan Howard ||||||||||||| 342 ||||||||||| 281 ||||||||||||| 31 ||||||||| 37 |||||||||||| 104
Doug Mientkiewicz ||||||||||||||| 307 ||||||||||| 280 || 4 |||||||| 35 |||||||| 49
Prince Fielder ||||||||||||| 358 ||||||||||| 279 |||||||| 17 ||||||| 29 ||||||||||||| 82
Michael Cuddyer |||||||||||||| 299 ||||||||||| 271 |||||| 12 ||||||||| 37 ||||||||||| 71
Chris Shelton |||||||||||||||| 332 ||||||||||| 271 |||||||| 16 ||||||| 31 |||||||||||| 92
Adam LaRoche ||||||||||||| 293 ||||||||||| 270 |||||||| 17 |||||||| 33 |||||||||||| 72
Mark Teixeira ||||||||||||| 383 ||||||||||| 269 |||||| 13 ||||||||||||| 53 |||||||||||| 75
Conor Jackson ||||||||||||| 276 ||||||||||| 264 |||| 8 |||||||||| 40 |||||| 37
Jeff Conine ||||||||||||| 280 |||||||||| 261 ||| 7 |||||| 26 |||||| 38
Nick Swisher |||||||||||||||| 337 |||||||||| 258 |||||||||| 21 |||||||||||| 62 |||||||||||| 90
Jason Giambi ||||||||||||| 294 |||||||||| 255 ||||||||||||| 28 |||||||||||| 68 |||||||||||| 74
Carlos Delgado |||||||||||||||| 336 |||||||||| 250 |||||||||||| 24 ||||||||| 39 |||||||||||| 84
Adam Dunn ||||||||||||| 349 |||||||||| 246 ||||||||||||| 30 |||||||||||| 73 |||||||||||| 113
Brad Wilkerson ||||||||||||| 280 ||||||||| 239 ||||||| 15 ||||||||| 37 |||||||||||| 99
Richie Sexson ||||||||||||| 361 ||||||||| 224 ||||||||| 19 |||||||| 35 |||||||||||| 99
Conditional Formatting Exercises
1. Make the numbers in this table red if <0, green if >0
A -12
B 8
C 23
D -3
2. Make every third row in this list grey

(Hint: The function "Row()" returns a row number)
A 9.0
B 9.4
C 2.5
D 5.5
E 2.3
F 7.5
G 1.7
H 3.1
3. Bold the words in this list that are longer than 8 letters long
(Hint: The function "Len(text)" returns the number of characters in a string)
Honey
Grapefruit
Milk
Tea
Cinnamon
Exercise 1: Change the formatting in Graph A to look the same as Gr
Graph A
Player Catches Yards Yards Per Catches

Yards
1000 Santana Moss 67 950 14.18
David Patten 68 853 12.54
900
Darnerien McCants 28 355 12.68
800 James Thrash 21 256 12.19
700 Taylor Jacobs 26 275 10.58
Kevin Dyson 14 158 11.29
600
Jimmy Farris 2 25 12.50
500 Chris Cooley 45 408 9.07
400 Robert Royal 8 83 10.38
Jabari Holloway 5 52 10.40
300
Mike Sellers 2 29 14.50
200 Billy Baber 2 21 10.50
100
0
Santan David Darneri James Taylor Kevin Jimmy Chris Robert Jabari Mike Billy
a Moss Patten en Thrash Jacobs Dyson Farris Cooley Royal Hollowa Sellers Baber
McCant y
s
Graph A
Yards Per Catches
Santana Mo
David Patten
Darnerien M
James Thras
Taylor Jacobs
Kevin Dyson
Jimmy Farris
Chris Cooley
Robert Roya
Jabari Hollow
Mike Sellers
Billy Baber
Mike Sellers
Billy Baber
Graph A
Catches
80
70
60
50
40
C
30
20
10
0
Santan David Darneri James Taylor Kevin Jimmy Chris Robert Jabari Mike Billy
a Moss Patten en Thrash Jacobs Dyson Farris Cooley Royal Hollow Sellers Baber
McCan ay
ts
ook the same as Graph B (three examples)
Graph B
Receivers' Total Yards
1000 950
853
800
600
Yards 408
400 355
256 275
200 158
25
0
Santan David Darneri James Taylor Kevin Jimmy Chris
ert Jabari Mike Billy
al Hollowa Sellers Baber a Moss Patten en Thrash Jacobs Dyson Farris Cooley
y McCant
s
Graph B
Receivers' Yards Per Catches
Billy Baber
Santana Moss Mike Sellers

David Patten
Jabari Holloway
Darnerien McCants
James Thrash Robert Royal
Taylor Jacobs
Chris Cooley 9.0
Kevin Dyson
Jimmy Farris Jimmy Farris
Chris Cooley Kevin Dyson

Robert Royal
Taylor Jacobs
Jabari Holloway
Mike Sellers James Thrash
Billy Baber
Darnerien McCants
David Patten
Santana Moss
Mike Sellers James Thrash
Billy Baber
Darnerien McCants
David Patten
Santana Moss
Graph B
Receiver's Total Catches
75
50 45
Catches
25
0
abari Mike Billy Santan David Darneri James Taylor Kevin Jimmy Chris
Hollow Sellers Baber a Moss Patten en Thrash Jacobs Dyson Farris Cooley
ay McCant
s
ivers' Total Yards
408
158
83
52 29
25 21
Kevin Jimmy Chris Robert Jabari Mike Billy

Dyson Farris Cooley Royal Hollowa Sellers Baber
y
s' Yards Per Catches
10.50
14.50
10.40
10.38
9.07
12.50
11.29
10.58
12.19
12.68
12.54
14.18
12.19
12.68
12.54
14.18
ver's Total Catches
45
Kevin Jimmy Chris Robert Jabari Mike Billy

Dyson Farris Cooley Royal Hollowa Sellers Baber
y
Charts: exercise
Microsoft Excel has notoriously bad defaults for its charts.
Exercise: Change the formatting of the chart on the right to reduce the chart junk. Answers below.
A Microsoft Excel default chart

1
0.9
0.8
0.447868 0.79028 0.974226
0.800279 0.73481 0.353446
0.7
0.325054 0.540395 0.415897
Column B
0.968734 0.064175 0.487742
0.6 0.137358 0.236334 0.895357
Column C
0.031746 0.862361 0.646153 Column D
0.5 0.419356 0.313238 0.01849
0.35919 0.549364 0.236919
0.4 0.924709 0.837588 0.501762
0.117151 0.719619 0.018552
0.390259 0.297741 0.617845
0.3
0.148927 0.082118 0.654362
0.73608 0.367831 0.448809
0.2
0.1
0
1 2 3 4 5 6 7 8 9 10 11 12 13
Visit this site for more examples of fixing charts: http://www.perceptualedge.com/example
Ideas for cleaning this chart:

* Eliminate 3-D (Chart -- Chart Type -- Select 2-D column chart)
* Eliminate the table at the bottom of the chart (Right click on table -- Clear)
* Eliminate gray background and border (Double click on chart background, select None under Area and Non
* Eliminate vertical gridlines. (Right click on a vertical gridline and select Clear)
* Optional: Eliminate borders around the columns. (Double click on column and select None under Border)
k. Answers below.
De-chart junked chart

1
0.9
0.8
0.7
Column B Column B
Column C 0.6 Column C
Column D Column D
0.5
0.4
0.3
0.2
0.1
0
1 2 3 4 5 6 7 8 9 10 11 12
13 13
ceptualedge.com/examples.htm#
ct None under Area and None under Border)

select None under Border)
Column B
Column C
Column D
12 13
Exercise 3: Secondary Axis
It is often userful to show two related data series in a single chart. If the series have very different scale
you will want to add a second axis. Here's how you do it:
1. On Chart Menu, select Source Data
2. Change the data range to include the second metric
3. You will see that the second series doesn't fit well given the scale on the y-axis
4. Right click on the second series and choose Format Data Series
5. Under the Axis tab, select Secondary axis. The new axis will be auto-scaled for the second data series
Amount o
year amount second metric 50000

1997 23564 25
45000
1998 27948 25
1999 31500 50 40000
2000 33021 50
2001 35093 70 35000
2002 36927 80 30000
2003 37321 75
2004 41002 65 25000
2005 43982 60
20000
2006 47283 55
15000
10000
5000
0
1996 1998 2000 2
Amount o
50000
45000
40000
35000
30000
25000
20000
15000
10000
5000
0
25000
20000
15000
10000
5000
0
1996 1998 2000 2
ries have very different scales
led for the second data series
Amount over Time
2000 2002 2004 2006 2008
Amount over Time
90
80
70
60
50
40
30
20
10
0
50
40
30
20
10
0
2000 2002 2004 2006 2008
Exercise 4: Mixed Chart Types
You have more flexibility with Excel charts than you may know. Excel gives you the ability to mix certain
under the customer chart types in the Chart Type dialogue box.
Fortunately, you don't need to depend entirely on these

pre-defined custom types.
Making Mixed Charts is as easy as selecting one of the data ranges

in your chart, right clicking, and selecting Chart Type
The Chart Type that you select will only apply the the range
you selected
Practice with the chart below to discover the different combinations

that are possible
1.2
0.8
0.6
1 2
0.4 1 0.3398148506 0.048911
2 0.0585180813 0.508983
0.2 3 0.5387832046 0.996382
4 0.6835991085 0.99644
0 5 0.8157848206 0.107678
1 2 36 0.8870710247
4 5 6 0.229418
7 8 9 10
7 0.2122196241 0.698583
8 0.6492550553 0.036419
9 0.3132508272 0.519141
10 0.3898340642 0.155745
you the ability to mix certain chart types. You may have seen some of these

Juice Excel Training Worksheets

Uploaded by

Copyright:

Available Formats

Juice Excel Training Worksheets

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Juice Excel Training Worksheets

Uploaded by

Copyright:

Available Formats

What are some of the keyboard shortcuts mentioned in the document?

What are some of the keyboard shortcuts mentioned in the document?

What are some ways to clean up a chart mentioned in the document?

What are some ways to clean up a chart mentioned in the document?

Juice Excel Training Worksheets

Data and functions

Menu-based Keyboard shortcuts

Formatting Text In Worksheet

Inserting Text Automatically

Select only cells that are directly referred to by formulas Cell

jump jump jump jump jump jump jump

Select the blue shaded cells only

select me! select me! select me!

Excel for Windows users Excel for Mac users

Deleting columns: Alt-E-D-C Deleting columns: CTRL+Spacebar,

exercise 1: delete the red columns and rows

s: CTRL+Spacebar, then CTRL-K

Excel for Windows users Excel for Mac users

Inserting columns: Alt-I-C Inserting columns: CTRL+Spa

umns: CTRL+Spacebar, then CTRL-I

nd a row after every blue row

Relative formula result

Absolute formula result

When copyed down, the reference stays the same

wed by an excalimation mark (!), then the cell range

start by going to either:

way for data filters

How Excel stores dates and times.

The 1904 Date System

How Excel displays dates and times.

Excel will recognize an input that is in a date format.

s it 38,687 days after January 1,1900.

nuary 2, 1904 equal to 1.

4 date system and

ate system checkbox is unchecked.

ed in various date formats by clicking Format-Cells-Number-Date-Type.

Function Name optional argument

Functions always begin with a "="

To find a list of all excel functions, go to Insert-->Function

Character Used to:

to find if a value in a cell is an error, use the ISERROR() function

The error messes up computations

ct. The intersection operator is a space between references.

error check formula used:

Can now find the total despite the bad data

=MID(text, start-position, num-characters)

=FIND(text-to-find, within-text, [start-position])

s the character location of the text

Adds strings of text together

exercise 1: Capture the year from the election column

President Vote % Election Exercise 1

exercise 1: create a column containing the full name:

First Name Last Name Name

exercise 2: Split the name into 2 different columns:

Name First Name Last Name

exercise 3: Split the name into 2 different columns:

Name First Name Last Name

=LEFT(B14,FIND(" ",B14)-1) =MID(B14,FIND(" ",B14)+1,20)

=MID(B23,FIND(" ",B23)+1,20) =LEFT(B23,FIND(",",B23)-1)