Excel Expert Outlines
| 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 Minutes
00. 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)
|
|
|
