Excel 2010-2019
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
 
Home > Courses > Excel > Expert Outlines
 
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)
 

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

9/11/2021Circular References
8/25/2021Stock History
8/23/2021Concatenation
8/17/2021Flash Fill
8/5/2021Stock Portfolio
8/4/2021Stock Portfolio
6/23/2021Import Data
5/14/2021Quick Queries #4
4/4/2021Text to Columns
3/15/2021VLOOKUP in Access
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Page Tag: outline excel 2 1  PermaLink