| |
| |
|
Microsoft
Excel |
You can use this index to see
what topics are covered in each class. If you are looking for a
particular course topic, you can use your browser's search function
to find that topic. Internet Explorer and FireFox users, pressss
CTRL-F.
|
|
Excel 2010 |
|
Excel 2010 Beginner 1 |
Running Time 1 hr, 29 min
00. Intro (9:50)
01. Excel Interface 1 (6:22)
Starting Excel
The Ribbon Changes with Window Size
Title Bar
Maximize, Minimize, Restore Down
Close Button
File Tab - Backstage View
02. Excel Interface 2 (7:06)
The Ribbon
Ribbon Tabs, Groups, Buttons
Help Popup Menus for Each Command
Command Buttons
Drop-Down Menus
Command Button Groups
Galleries
Dialog Box Launcher Buttons
03. Excel Interface 3 (5:30)
Quick Access Toolbar
Adding or Removing a Command from QAT
Status Bar
Views Buttons
Zoom Controls
Scroll Bars
04. Excel Interface 4 (5:55)
Formula Bar
What is a Spreadsheet
Columns, Rows, Cells
Name Box
Mouse Pointer
Sheet Tabs
05. Entering Data (5:19)
Creating a sales summary sheet
Entering text in Excel
Typing data into a cell
Select a Cell with Mouse
Select a Cell with Keyboard
Move with ENTER, TAB
Move with Arrow Keys
Entering Data
06. Editing Data (7:37)
Editing data in a cell
Four methods for editing data
Overtyping your data
Replacing data in your cells
Editing data using the Formula Bar
Editing a cell by double-clicking
Backspace vs. Delete Keys
Edit Mode with F2
Deleting data in a cell
Using UNDO in Excel
Using REDO in Excel
07. Formatting 1 (6:14)
Home, Alignment
Formatting your text
Horizontal cell alignment
Align Right, Left, Center
Format multiple cells at once
A Cell Range defined
Cell Range Notation (A1:B5)
Selecting cell rangess
Bold, Italics, Underline
08. Formatting 2 (8:40)
Changing the Font
Live Preview
Change Font Size Dropdown
Type in a Font Size Manually
Increase Font Size
Decrease Font Size
Resizing Columns
Changing Column Width
Resize Column with Double-Click
Resizing Rows
Background Color / Fill Color
Foreground Color / Font Color
09. Basic Math 1 (7:48)
Moving to a different sheet tab
Using a "scratch" sheet
Adding two cells together
How Formula Work in Excel
Auto Recalculation of Formula
See Formula in Formula Bar
See Result on the Sheet
Basic math operators
Addition
Subtraction
Multiplication
Division
Exponentiation
Using Constants
Clearing a range of cells
Selecting an Entire Column
10. Basic Math 2 (5:11)
Adding Multiple Cells
Select to See Summary in Status Bar
Click to Add Cell to Formula
Order of Operations
Using parentheses in math operations
Average a Group of Cells
11. Save, Load, Print (8:24)
Saving Your Workbook File
Save As a Different File
Open Recent Documents
Open Any Document
Pinning Documents to the List
Create a New Blank Workbook
Print Preview
Print Your Spreadsheet
12. Review (4:57)
|
|
|
Excel 2010 Beginner 2 |
Running Time: 1 hour, 28 minutes
00. Intro (7:37)
01. Cut Copy Paste (9:30)
The Clipboard
Copy, Paste
Cut, Paste
Formula References Updated
Paste Multiple Times
Clipboard Heyboard Shortcuts
02. Autofill (7:46)
Autofill a Formula
Autofill Handle
Mistakes
Moving a cell
Move a Cell Value
03. Cell Formats 1 (10:39)
Text, Number
Missing Leading Zeros
Currency, Accounting
Short Date, Long Date
04. Cell Formats 2 (7:32)
Fraction
Mixed Fraction
Percentage
Increase Decimal
Decrease Decimal
Comma Style
Scientific Style
05. Rows & Columns (9:42)
Clear v. Delete
Selecting Multiple Columns
Delete Columns
Delete Rows
Right-Click Delete
Insert Column
Insert Row
Insert Multiple Row
Clear Contents
06. Sheets (9:34)
Insert New Worksheet
Delete Worksheet
Rename Sheet
Moving Sheets
Copying a Sheet
Sheet Tab Color
07. Sorting Data (6:25)
Sort A to Z
Dealing with Total Rows
Select Everything First
Sort on a Different Column
Use TAB to Change Columns
08. Conditional Formatting (8:03)
Highlight Cells Rules
Greater Than, Less Than
Above Average
Multiple Conditions
Clear Formatting from Sheet
Data Bars
Color Scales
Icon Sets
09. Find & Replace (7:35)
Find Data
Find Next
Find All
Replace
Replace All
10. Review (4:40)
|
|
|
Excel 2010 Beginner 3 |
Running Time: 1 Hour, 40 Minutes
00. Intro (8:00)
01. Functions 1 (8:14)
What is a Function
The SUM Function
Benefits of Functions
02. Functions 2 (9:42)
AVERAGE Function
COUNT
MAX, MIN
Move a Block of Cells
03. Functions 3 (11:17)
Practice with Functions
The Key Cell Format Trick
Insert New Month of Sales
AutoFill Multiple Cells
04. Styles & Themes (9:03)
Selecting Cell Styles
Using Styles to Format
Changing Themes
Theme Colors vs. Standard Colors
05. Page Layout (11:55)
Margins
View, Page Layout
Print Preview
Orientation
Page Size
Set Print Area
Clear Print Area
Page Breaks
Background
Gridlines: View, Print
Headings: View, Print
06. Illustrations 1 (13:11)
Insert a Picture
Move and Resize a Picture
Rotate Picture
Picture Tools, Format Tab
Brightness, Contrast
Reset Picture
Compress Pictures
Delete a Picture
Insert Clipart
07. Illustrations 2 (11:38)
Insert Shapes
Rectangle
Drawing Tools, Format Tab
Shape Styles
How Themes Effect Shape Styles
Shape Outline
Shape Effects
Add Text
Format Text
Quick Mini Toolbar Popup
WordArt Styles
Inserting an Arrow
08. Illustrations 3 (12:39)
Insert SmartArt
Basic Process
Format SmartArt Options
Screenshots
Screen Clippings
Insert WordArt
WordArt Options
09. Review (5:03)
|
|
|
Excel 2010 Beginner 4 |
Running Time: 1 Hour 38 Minutes
00. Introduction (8:46)
01. Charts 1 (10:51)
What is a Chart
Parts of a Chart
Insert a Column Chart
02. Charts 2 (11:24)
Add Data to an Existing Chart
Line Charts
Multiple Series in a Chart
Chart Styles & Themes
03. Charts 3 (16:14)
Selecting Non-Contiguous Data
3D Pie Charts
Move a Chart to a Different Sheet
Format Objects Inside Charts
Chart Layouts
Leader Lines
Change Chart Type
3D Column Charts
Stacked 3D Column Chart
04. Charts 4 (13:52)
Switch Row/Column
Current Selection Box
Format Selection
Reset to Match Style
Insert Shapes
Insert Text Box
Labels
Chart Title
Axis Titles
Legend
Data Labels
Data Table
05. Charts 5 (13:15)
Copy Excel Charts into Word
Creating a Summary Sheet / Dashboard
Separate a Slice of Pie
Create a Leader Line
06. Tables (17:05)
Creating a Table
Why Use a Table
Table Styles
Table Formulas are Uniform
New Rows Automatically Copy Formulas
Sort in a Table
Filter in Tables
Turn off Filtering
Total Row
Insert New Row
07. Review (6:46)
|
|
|
Excel 2010 Beginner 5 |
Running Time: 1 Hour 46 Minutes
00. Introduction (9:35)
01. Insert Text 1 (9:45)
Insert > Text Group
Insert Hyperlink
Web Page Hyperlinks
Email Hyperlinks
Link to Customer Address
Link to Google Maps
02. Insert Text 2 (13:07)
Insert Text Box
Insert WordArt
Insert Signature Line
Insert Object
Insert Symbol
03. Header & Footer (12:55)
Insert Header & Footer
Three Sections
Header & Footer Elements
Entering Text in Header
&[Page] Code for Page Number
Page 1 of 2
Page Footer
Date Code
Insert Picture
Header & Footer Templates
Go To Footer, Header
04. View Window (12:25)
Working with Large Sheets
Create a Large Sheet of Data
Freeze Panes
Freeze Top Row
Freeze First column
Split the Window
05. Borders (7:42)
Print Preview
Zoom to Page
Printing Gridlines
Changing Borders
Top, Left, Right, Bottom
Inside, Outside Borders
Thick Borders
06. Misc Format Tricks (9:22)
Merge and Center
Merge Across
Merge Cells
Unmerge Cells
Cell Orientation
Format Painter
Double-Click Trick
07. Comments (5:56)
Insert Comment
Edit Comment
Delete Comment
Show, Hide Comments
Previous, Next Comment
08. Research (10:00)
Spell Check
Thesaurus
Getting Stock Quotes
Translate
09. Office Online Templates (11:47)
How to Download Templates
Calendar Template
Invoice Template
Home Mortgage Template
10. Review (4:07)
|
|
|
Excel 2010 Expert 1 |
Running Time: 68 Minutes
00. Intro (7:20)
01. Working With Functions (10:06)
Typing in a function
Function library
Grouped categories of functions
AutoSum button
Changing the AutoSum function
Insert Function dialog
CONCATENATE function
Argument Select Buttons
Concatenate with the & sign
02. Relative v Absolute References (8:53)
What is an Absolute Reference?
What is a Relative Reference?
Reference behavior with AutoFill
Sales Tax Rate example
F4 keyboard trick
03. Named Cells and Ranges (7:03)
What is a Named Cell
Create a Name with the Name Box
Jump to a Named Cell
Define Name button
Name Manager
Edit, Delete Names
Create a Named Range
Named Range example with SUM
04. Values on Other Sheets (10:05)
Refer to a Cell on Another Sheet
=SheetName!CellName
Moving a Named Cell
Copy, Paste Link
Creating a Summary Sheet
05. Text Functions 1 (14:10)
Checking if Two Strings are Equal
=A1=B1
EXACT
CONCATENATE
=A1&B1
LEN
LEFT
RIGHT
MID
FIND
Separate First and Last Names
SEARCH
06. Text Functions 2 (6:23)
SUBSTITUTE
REPLACE
TRIM
07. Review (4:27)
|
|
|
Excel 2010 Expert 2 |
|
Running Time: 1 Hour 33 Minutes
00. Intro (7:49)
01. Excel Dates & Times (9:12)
Valid Date Formats
Valid Time Formats
Valid Date/Time Formats
Understanding Internal Dates
Date Arithmetic
Adding Days
Adding Hours
02. Custom Date/Time Codes (6:33)
Customizing Date Formats
Custom Date & Time Codes
03. Date/Time Functions 1 (11:11)
Current System Date/Time
Current Time
NOW()
F9 to Recalculate
Current Date
TODAY()
Calculating the Time Only
Components of a Date
YEAR, MONTH, DAY
HOUR, MINUTE, SECOND
WEEKDAY
Changing WEEKDAY Start Day
Building a Date from Components
DATE
Buildnig a Time from Components
TIME
Adding Dates with DATE Function
Adding Times with TIME Function
04. Date/Time Functions 2 (14:13)
Calculate Difference in Dates
Calculate Age in Years Using Math
INT function
Difference in Whole Years
YEARFRAC
DATEDIF
Difference in Months Between Dates
05. Date/Time Functions 3 (13:49)
Converting Bad Imported Dates
Convert 20110801 to 8/1/2011
Review of LEFT, RIGHT, MID
Convert Text Date to Actual Date
DATEVALUE, TIMEVALUE
EDATE
Determine Last Day of the Month
EOMONTH
Work Days Between Two Dates
NETWORKDAYS
Custom List of Excluded Holidays
Count Number of Workdays Forward
WORKDAY
NETWORKDAYS.INTL
WORKDAY.INTL
Custom Work Days String "0000011"
Week Number of the Year
WEEKNUM
06. Date/Time Functions 4 (12:04)
AutoFill with Days of the Week
AutoFill Date Options
Fill Days, Months, Years, Weekdays
Subtract Two Times
Format as a Number
Convert Days to Hours
Display Hours and Fractions
Display Hours and Minutes
Adding up hours with [h]:mm
07. Date/Time Tips & Tricks (13:57)
Creating a series of dates with AutoFill
Calculating specific dates
First day of a month
Last day of a month
Number of days in a month
What quarter is a date in
First day of the year
Last day of the year
Day number (of the year)
Days remaining in the year
Inserting the current date and time
Adding up hours, minutes, seconds
[h]:mm
[mm]:ss
Displaying fractions of a second
h:mm:ss.00
08. Review (4:28)
|
|
|
Excel 2010 Expert 3 |
|
Running Time: 1 Hour 11 minutes
00. Intro (6:29)
01. Logic Functions (19:05)
TRUE, FALSE Values
0 = FALSE
AND, OR, NOT
Is an invoice late?
Is an invoice paid?
Collection Letter Example
Shipping an Order Example
Credit Card Batching Example
TRUE, FALSE Functions
02. IF Function 1 (14:18)
IF THEN Statement
IF THEN ELSE Statement
Charge Sales Tax Example
Set Tax Rate if in NY
Calculate Sales Tax
Calculate Credit Card Batch Total
Give Students PASS or FAIL Grade
03. IF Function 2 (14:05)
Nested IF Functions
Assign a Letter Grade with IF
Fixing Our Time Sheet Example
Shifts That Cross Over Midnight
Calculating Overtime Pay
04. IS Functions (14:05)
ISBLANK, ISERROR
ISEVEN, ISODD
ISLOGICAL, ISNUMBER
ISTEXT, ISNONTEXT
Extra Space Between Names
FN, MI, LN
"Mr.", "Mrs.", or "Mr. & Mrs."
Divide by Zero errors
ISERROR to fix #DIV/0!
IFERROR to fix #DIV/0!
05. Review (3:20)
|
|
|
Excel 2010 Expert 4 |
Running Time: 1 Hour 10 minutes
00. Intro (5:58)
01. VLOOKUP 1 (8:10)
Look up week day name
VLOOKUP simple function
Lookup Value
Table Array
Column Index Number
Problem With AutoFill
Use Absolute Reference
Use Named Range
02. VLOOKUP 2 (7:01)
Student Letter Grades
Range Lookup
Assign A to F
Convert to Table
Hide empty values
IF and ISBLANK
03. VLOOKUP 3 (9:22)
Employee Time Sheet
Employee List Table
Rename a Table
Timecard Worksheet
Exact Match Lookup
Lookup Employee Name
Lookup Pay Rate
Calculate Hours Worked
Calculate Total Pay
04. Other Lookups (13:50)
HLOOKUP
LOOKUP
MATCH, INDEX
Less Than
Exact Match
Greater Than
Wildcard Match
Backward Lookup
Searching an Array
05. Cell References (10:26)
Construct a cell reference
Deconstruct a cell reference
ADDRESS
INDIRECT
Year to date sales figure
Sales between two months
06. Compare Two Lists (4:18)
Use MATCH to compare
Is item in list missing
ISNA
07. Closest to Value (7:06)
Which value is closest to target
Calculate Difference
Autofill Double-Click Trick
ABS Absolute Value
MIN to find closest value
MATCH to locate the value
INDEX to determine winner
Closest to average value
|
|
|
Excel 2010 Expert 5 |
Running Time 1 Hour, 5 Minutes
00. Intro (5:49)
01. More With SUM (5:01)
Non-contiguous ranges with SUM
Insert function with multiple ranges
Running balance
Running total
02. SUMIF (11:05)
Add up values with specific criteria
">=100"
Variable criteria
">="&A1
SUMIF, COUNTIF, AVERAGEIF
Sum Range vs. Criteria Range
Make sure to use absolute references
03. SUMIFS (10:03)
Sum of commissions by sales rep
Commissions by rep over $100
Orders past due
Unpaid orders past due
SUMIFS, COUNTIFS, AVERAGEIFS
04. Counting & Rounding (11:29)
COUNT
COUNTA
COUNTBLANK
What you see isn't necessarily valid
Decrease decimal hides true value
ROUND
ROUNDUP
ROUNDDOWN
MROUND
CEILING
FLOOR
INT
TRUNC
05. Misc Math (13:03)
MEDIAN
MODE
PRODUCT
Scientific Notation
4.19E+23
POWER
SQRT
10^2
Integer Division
QUOTIENT
MOD
Modulus
ABS
SIGN
Random Numbers
RAND
RANDBETWEEN
Turn off Automatic Recalculation
F9 to Recalculate
06. Trigonometry (5:10)
SIN, COS, TAN
PI
RADIANS
DEGREES
Calculate Height of a Building
07. Review (3:08)
|
|
|
Excel 2010 Expert 6 |
RunningTime: 1 Hour, 8 Minutes
00. Intro (6:14)
01. Financial Terms (4:49)
PV, FV, PMT, RATE, NPER
APR (Annual Percentage Rate)
Simple Interest
Compound Interest
02. Loan Calculator (9:39)
Calculate Mortgage Payment
PMT function
Compare Multiple Scenarios
03. Investment Calculator (5:03)
FV Future Value Function
Calculate Investment in 5 Years
04. Interest Rate Calculator (6:09)
What is your Return on Investment
RATE function
Calculate interest rate
05. Millionaire Calculator (6:01)
NPER Function
How many years until goal reached
06. Initial Deposit Calculator (2:29)
PV Function
What was the initial deposit?
07. Home Value Calculator (4:04)
How much house can I afford
PV function
Optional FV and TYPE parameters
08. Loan Amortization (12:10)
Microsoft's Amortization Template
Create your own amortization table
IPMT, PPMT functions
Cumulative interest and principal paid
CUMPRINC, CUMIPMT
09. Credit Card Payments (6:08)
How many payments at minimum
How long to pay off card debt
What payment to pay off in 6 months?
NPER and PMT
10. Review (4:44)
|
|
|
Excel 2010 Expert 7 |
Running Time: 1 Hour, 5 Minutes00. Intro (5:55)
01. What is a PivotTable (4:58)
Why are they useful?
What you can do with them.
02. Our First PivotTable (6:07)
Create Data Table
Optional download from web site
Create PivotTable
Set Row Labels
Set Column Labels
Set Values
Change from Count to Sum
03. Editing Your PivotTable (8:28)
Changing Fields
Multiple Fields per Axis
Expanding / Collapsing Levels
Filter & Sort Data
Label Filter
Value Filter
Report Filter
"Pivoting" Your Table
04. PivotTable Options (12:42)
Multiple Sets of Values
PivotTable Pane Layout Mode
Defer Layout Update
PivotTable Options Menu
For Empty Cells Show
Group / Ungroup
Slicers
Select Multiple Items
Slicer Settings
Multiple Slicers
05. PivotTable Design (5:14)
Layout
Show Subtotals
Grand Totals
Report Layout
Blank Rows
Row & Column Headers
Banded Rows / Columns
PivotTable Styles
Double-Click to Create Data Subset
06. PivotCharts (4:51)
Creating a PivotChart
PivotChart Options
07. Grouping Data (11:47)
Group by Month
Table Better Than Range
Refresh
Change Data Source
Group by Year/Month
Subtotal Row
Custom Weekly Grouping
Group Ranges of Numbers
Group on Age Distribution
08. Review (4:37)
|
|
|
Excel 2010 Expert 8 |
|
Running Time: 1 Hour, 8 Minutes 00. Intro (5:13)
01. Protect Worksheets (11:15)
Create Loan Calculator Sheet
Review > Changes
Protect Sheet
Set Password
Locked v. Unlocked Cells
Set Allowed Actions
Hiding Cell Formulas
02. Protect Workbook (8:58)
Structure
Windows
Set Password
Protect With Password
Save As > Tools
Password to Open
Password to Modify
03. Advanced Copy Paste 1 (10:59)
Cut Copy Paste Review
Copy by Click & Drag
Paste Smart Tag Options
Paste Formulas
Transpose Axes
Paste Number Formatting
Keep Source Formatting
Keep Source Column Width
No Borders
Paste Values
Paste Link
Paste as Picture
Paste Special
Merge Conditional Formats
Mathematical Operations
Increase all prices 3%
04. Advanced Copy Paste 2 (6:10)
Office Clipboard
Collecting Items
Pasting Multiple Items
Clear All
Undocking the Clipboard Pane
Copying Formulas not Values
05. Fill & Series (6:03)
Left, Right, Up, Down
Fill Series
Linear Numbers
Dates
Weekdays
Trends
Step Value
Stop Value
Across Worksheets
Growth Factor
06. Data Entry Forms (15:07)
Adding Buttons to Quick Access Toolbar
Add/Remove Standard Buttons
Add Forms Button to QAT
Creating Data Form
Make Labels
Set up the Form
Add Data
Move Between Records
Leading Zero Problem
Calculated Columns Display Differently
Delete - No Undo!
Restore
Criteria
Simple Search
Inequalities
Wildcard Characters *, ?
07. Review (4:28)
|
|
|
Excel 2010 Expert 9 |
|
Running Time : 1 Hour, 5 Minutes 00. Intro (5:28)
01. Advanced Sorting 1 (7:31)
Custom Sort
My data has headers
Sort By
Sort On
Sort Order
Add Level
Delete Level
Copy Level
Sort by Color
Conditional Formatting
Icon Sets
Options
Case Sensitive
Sort Left to Right
Sort Top to Bottom
02. Advanced Sorting 2 (8:49)
Sort on Custom List
Sort on Birth Month
Create a Custom List
Sort by Roman Numerals
Alphanumeric Sorts
McDonald, Mc Donald
a1, a10, a11, a2, a3
03. Customizing the Interface (11:11)
Customizing Quick Access Toolbar
Adding buttons
Removing buttons
Change order of buttons
Insert separator
Choose commands from
Show QAT Below the Ribbon
Reset customizations
Import/export customizations
Custom toolbars for specific workbooks
Customizing the Ribbon
Main Tabs v. Tool Tabs
Removing groups
Adding your own custom tab
Adding groups
Adding commands
Hiding built-in tabs
04. Filtering Data 1 (5:58)
Download sample spreadsheet
Turn on filtering
Filter on one or more values
Filtering on multiple columns
Turn off filtering
Clearing the filter
Reapplying the filter
05. Filtering Data 2 (8:15)
AND and OR conditions
Text Filters
Numeric Filters
Date/Time Filters
Wildcard Characters
Top 10 Filters
Filter for blanks
Right-click to filter
06. Advanced Filters (13:15)
Create an Advanced Filter Row
List range
Criteria range
Copy to another location
Filter the list in place
Unique records only
AND across, OR down
Multiple rows, multiple columns
Set up nice formatted filter region
07. Review (4:18)
|
|
|
Excel 2010 Expert 10 |
00. Intro (5:33)
01. Custom Views (6:06)
Turn on filtering
Create a Custom View
Add Dropdown to Toolbar
Add View with Dropdown
02. SUBTOTAL Function (7:12)
SUM calculates ALL cells
SUBTOTAL function know filters
Select a subtotal type
SUM, AVERAGE, etc.
Just HIDING a row
Only works in columns, not rows
AutoSum uses SUBTOTAL for SUM only
03. Outline Subtotals (7:33)
Create a Subtotal
Expand Collapse Buttons
Multiple Subtotal Groups
04. Outlines (7:45)
Creating a Manual Outline
Group, Ungroup Buttons
Clear Outline
Auto Outline
05. Data Validation 1 (10:05)
Validation Rules
Text Length
Circle Invalid Data
Input Message
Error Alert
Stop, Warning, Information
06. Data Validation 2 (9:30)
Date Values
TODAY() function
Appts in 30 days
Range Formulas for Validation
Ignore First Row Error
Times
Whole Numbers
List
Dropdown List Values
Conditional Formatting
07. Data Validation 3 (8:43)
Custom Rules
ISTEXT()
AND()
MID()
LEN()
Exceeding a Budget
08. Data Validation 4 (8:46)
Cascading Lists
Pick a Car Make
Show Models for that Company
DGET Function
Lookup with Multiple Criteria
09. Review (4:36)
|
|
|
Excel Expert 11 |
00. Intro (4:37)
01. Video Time Conversion (27:39)
Separating Text Strings
FIND, LEFT, LEN, MID, RIGHT
Converting to Seconds
Adding Times
QUOTIENT, MOD, TEXT
Functions
How Many Hours in X Seconds
Force 2 Digit
Minute, Sec 00:00
02. XLOOKUP Part 1 (13:28)
Benefits
of XLOOKUP
Look up to the left or right
Return multiple
results
Vertical or horizontal lookups
Reverse search
(last to first)
Defaults to an exact match
Supports
wildcards (*, ?, ~)
Sspecify a value if not found
Return
entire columns or rows
Can work with arrays
Review of
VLOOKUP
Weekday Lookup
Absolute References F4
Still
works if you move ranges
Student Grades
Grade Lookup Table
List doesn't have to be in asc order
Exact Match Lookup
Approximate Lookup (Range)
Match Mode: Exact Match or Next
Smaller Item
03. XLOOKUP Part 2 (11:13)
Returning
multiple values
match_mode
Match Not Found: if_not_found
argument
Wilcard Searches * ? ~
search_mode
Binary
Searches - discussion
XMATCH
04. XLOOKUP Part 3
(16:48)
Two-way lookups (double lookup)
Clothes, Sizes,
Look up Price
Course, Grade, Look up Letter
BONUS:
Developer Tab
Combo Box
Input Range
Cell Link
05. Review (2:49)
|
|
|
Excel Developer 1 |
Scheduled for production soon. Be sure to
indicate your interest for more Excel training on our
Waiting List.
|
|
|
Excel 2007 |
|
Excel 2007 Basic 1 |
Running Time: 76 minutes
00. Intro (9:25)
01. Excel Interface 1 (5:14)
Starting Excel
The Ribbon Changes with Window Size
Title Bar
Maximize, Minimize, Restore Down
Close Button
Office
Button
02. Excel Interface 2 (6:09)
The Ribbon
Ribbon Tabs, Groups, Buttons
Help Popup Menus for
Each Command
Command Buttons
Drop-Down Menus
Command
Button Groups
Galleries
Dialog Box Launcher Buttons
03. Excel Interface 3 (4:31)
Quick Access Toolbar
Status Bar
Views Buttons
Zoom Controls
Scroll Bars
04. Excel Interface 4 (5:06)
Formula Bar
What is a Spreadsheet
Columns, Rows, Cells
Name
Box
Mouse Pointer
Sheet Tabs
05. Entering Data
(4:16)
Creating a sales summary sheet
Entering text in Excel
Typing
data into a cell
Select a Cell with Mouse
Select a Cell
with Keyboard
Move with ENTER, TAB
Move with Arrow Keys
Entering Data
06. Editing Data (5:04)
Editing data in a cell
Four methods for editing data
Overtyping your data
Replacing data in your cells
Editing
data using the Formula Bar
Editing a cell by double-clicking
Edit Mode with F2
Deleting data in a cell
Using UNDO in
Excel
Using REDO in Excel
07. Formatting 1 (5:10)
Home, Alignment
Formatting your text
Horizontal cell alignment
Align Right, Left, Center
A Cell Range defined
Cell Range
Notation (A1:B5)
Selecting cell rangess
Bold, Italics,
Underline
08. Formatting 2 (6:49)
Changing the Font
Live Preview
Change Font Size Dropdown
Type in a Font Size Manually
Increase Font Size
Decrease
Font Size
Resizing Columns
Changing Column Width
Resize
Column with Double-Click
Resizing Rows
Background Color /
Fill Color
Foreground Color / Font Color
09. Basic
Math 1 (6:36)
Moving to a different sheet tab
Using a "scratch" sheet
Adding
two cells together
How Formula Work in Excel
Auto
Recalculation of Formula
See Formula in Formula Bar
See
Result on the Sheet
Basic math operators
Addition
Subtraction
Multiplication
Division
Exponentiation
Using Constants
Clearing a range of cells
Selecting an
Entire Column
10. Basic Math 2 (4:38)
Adding Multiple Cells
Select to See Summary in Status Bar
Click to Add Cell to Formula
Order of Operations
Using
parentheses in math operations
Average a Group of Cells
11. Save, Load, Print (8:43)
Saving Your Workbook File
Save As a Different File
Open Recent
Documents
Open Any Document
Pinning Documents to the List
Create a New Blank Workbook
Print Preview
Print Your
Spreadsheet
12. Review (4:08)
|
|
|
Excel 2007 Basic 2 |
Running Time: 66 minutes
00. Intro
(6:57)
01. Cut Copy Paste (6:53)
The Clipboard
Copy, Paste
Cut, Paste
Formula References
Updated
Paste Multiple Times
02. Autofill
(5:36)
Autofill a Formula
Mistakes
Autofill
Handle
Move a Cell Value
03. Cell Formats 1
(6:48)
Text, Number
Missing Leading Zeros
Currency, Accounting
Short Date, Long Date
04.
Cell Formats 2 (4:16)
Fraction
Percentage
Increase Decimal
Decrease Decimal
Comma Style
Scientific Style
05. Rows & Columns (6:56)
Clear v. Delete
Selecting Multiple Columns
Delete Columns
Delete Rows
Right-Click Delete
Insert Column
Insert Row
Clear Contents
06. Sheets (5:17)
Insert New Worksheet
Delete Worksheet
Rename Sheet
Moving Sheets
Copying a Sheet
Sheet Tab Color
07. Sorting Data (5:44)
Sort A to Z
Dealing with Total Rows
Select Everything First
Sort on a
Different Column
Use TAB to Change Columns
08.
Conditional Formatting (5:52)
Highlight Cells Rules
Greater Than, Less Than
Above Average
Data Bars
Color
Scales
Icon Sets
09. Find & Replace (7:53)
Find Data
Find Next
Find All
Replace
Replace All
10. Review (3:56)
|
|
|
Excel 2007 Basic 3 |
Running Time: 67 minutes
00. Intro
(7:14)
01. Functions 1 (6:39)
What is a Function
The SUM Function
Benefits of Functions
02. Functions 2 (4:36)
AVERAGE Function
COUNT
MAX, MIN
Move a Block of Cells
03.
Functions 3 (6:56)
Practice with Functions
The
Key Cell Format Trick
Insert New Month of Sales
AutoFill
Multiple Cells
04. Styles & Themes (7:53)
Selecting Cell Styles
What's a #DIV/0! Error
Using Styles
to Format
Changing Themes
05. Page Layout
(7:37)
Margins
View, Page Layout
Print Preview
Orientation
Page Size
Set Print Area
Clear Print Area
Page Breaks
Background
Gridlines: View, Print
Headings:
View, Print
06. Illustrations 1 (9:07)
Insert a Picture
Move and Resize a Picture
Rotate
Picture
Picture Tools, Format Tab
Brightness, Contrast
Reset Picture
Compress Pictures
Delete a Picture
07. Illustrations 2 (7:37)
Insert Shapes
Rectangle
Drawing Tools, Format Tab
Shape Styles
How
Themes Effect Shape Styles
Shape Outline
Shape Effects
Add Text
Format Text
Quick Mini Toolbar Popup
WordArt
Styles
Inserting an Arrow
08. Illustrations 3
(5:50)
Insert SmartArt
Basic Process
Format
SmartArt Options
Insert WordArt
WordArt Options
09. Review (3:47)
|
|
|
Excel 2007 Basic 4 |
Running Time: 64 Minutes
00. Introduction (7:48)
01. Charts 1 (7:53)
What is a
Chart
Parts of a Chart
Insert a Column Chart
02. Charts 2 (8:08)
Add Data to an Existing
Chart
Line Charts
Multiple Series in a Chart
Chart
Styles & Themes
03. Charts 3 (11:00)
Selecting Non-Contiguous Data
3D Pie Charts
Move a Chart
to a Different Sheet
Format Objects Inside Charts
Chart
Layouts
Change Chart Type
3D Column Charts
04. Charts 4 (7:03)
Switch Row/Column
Current
Selection Box
Format Selection
Reset to Match Style
Insert Shapes
Insert Text Box
Labels
Chart Title
Axis Titles
Legend
Data Labels
Data Table
05. Charts 5 (8:05)
Copy Excel Charts into
Word
Creating a Summary Sheet / Dashboard
Separate a Slice
of Pie
Create a Leader Line
06. Tables (9:45)
Creating a Table
Why Use a Table
Table Styles
Table Formulas are Uniform
New Rows Automatically Copy
Formulas
AutoFilter in Tables
Total Row
Insert New Row
07. Review (5:02)
|
|
|
Excel 2007 Basic 5 |
Running Time: 63 Minutes
00. Introduction (8:07)
01. Insert Text 1 (7:47)
Insert
> Text Group
Insert Hyperlink
Web Page Hyperlinks
Email
Hyperlinks
Insert Text Box
Insert WordArt
Insert
Signature Line
02. Insert Text 2 (3:54)
Insert Object
Insert Symbol
03.
Header & Footer (7:59)
Insert Header & Footer
Three Sections
Header & Footer Elements
Entering Text in
Header
&[Page] Code for Page Number
Page Footer
Date
Code
Header & Footer Templates
Go To Footer, Header
04. View Window (6:43)
Working with
Large Sheets
Create a Large Sheet of Data
Freeze Panes
Freeze Top Row
Freeze First column
Split the Window
05. Borders (4:26)
Printing Gridlines
Changing Borders
Top, Left, Right, Bottom
Inside, Outside
Borders
Thick Borders
06. Misc Format Tricks
(5:28)
Merge and Center
Merge Across
Merge
Cells
Unmerge Cells
Cell Orientation
Format Painter
Double-Click Trick
07. Comments (3:42)
Insert Comment
Edit Comment
Delete Comment
Show, Hide Comments
Previous, Next Comment
08.
Research (5:20)
Spell Check
Thesaurus
Getting
Stock Quotes
Translate
09. Office Online
Templates (5:03)
How to Download Templates
Calendar Template
Invoice Template
Home Mortgage Template
10. Review (4:49)
|
|
|
Excel 2007 Expert 1 |
The differences between Excel 2007 and 2010 are minor. To
continue learning Excel as an Expert user, please see our Excel
2010 lessons (above). You should have no problems following
along with 2007.
|
|
|
Excel 2000-2003 |
|
Excel 101 |
1. INTRODUCTION
Objectives
Pre-Requisites
Important concepts
2. PARTS OF THE EXCEL SCREEN
Starting Excel
Parts of the screen
Title Bar
Workbook name
Spreadsheets Defined
Maximize
Minimize
Close
Two sets of max/min/close buttons
Menubar
Toolbars
Standard toolbar
Formatting toolbar
Formula Bar
The Spreadsheet Window
Columns v. Rows
Names of Columns and Rows
Column and Row Headers
Cells
Names of Cells
The Cell Name Box
The Mouse Pointer in Excel
Scroll Bars and their use in Excel
The Task Pane
Sheet Tabs
Status Bar
Adjustments to Excel for class
Closing the Task Pane
Moving Toolbars Around
Floating v. Docked Toolbars
3. ENTERING DATA
Creating a sales summary sheet
Entering text in Excel
Typing data into a cell
Entering a full column of data
Pressing ENTER, TAB, or ARROW KEYS
Using the Mouse to move around
Using the keyboard to move around
4. EDITING DATA
Editing data in a cell
Overtyping your data
Replacing data in your cells
Editing data using the Formula Bar
Editing a cell by double-clicking
Deleting data in a cell
Using UNDO in Excel
Using REDO in Excel
5. FORMATTING OUR SHEET
A Cell Range defined
Cell Range Notation (A1:B5)
Highlighting (selecting) ranges of cells
Formatting your text
Horizontal cell alignment
Align Right, Left, Center
Bold, Italics, Underline
Changing Fonts, Font Size
Resizing Columns
Changing Column Width
Resizing Rows
Resizing Columns Automatically with Double-Click
Background Color / Fill Color
Foreground Color / Font Color
6. BASIC MATH
Moving to a different sheet tab
Using a "scratch" sheet
Basic math operators
Adding two cells together
Demonstrate auto-recalculation
Result on sheet, formula in formula bar
Addition
Subtraction
Multiplication
Division
Exponentiation
Clearing a range of cells
Adding a range of cells
Taking the average of a range of cells
Order of Operations
Using parentheses in math operations
7. FUNCTIONS, PART ONE
Functions defined
What is a function
The SUM function
The AVERAGE function
Using the mouse with functions
Selecting function ranges with the mouse
The MAX, MIN, and COUNT functions
8. FUNCTIONS, PART TWO
Adding functions to our sales summary sheet
Totalling each month's sales
Totalling each sales rep's sales
Calculating a grand total
Calculating sums for columns and rows
Practical application for our student gradebook
9. CUT, COPY, PASTE, AUTOFILL
Copying and pasting a formula
Excel adjusting formula automatically
Using AutoFill to copy a formula
The AutoFill Handle
Common mistakes while trying to AutoFill
Moving cells by click and drag
AutoFill Series: Months
10. SAVING, LOADING, PRINTING
Saving your workbook
Saving with the floppy disk (save) button
Saving to your "My Documents" folder
Entering a filename
Closing Excel
Reloading your workbook
Using the Task Pane
Using the File Menu Option
Using the Windows Documents options
Printing your spreadsheet
File, Print
Print Options
11. REVIEW
Review topics
|
|
|
Excel 102 |
1.
INTRODUCTION
Objectives
Pre-Requisites
2. USING AUTOFILL, PART ONE
Using AutoFill with series
Months of the year
Days of the week
Sequential dates
Number series
Non-seqential dates
Creating a calendar in Excel
Using the ZOOM drop-down
Zooming in and out
Zoom to selection
AutoFilling backwards (to the left)
###### showing up in your cells
Double-click to auto-resize a column
Highlighting multiple columns
Resizing multiple columns to the same width
Double-click resize multiple columns
Manually entering a column width
3. USING AUTOFILL, PART TWO
Right Click, Format Cells
Changing the format of a date
Two v. Four Digit Years
Using the Format Painter
Format Painter double-click trick
Sticky Format Painter
AutoFilling multiple columns/rows
Inserting a blank row or column
Forcing Excel to format text
Single-quote text formatting
Losing leading zeros (ZIP codes, SSN, etc.)
Align left
Merge and Center Cells
Center Across Columns
Print Preview
Cell Borders
Turning borders on and off
Preset borders
All Borders
No Borders
Thick Outside Border
Resizing rows to the same hight
Changing vertical cell alignment
Top, Center, Bottom vertical alignment
4. SORTING DATA
Setting up a new sheet
Green warning markers
Ignoring Errors
Numbers stored as text
Percent Style
Dealing with decimals and fractions
Increase Decimal
Decrease Decimal
Currency Style
How Excel rounds decimals
Using the AutoSum button
A warning about the AutoSum feature!
Sort Ascending
Sort Descending
Selecting the right cells to sort
Sort Warning
Expand the selection
Continue with the current selection
Accidentally scrambling your data
Recognizing the Key Cell to sort by
Sorting by the left-most cell in a range
Sorting by the right-most cell in a range
Sorting by some column in the middle of your range
Moving the Key Cell with the TAB key
Using Data > Sort
Sorting by up to three fields
The Sort Dialog box
5. CHARTING, PART ONE
Selecting which cells to chart
Selecting non-contiguous ranges with the CTRL key
Getting rid of Clippy, the Office Assistant
Using the Chart Wizard
Selecting a Chart Type and Chart Subtype
Verifying your data range
Entering a chart title
Where to place your chart
Chart as a new sheet
Chart as an object in an existing sheet
Using Format Data Series
Changing the color of your chart columns
Changing the background colors with Format Plot Area
Using Format Axis
6. CHARTING, PART TWO
Creating a 3D Column Chart
Press and Hold to View Sample
Entering a Category (X) Axis Title
Entering a Series (Y) Axis Title
Entering a Value (Z) Axis Title
The Axes tab - turning axis labels on/off
Major and Minor Gridlines
Showing or hiding the Legend
Legend Placement
Using Data Labels
Show Data Table
Show Legend Keys
Hiding the Chart Toolbar
Format Data Point
Changing the format of one specific column
Format Walls
Using a Gradient fill
Fill Effects
Textures
Patterns
Using 3-D View to rotate your chart
Changing elevation, perspective, rotation
7. CHARTING, PART THREE
Creating a pie chart
Category name, percentage data labels
Separating a piece of the pie
Separating all of the pie pieces
3-D View for pie charts
Formatting, moving data labels
Leader lines
8. REVIEW
Review topics
|
|
|
Excel 103 |
0.
INTRODUCTION
Objectives
Pre-Requisites
1. SETTING UP OUR SHEET
Building an Income & Expense Report Sheet
Setting up Income & Expense Categories
Moving a range of cells with the mouse
Formatting our sheet
Inserting blank rows
AutoFill Review
2. WORKING WITH LARGE SHEETS
Freezing panes
Window, Freeze Panes
Window, Unfreeze Panes
Splitting the Screen
Window, Split
Window, Remove Split
Window, New Window
Insert Comments
Edit Comment
Delete Comment
Hiding Columns and Rows
Unhiding Columns and Rows
3. PAGE SETUP, PART ONE
Print Preview
Using the Magnifying Glass
Zooming in and out
Closing Print Preview
File, Page Setup
Page Orientation
Portrait v. Landscape
Scaling
Adjust to % Normal Size
Fit to pages
Print quality (resolution)
First Page Number
Additional printer options
Page Margins
Top, Bottom, Left, Right Margins
Header & Footer Margin Settings
Center on Page Horizontally, Vertically
Adjusting margins visually
4. PAGE SETUP, PART TWO
Headers & Footers
Using the stock page headers
Page header previews
Creating a custom header and footer
Left, center, right sections
Using the insert codes for headers/footers
Page numbering
Number of pages
Putting an ampersand in your headers
Inserting the date and time
The path and file of your workbook
Inserting a picture in your sheet
Changing text format of headers/footers
5. PAGE SETUP, PART THREE
Working with the Page Setup, Sheet tab
Specifying a Print Area
Using the range popup boxes
Rows to repeat at top
Columns to repeat at left
Showing gridlines
Black and white printing only
Draft quality
Showing row and column headings
Showing comments, options
Forcing comments to stay visible on sheet
Displaying Cell Errors
Page Order (numbering)
6. WORKING WITH MULTIPLE SHEETS
Viewing the page breaks
View, Page Break Preview
Moving the page break lines
View, Normal
Changing the name of your sheet tabs
Creating a copy of a sheet
Changing the color of a sheet tab
Using the keyboard to move between sheets
Deleting a sheet tab
Inserting a blank new worksheet
Moving a sheet tab with the mouse
Selecting multiple sheet tabs
Moving multiple sheet tabs
Format, Sheet, Background
Creating a background picture for the sheet
7. REVIEW
Review topics
|
|
|
Excel 104 |
0.
INTRODUCTION
Objectives
Pre-Requisites
1. FORMAT CELLS: NUMBER FORMATS
Setting up a sales log sheet
Right Click, Format Cells
The Number Tab
General Number Format
Number of decimal places
Use 1000 Separator
Formats for negative numbers
Currency Style
Accounting Style
The Date Formats
The Time Formats
Percentage Formats
Fractions Format
Precisions with fractions
Scientific notation format
Plain Text Formats
Special Formats
Custom Formats, a brief discussion on
Using Format Painter on a whole column
Percent Style Button
Calculating sales tax by a tax rate
Math for calculating totals, subtotals
2. FORMAT CELLS: ALIGNMENTS
Format Cells, Alignment Tab
Setting vertical alignment
Top Vertical Alignment
Wrap Text to keep text in a cell
Text Orientation
Displaying text at an angle
Insert Cut Cells to move a whole row
Shrink To Fit text
Merge and Center
Center Across Columns
Merge Cells
3. FORMAT CELLS: FONTS
Format Cells, Font Tab
Strikethrough, Superscript, Subscript
Borders Tab
None, outline, inside borders
Manually turning borders on/off
Diagonal (strike) borders
Border styles, colors, thickness
Patterns tab
Setting a pattern and color
Cell protection, brief discussion
4. CONDITIONAL FORMATTING
Format, Conditional Formatting
Formatting a cell based on its value
Example: Highlighting sales over $300
Adding more conditions
Example: Highlighting sales under $100
Formatting based on a cell value
5. SPELLING, FIND, REPLACE, AUTOFORMAT
Tools, Spelling to spell check your sheet
Ignore once
Ignore all
Add to dictionary
Change
Change All
Edit, Replace
Find and Replace Dialog
Finding based on format
Match case
Match entire cell contents
Find Within (workbook, sheet, range)
Search By (rows, columns)
Look in (formulas)
Format, AutoFormat
Pre-designed AutoFormats
6. FINANCIAL CALCULATOR
Creating a loan calculator
Entering the value of the house, down payment
Calculating the financed amount
Entering the interest rate, period
Learning the PMT function
Using PMT to calculate the monthly payment
Calculating number of payments
Figuring out total interest paid
Currency Style button
Comparison shopping: compare two loans
Changing interest rates, periods, down payments
Calculating your savings
Locking specific cells to prevent tampering
Turning on sheet protection
Format Cells, Protection tab
Locked cells
Tools, Protection, Protect Sheet
Unprotect Sheet
7. REVIEW
Review Topics
|
|
|
Excel 201 |
0.
INTRODUCTION
Objectives
Pre-Requisites
1. REVIEW AND SHEET SETUP
Setting up a student gradebook
2. THE IF FUNCTION, PART ONE
How the IF function works
Learning about logical tests
Specifying true and false values for IF
Using text values with IF
3. THE IF FUNCTION, PART TWO
Setting up a sales tax sheet for IF
Logical test with text values
Charging tax only to customers from NY
4. RELATIVE V. ABSOLUTE REFERENCES, PART ONE
Marking a student "pass" or "fail"
Move passing grade outside formulas
Making a cell reference absolute
absolute references v. relative references
AutoFill with absolute references
Move sales tax outside formulas
5. RELATIVE V. ABSOLUTE REFERENCES, PART TWO
Making passing grade based on class average
Grading on a "curve"
Creating a new quarterly sales summary sheet
Showing percent of sales for each quarter
Divide by Zero Error (#DIV/0!)
6. VALUES FROM OTHER SHEETS
Moving all of our external data to a new sheet
Refering to a cell on a different sheet
Cutting and pasting a link to a cell
Right click, paste special
Paste Special dialog box
Pasting the value of a cell (Paste Values)
#NAME? error
Referring to sheet names with spaces in them
7. COPYING DATA TO MICROSOFT WORD
Copying Excel sheet data into Word
Pasting Excel Data as a Word Table
Edit, Paste Special
Microsoft Excel Worksheet Object
Paste Link
Linking v. Embedding
Linking data back to the original Word document
8. TIPS AND TRICKS
Insert Cut Cells
Moving a whole row
Shift cells right
Shift cells down
Copying a sheet with the CTRL-drag trick
Manually drawing borders
View, Toolbars, Borders
Borders Toolbar
Pencil tool
Eraser tool
Insert Comments
Show Comments
Hide Comments
9. REVIEW
Review topics
|
|
|
Excel 202 |
0.
INTRODUCTION
Objectives
Pre-Requisites
1. REVIEW AND SHEET SETUP
Reconstruct Student Gradebook
Short review of important concepts
2. NAMED CELLS
Defining a named range
Insert, Name, Define
Refers To Box
Using the Name Box
Naming the PassingGrade cell
Jumping to a named cell
Using the named cell in functions
Collapse Dialog
Expand Dialog
Valid names for cells
3. NAMED RANGES, PART ONE
Defining a named range
Pasting a named cell or range
Insert, Name, Paste
Using named ranges in formulas
Jumping to named ranges
Range Intersections
4. NAMED RANGES, PART TWO
Deleting a named range or cell
Editing a named range or cell
Pasting a list of your named ranges
Paste List
Creating names based on your headers
Insert, Name, Create
Applying names to existing ranges
Insert, Name, Apply
5. NESTED IF FUNCTIONS
What is a Nested IF function
Using the COUNT function
Checking multiple conditions
Giving our students an Incomplete
Giving a letter grade using nested IFs
6. VLOOKUP, PART ONE
Creating a subtable (lookup table)
Grade values lookup table
Using VLOOKUP to return the letter grade
Using VLOOKUP to return a commission rate
7. VLOOKUP, PART TWO
Expanding our lookup table to three columns
Using VLOOKUP to return multiple columns
Creating a basic invoice using Excel
Using HLOOKUP to calculate a discount
Using the Function Wizard for HLOOKUP
8. REVIEW
Review Topics
|
|
|
Excel 220 |
0.
INTRODUCTION
Objectives
Pre-Requisites
1. DATA LISTS
Data Lists Defined
Header Row
Fields
Records
Database Terminology
Pick From List
2. FORMATTING DATA LISTS
Formatting the Header Row
Freezing Panes (review)
Calculate Time Difference (in hours)
Absolute References (review)
Conditional Formatting (review)
Spreadsheet v. Database (when to switch)
3. DATA ENTRY FORMS
Data entry forms defined
Opening the data form
Moving between fields
Entering data
New records
Find Previous
Find Next
Deleting records
You can't undo deletes
Calculated fields in data forms
Searching for records
Search Criteria
Using wildcard characters * and ?
Wildcard rules
Restore button
4. ADVANCED SORTING
The sum preview on the status bar
Changing from sum to a different function
Count v. Count Nums
Sorting month names in month order (not alpha)
First key sort order
Sort options
Custom Lists (using in sorts)
Sorting on more than 3 fields
Restoring a list's original sort order
Alphanumeric sorting rules
5. CUSTOMIZING TOOLBARS
Toolbars and commands defined
Customizing built-in toolbars
Reviewing the command button options
Moving buttons around
Removing buttons from toolbars
Copying a button from one toolbar to another
Resetting a toolbar to its default settings
Changing a button from icon to text
Creating a New Toolbar
Attaching a toolbar to a workbook (to share it)
Change Button Image
Creating a custom button image
Button Editor
Edit Button Image
Copying ANY image onto a button
Changing the tooltip text
Toolbars Options tab
Toolbar Quick-Add Buttons
Toolbar buttons with dual functions
How to backup your custom toolbars
8. REVIEW
Review Topics
|
|
|
Excel 221 |
0.
INTRODUCTION
Objectives
Pre-Requisites
LESSON 1. FILTERING DATA
Data > Filter > AutoFilter
Filtering by a single item
Filtering by multiple items
Showing (All) records
Showing (Top 10...) results
Top/Bottom X Items/Percent
Duplicate values
Turning off AutoFilter
Adding AutoFilter and Show All to toolbar
1000 Item Limit
LESSON 2. CUSTOM FILTERS
(Custom...) AutoFilter
One item, equals
AND and OR conditions
End With
Inequalities (greater/less than)
Blank option
(Blanks) and (NonBlanks)
LESSON 3. ADVANCED FILTERING
Boolean operators
Creating custom advanced filter fields
Data > Filter > Advanced Filter
List Range
Criteria Range
Adding Advanced Filter to Toolbar
Creating a custom advanced Criteria Row
Filtering in place
Creating a named range for our DataList
Criteria named range
AND across, OR down
LESSON 4. SENDING LATE NOTICES (PROJECT)
Creating a customer list
Show records with missing data
Show customers over 30 days late
Copying filtered records
Showing non-blank records with Advanced Filters
Showing blank records with Advanced Filters
Showing customers in the 14xxx ZIP code
Wildcard Filter Characters
Showing customers with "gate" in address
Copying filtered records to a new sheet
Microsoft Word Mail Merge Wizard
Creating Mailing Labels
Configuring your Address Block
Matching Fields
LESSON 5. CALCULATED FILTERS, CUSTOM VIEWS
Calculating the average of all sales
Filtering sales above/below average
Calculating average of an entire column (D:D)
Custom Views
Creating a filtered Custom View
Switching between Custom Views
Placing Custom Views on your toolbar
LESSON 6. SUBTOTALS
Using the SUBTOTAL function
Why use SUBTOTAL instead of SUM
What the function_num means
List of available functions for SUBTOTAL
Using the Function Wizard with SUBTOTAL
Using the AutoSum button with SUBTOTAL
How AVERAGE doesn't work with filters
Using SUBTOTAL for averages
Creating automatic Subtotals
Data > Subtotals
Grouping and Outlining - brief discussion
Calculated Subtotals for each group
Removing subtotals
Sorting the list properly
Sorting to remove subtotals
7. REVIEW
Review Topics
|
|
|
Excel 222 |
INTRODUCTION
Objectives
Pre-Requisites
LESSON 1. STYLES
Format > Style
Style Dialog
Built-In Excel Styles
Currency Style
Modifying Styles
Style Patterns
Creating your own Styles
Changing styles throughout your workbook
Adding the Style dropdown to your toolbar
Style Borders
Merging styles from different workbooks
LESSON 2. GROUPING AND OUTLINING
Traditional outline formats
Sales & expenses
Tracking multiple stores
Data > Group and Outline > Auto Outline
Grouping and Outline Levels
Collapse and Expand
Data > Group and Outline > Clear Outline
Horizontal outlines
Grouping months into quarters
Data > Group and Outline > Settings
Summary rows below detail
Summary columns to right of detail
Automatic styles
RowLevel_1 and RowLevel_2
ColLevel_1 and ColLevel_2
LESSON 3. MANUAL GROUPING
Data > Sort review
Data > Group and Outline > Group
Creating a manual group
Creating a sorting and grouping toolbar
Manually creating subtotals
Hide detail and show detail
Review of creating a custom view
LESSON 4. GROUPING TIPS AND TRICKS
Selecting only visible items when you collapse a group
Selecting Visible Cells Only
Using the Go To command
Using CTRL-8
Creating a HeaderGroupStyle
Changing your style and watching the whole sheet change
LESSON 5. DATA VALIDATION
Correcting user input
Data > Validation
Checking text length
Custom Input Message
Custom Error Alerts
Stop, Warning, or Information
Dealing with dates in a range
Number types
Picking from a list
Drop down menu of options
Hiding your source list
Putting your list in the Settings dialog
Formula Auditing Toolbar - a quick look
Circle Invalid Data
Clear Validation Circles
Conditional Formatting - review
Custom formulas for data validation
ISTEXT() function
Ignore Blank
LEFT() function
REVIEW
Review Topics
|
|
|
Excel 223 |
- What is a PivotTable
- Building a PivotTable
- Manipulating PivotTable Fields
- PivotTable rules for data
- Field Settings
- Pivoting Your Data
- Inner & Outer Fields
- Example: Sales Log
- Example: Employee List
- Refreshing PivotTable Data
- Filtering Data
- Using Page Fields to Filter Data
- Sorting PivotTable Data
|
|
|
Excel 224 |
- Grouping Data
- Drill to Detail
- Grouping Dates Together (months, quarters, etc.)
- Grouping Numeric Values
- Subtotals
- Subdetails
- Advanced Field Settings
- Formatting PivotTables
- Copying PivotTable Data
- PivotTable Options
- Calculated Fields
- Calculated Items
- Base Fields & Items
- Running Totals
- Showing Data as a Percentage of Previous Data
- Example: Employee Costs using IF, VLOOKUP, and a
PivotTable
|
|
|
Excel 230 |
Lesson 1. Multisheet Ranges
Selecting Multisheet (3D) Ranges
Tracking sales for multiple years
Formatting multiple sheets with one selection
Creating a summary sheet
Lesson 2. Go To > Special
Comments, Formulas, Blanks,
Current Region, Objects, Row & Column Differences
Precedents & Dependents, Last Cell
Visible Cells Only, Conditional Formats,
Data Validation
Lesson 3. Advanced Find & Replace
Find, Replace Options
Wildcards: *, ?
using tilde ~ to find a wildcard character
Find Format > Choose Format From Cell
Find in Sheet, Workbook
Find All
Selecting all found cells
Find in Formulas, Values, Comments
Match Case
Match Entire cell
Careful when doing Replace All, esp w numbers!
Lesson 4. File Options
AutoRecover
AutoRecover Options
Saving Your AutoRecover file
File Save Options
Creating Automatic Backups
Password to Open, Modify
Encryption
Workbook Summary
Summary Fields (Subject, Title, etc.)
Custom Fields - creating a workbook database
Item, Type, Data
Lesson 5. Templates
Understanding templates
Modifying the default Excel template
Setting up custom workbook templates
Book.xlt, Sheet.xlt
XLStart Folder
|
|
|
Excel 231 |
LESSON 1. ERROR TYPES
Types of Errors
Syntax Errors
Logical Errors
Reference Errors
Circular References
Mismatched Parentheses
LESSON 2. SPECIFIC ERRORS
#DIV/0!
Handle DIV/0 error with IF function
ISERROR() function
#NAME?
#REF!
#VALUE!
#NUM!
SQRT() Function for square root
Exponents too large 10^308
#NULL!
#N/A
=NA() Function
LESSON 3. ERRORS WITHOUT MESSAGES
Logical Errors
Relative v. Absolute References
Rounding errors
Actual v. displayed values
ROUND() function
Force "Precision as displayed"
Floating Point Errors
Numbers close to zero not rounding right
LESSON 4. LOGICAL FUNCTIONS
TRUE
FALSE
NOT
AND
OR
=IF(AND(X,Y),TRUE,FALSE)
LESSON 5. AUDITING
Tracking Errors
Auditing Toolbar
Trace Dependents
Trace Precedents
Dependents on another sheet
Goto jumping to those dependents
Trace Errors
Blue and Red Arrows
Evaluate Formula
Evaluate, Step In, Step Out
Circle Invalid Data
Watch Window
LESSON 6. HIDING ERRORS
Automatic Error Checking
Hiding Cell Errors from Printouts
LESSON 7. CIRCULAR REFERENCES
Circular Iterations
When you WANT a Circular Reference
Simulating a DO LOOP
LESSON 8. TIPS & TRICKS
Transpose Data
Edit > Fill
Series Trend
Increasing X Weekdays
AutoFill with RIGHT Mouse Button
|
|
|
Upgrading to Excel 2007 Seminar |
00. Intro (6:29)
01. The Ribbon 1 (8:03)
Ribbon
Tabs
Ribbon Tab Groups
Simple Command Buttons
Complex
Command Buttons
Drop-Down Menus
Dialog Box Launcher
Buttons
On Demand Menu Tabs
Minimize the Ribbon
02. The Ribbon 2 (7:37)
Resize Formula Bar
Keyboard
Shortcuts
New ALT-key Combinations
ALT-arrow keys
Live
Preview
Mini Toolbar Popup Menu
03. Quick Access
Toolbar (7:35)
Customizing
Office > Options >
Customize
Adding Commands
Adding a Separator
Add for
All Books
Add to This Book
Show Above / Below the Ribbon
Right-Click, Add to
Commands Not in the Ribbon
04.
Upgrades & Changes (6:16)
Get the Latest Updates
Upgrades and Changes
Row, Cell, Size Increases
Other
Changes
05. Office Button 1 (9:22)
Save, Load,
Print, etc.
Save in Other Formats
New File Formats
XLSX, XLSM, XLTX, XLTM
Backward Compatibility
Excel 2007
Viewer
Other File Formats
06. Office Button 2
(12:32)
Pin Workbooks to Recent List
Print vs. Quick
Print
Print Preview
Page Layout View
Prepare
Document Properties
Inspect Document
Encrypt Document
Digital Signatures
Mark as Final
Compatibility Checker
07. Excel Options (14:33)
Show Mini Toolbar
Enable
Live Preview
Show Developer Tab
Edit Custom Lists
New
Workbook settings
Font, Number of Sheets
Change Language
Settings
R1C1 Reference Style
Other Formula Options
Error Checking Options
AutoCorrect Options
Spelling
Options
Default File Format
AutoRecover
Advanced
Options
Trust Center Settings
Macro Settings
Resources
Get Updates
About Excel
08. Home Tab 1 (11:02)
Home Tab
Clipboard Group
Upgrades to Paste Options
Draw Borders
New Color Options
Orientation Button
New
Format Number Dropdown
Cells, Insert, Delete
Format Cells
on Ribbon
New AutoSum Button
09. Home Tab 2 (13:52)
Styles
Conditional Formatting
10. Home Tab 3
(13:23)
Tables
Table Styles
How Tables Work
Total Row
Banded Columns / Rows
Table Autofilters
Sort
& Filter
Sort By Color
11. Charts (14:58)
Inserting a Chart
No More Chart Wizard
Chart Types
Switch Row/Column
Quick Layout
Chart Styles
Move Chart
Layout Formatting
12. PivotTables (14:02)
Creating PivotTables
New / Changed Features
Adding Fields
Rows Columns
Formatting
Grouping
Sorting
PivotChart
Conditional Formatting
13. Illustrations (10:51)
Pictures
Clipart
Shapes
Smart Art
14.
Insert Tab Misc (5:51)
Text Box
Header & Footer
WordArt
Signature Line
Object
Symbol
15.
Themes (6:45)
Color Schemes
Font Groups
Effects
16. Page Layout (10:57)
Margins
Orientation
Page Size
Print Area
Page Breaks
Background
Print
Titles
Scale to Fit
Gridlines
Headings
Arrange
Bring to Front
Send to Back
Selection Pane
Align,
Group, Rotate
17. Formulas (12:37)
Insert
Function
Search For Function Name
Function Libraries
New Functions
IFERROR
AVERAGEIF
AVERAGEIFS
SUMIFS
COUNTIFS
18. Get External Data (9:49)
From
Access Database
From a Web Site
Automatic Refresh
19. Data, Review (4:32)
Remove Duplicates
Review
Tab
20. Miscellaneous (11:45)
Templates
Microsoft Office Online
Customize Status Bar
Zoom Slider
Find & Select New Features
Name Manager
Create from Range
Range Intersections
21. Review (5:02) |
|
|
|
|
|
|
| |
|
|
