Free Microsoft Excel Advanced Training
Microsoft Excel 2003 | 2007 | 2010
John Donovan, consultant and instructor at
www.TeachMSOffice.com has an innate
ability to take very complex Microsoft Excel topics and make them easy and
simple to understand. John has been kind enough to allow me to post links to his
YouTube videos here at teachanolddognewtricks.com for my students.
If you like John’s teaching style please consider subscribing to his training at
www.TeachMSoffice.com.
In addition, John does Excel consulting and development.
Intermediate and Advanced Microsoft Excel 2007 | 2010
Working With Dates in Microsoft Excel
Add or Subtract Days from a Date in Excel
Add or Subtract Months from a Date in Excel
Add or Subtract Years from a Date in Excel
Add Days Months or Years to a Date in Excel
Excel Tips 28 - Display The Current Time in Excel Down to the Second
Microsoft Excel Charts & Graphs
Chart Wizard in Excel - Make your first Graph or Chart
Add or Remove Gridlines from a Chart in Excel
Format Data Series and Data Points in Excel - Walkthrough
Format the Chart Area of a Chart in Excel - Walkthrough
Format the Plot Area of a chart in Excel
X & Y Axes Formatting in Excel - Colors, Fonts, Reverse order
Add Effects to Text within a Chart in Excel
Add a Title to a Chart in Excel
Add a Legend to a Chart in Excel
Add a Trendline to a Chart in Excel
Add a Hovering Effect to a Chart in Excel
How to Graph/Chart Hidden Data in Excel
Thermometer Charts/Graphs in Excel. How to create one and edit it.Make
a Histogram Chart in Excel Add Y Error Bars to Charts and Graphs in
Excel
Add Trendlines to Charts and Graphs in Excel - Includes Forecast
Future Data (Regular & Comparative) - with a Trendline
Microsoft Excel VBA & Macros
Add Multiple Sheets to a Workbook at Once Using VBA
Add Labels to Chart Data in Excel
Add Elements to a Dynamic Array Variable in VBA Using a Loop
Add Elements to a Dynamic Array Variable in VBA
Add Bold Italics Underline and Strikethrough to Cells using VBA
Get the Number of Items in an Array Variable in VBA and Macros in
Excel
Create a Static Array Variable in VBA and Macros in Excel
Add Elements to a Dynamic Array Variable in VBA Using a Loop -
Macros in Excel
Add Elements to a Dynamic Array Variable in VBA and Macros in Excel
Store Values in an Array in Excel VBA and Macros
Create a Dynamic Array in Excel VBA Macros
Do While Loop in VBA and Macros and Excel
While Loop in VBA - Macros in Excel - Visual Basic
Assign a Macro to a Button, Check box, or any object in Microsoft
Excel
Insert Macros into an Excel Workbook or File and Delete Macros from
Excel
Edit Macros in Excel - Make Dynamic Cell References (1 of 2)
Edit Macros in Excel - Make Dynamic Cell References (2 of 2)
Excel Macro VBA Tip 1 - How to Write Your Very First Macro in
Microsoft Excel
Excel Macro VBA Tip 2 - Using The Range Property to Select Cells in
Excel
Excel Macro VBA Tip 3 - Using The Range Property Macros to Select
Columns and Rows
Excel Macro VBA Tip 4 - Find Cells in Excel with Macros using the
Find Method
Excel Macro VBA Tip 5 - Learn How to Input Values, Text, Numbers,
and Formulas into Cells
Excel Macro VBA Tip 6 - IF Then Statement in Excel Macros and VBA
Excel Macro VBA Tip 7 - Check if a Cell in Excel is Empty Using
Macros in Excel
Excel Macro VBA Tip 8 - Hide UnHide Rows and Columns in Excel with a
Macro
Excel Macro VBA Tip 9 - Enter Formulas in Excel with a Macro
Excel Macro VBA Tip 10 - Enter Array Formulas into Cells in Excel
with a Macro
Excel Macro VBA Tip 11 - Assign Excel Cell Contents to Variables in
Excel Macros
Excel Macro VBA Tip 12 - Clear Data & Cell Contents with an Excel
Macro
Excel Macro VBA Tip 13 - Select cells with the Cells Property in
Excel Macros
Excel Macro VBA Tip - 14 Using Variables with the Cells Property in
Excel Macros
Excel Macro VBA Tip 15 - Combine the Range and Cells Property in
Excel Macros
Excel Macro VBA Tip 16 - Using Offset in Excel Macros and VBA
Excel Macro VBA Tip 17 - Find Last Row and Last Column with VBA
Excel Macro VBA Tip 18 - Easily Make R1C1 References for Large Array
Formulas Using Macro Recorder
Excel Macro VBA Tip 19 - Speed up Macros in Excel by Turning Off
Screen Updating
Excel Macro VBA Tip 20 - Rename Modules in Excel for Macros
Excel Macro VBA Tip 21 - Call Macros from Other Macros in Excel
Excel Macro VBA Tip 22 - Open Workbooks with Excel Macros
Excel Macro VBA Tip 23 - Put a Time Delay in Macros Pause a Macro
for a Given Amount of Time Excel
Excel Macro VBA Tip 24 - Learn The For Next Loop For Excel Macros
Excel Macro VBA Tip 25 - Make User Input Boxes for Excel Macros &
Set a Variable Equal to The Input
Excel Macro VBA Tip 26 - Stop Cancel or Close a Macro using VBA in
Excel
Finance Basics in Microsoft Excel
Finance Basics 1 - Simple Interest in Excel
Finance Basics 2 - Compound Interest in Excel
Finance Basics 3 - Custom Compound Interest Function Made in Excel -
Macro - Custom Functions
Finance Basics 4 - Calculating Annuities in Excel - Future Value for
Annuities
Finance Basics 5 - Learn Present Value and what it Means & Does
Finance Basics 6 - Present Value Examples in Excel - How much
something is worth today.
Finance Basics 7 - Future Value Examples in Excel - How much
something is worth in the future
Finance Basics 8 - Future Value of a Principal Amount With Recurring
Annuity Payments in Excel
Finance Basics 9 - Future Value Calculation With Intra-Year
Compounding of Interest in Excel
Finance Basics 10 - Present Value Calculation With Intra Year
Compounding of Interest in Excel
Finance Basics 11 - Annuity Due Calculation in Excel - Present Value
and Future Value
Finance Basics 12 - Perpetuity Calculation in Excel
Finance in Excel 1 - Live Stock Quotes in Microsoft Excel - MSN
MoneyCentral Investor Stock Quotes
Finance in Excel 2 - Import and Chart Historical Stock Prices in
Excel
Finance in Excel 3 - Determine Your Portfolio Value in Excel Using
Live Stock Quotes
Finance in Excel 4 - Calculate Straight Line Depreciation in Excel
Finance in Excel 5 - Calculate Declining Balance Method of
Depreciation in Excel
Finance in Excel 6 - Calculate Double Declining Balance Method of
Depreciation in Excel
Finance in Excel 7 - Calculate The Declining Balance Method of
Depreciation in Excel - Using VDB()
Finance in Excel 8 - Quickly Import Income Statement, Balance Sheet,
Stmnt of Cash Flows into Excel
Finance in Excel 9 - View Live News and Headlines for Publicly
Traded Companies and Stocks in Excel
Excel Finance - Calculate the Present Value of a Bond with
Semiannual or Quarterly Interest Payments
Get the Net Present Value of a Project Calculation - Finance in
Excel - NPV()
Bond Pricing, Valuation, Formulas, and Functions in Excel
Calculate Value of Savings/Retirement Plan in Excel - FV()
How to find Interest & Principal payments on a Loan in Excel
How to make a Fixed Rate Loan/Mortgage Calculator in Excel
Data Analysis in Microsoft Excel
Excel Data Analysis 1 - Move Data from Rows to Columns - Transpose
Data in Excel
Excel Data Analysis 2 - Import CSV Files into Excel
Data Analysis in Excel 3 - Filter Large Data Sets to Display Only
Specific Information
Data Analysis in Excel 4 - Sort Large Data Sets to Display Ordered
Information
Data Analysis in Excel 5 - Remove Duplicate Entries from Data Sets
in Excel
Data Analysis in Excel 6 - Fix Broken Links in Excel Check Data
Connections to Other Files
Data Analysis in Excel 7 - Quickly Import XML Files and Data into
Excel
Data Analysis in Excel 8 - Import XML Maps and Pull Selected Columns
Values into Excel
Data Analysis in Excel 9 - Filter Data Based on The First, Last, or
Middle Values in a Cell in Excel
Data Validation in Microsoft Excel
Data Validation 1 - Create Drop Down Menus in Excel
Data Validation 2 - Create Drop Down Menus From Source Data on
Separate Tabs in Excel
Data Validation 5 - Create Drop Down Menus That Accept Other Values
in Excel
Data Validation 6 - Run Macros from Drop Down Menus in Excel
Data Validation 7 - Prevent Spaces From Being Entered Before or
After Text in Excel
Data Validation 8 - Custom Error Messages in Excel
Data Validation 9 - Prevent Duplicate Entries in a List.avi
Data Validation 10 - Prevent Duplicate Entries in an Often Updated
List.avi
Data Validation 11 - Limit the Total Number Amount Entered into a
Range
Printing in Microsoft Excel
Printing in Excel 1 - Print the Gridlines when an Excel Spreadsheet
is Printed
Printing in Excel 2 - Print Specific Data from a Worksheet in Excel
- Set Print Area Excel 2007
Printing in Excel 3 - Add Headers and Footers in Excel
Printing in Excel 4 - Prevent Specific Cells in Excel from Printing
Printing in Excel 5 - Fit More Data on a Printed Page - Scale Data
for Printing
Printing in Excel 6 - Adjust Margins in Excel - Spreadsheets that
Will be Printed - Page Setup
Printing in Excel 7 - Repeat Row & Column Titles on Every Printed
Page from Excel - Page Setup
Formatting Spreadsheets for Printing
Print multiple Worksheets and Workbooks in Excel
Microsoft Excel Functions & Formulas
Formulas in Excel 1 - Round Numbers in Excel with Round Function to
the Nearest Decimal or Integer
Formulas in Excel 2 - Round Numbers to Next Lowest Integer in Excel
Using the INT Function
Functions/Formulas in Excel 5 - Combine Text and Formulas in a Cell
Functions/Formulas in Excel 6 - Use Round & Rand to return Random
Numbers with 2 Decimal Places
Functions/Formulas in Excel 7 - Learn the IF Statement in Excel &
Output Numbers, Text, and Formulas
Functions/Formulas in Excel 8 - Find The Most Common/Repeated Value
in a List in Excel
Functions-Formulas in Excel 9 - Highlight Cells With Formulas
Without a Macro in Excel
Functions Formulas in Excel 10 - Formula Auditing - Find Out Which
Cells are Used in Your Formulas
Microsoft Excel Formatting
Excel Formatting Tip 1 - Conditional Formatting with Data Bars,
Color Scales, & Icon Sets
Excel Formatting Tip 2 - Highlight Unique or Duplicate Values with
Conditional Formatting
Excel Formatting Tip 3 - Prevent Buttons and Shapes from Resizing in
Excel when Cells Change Size
Excel Formatting 4 - Change Tab Color for Worksheets in Excel and
Add/Delete/Rename Tabs
Excel Formatting 5 - Remove Gridlines, Headings, and The Formula Bar
in Excel 2007
Excel Formatting Tip 6 - Highlight Cells Greater Than or Less Than a
Certain Value in Excel 2007
Excel Formatting Tip 7 - Angle Cells Diagonally in Excel Make Better
Looking Reports and Tables
Excel Formatting 8 - Remove Tabs and Scroll Bars from Excel 2007 for
a Cleaner Appearance
Excel Formatting Tip 9 - Freeze Panes & Prevent Headers from Moving
in the Spreadsheet in Excel
Excel Formatting Tip 10 - Quickly Add Professional Formating to
Spreadsheets and Cells in Excel
Excel Formatting 11 - Quickly Clear ALL Formatting From a Worksheet
in Excel
Excel Formatting 12 - Quickly Copy Formats to Other Cells in Excel
Microsft Excel Lookup and Search
Excel Lookup/Search Tip 1 - Using Index and Match - It's Better than
Vlookup
Excel Lookup/Search Tip 2 - Vlookup Explanation 1 - Display Product
Suppliers from a List/Database
Excel Lookup/Search Tip 3 - Vlookup Explanation 2 - Vlookup to
Search an Employee Database
Excel Lookup/Search Tip 4 - Vlookup Explanation 3 - Vlookup to
Assign Grades to Students
Excel Lookup/Search Tip 5 - Vlookup - Assign Values Instead of
Errors to a Vlookup with IFERROR
Excel Lookup/Search Tip 6 - Vlookup with a Drop Down Menu and Data
Validation
Excel Lookup/Search Tip 7 - Vlookup on Data that has a Prefix before
it
Excel Lookup & Search Tip 8 - Vlookup with Lookup Values that Have
Extra Values in the Cell in Excel
Excel Lookup & Search Tip 9 - Vlookup to Return Prices with Quantity
Discounts for Orders in Excel
Microsoft Excel VLOOKUP & HLOOKUP
VLOOKUP Introduction, Explanation, Example in Excel (1 of 2)
VLOOKUP Introduction, Explanation, Example in Excel (2 of 2)
Make a Dynamically Updated Vlookup Table in Microsoft Excel
VLOOKUP Between Separate Workbooks and Worksheets in Excel
Do a Two Way Lookup in Excel - Match() Index() Functions
HLOOKUP() in Excel - how to use it and edit it. Walkthrough
explanation.
Remove the #N/A Error from VLOOKUP in Excel
FIND() LEN() LEFT() RIGHT() Functions Extract Text From Cells in
Excel Advanced P.1
FIND() LEN() LEFT() RIGHT() Functions Advanced Extract Text From
Cells in Excel P.2
Miscellaneous Microsoft Excel Tutorials
Absolute and Relative Cell References
Import RSS Feeds - News Headlines - into Excel
View Twitter Updates from Excel 2007
Consolidate Data in Excel that has Multiple Duplicate Values On the
Same Excel Worksheet
Excel Tip - Speed up Slow Workbooks Turn Off Automatic Formula
Calculation
Consolidate and Combine Data in Excel Between Multiple Workbooks
Consolidate and Combine Data Between Multiple Excel Worksheets
Excel Tips - Quickly Fill Series of Numbers in a Few Seconds Fill
Command
Quickly Resize Multiple Columns and Rows Quickly in Excel
Pin Recent Documents in Excel to the Start Menu or Office Button
Menu in Excel 2007
Automatically and Completely hide the Ribbon in Excel 2007 as soon
as it opens - Macro
Completely hide the Ribbon in Excel 2007 - Macro
Use AutoCorrect in Excel 2007 - add/delete words from autocorrect.
Tips/Tricks Vid 2
Hide scroll bars, sheet tabs, and charts, graphs, pictures, and
objects in Excel 2007
Combine/Merge Cells in Microsoft Excel - Make Better Headers
Count Cells That Only Contain Text in Microsoft Excel
Create Dynamic Named Ranges in Excel Across Rows Going Up/Down
Create Dynamic Named Ranges in Excel which go Left-Right Across
Columns
Change Columns from Letters to Numbers in Excel 2007
Generate Random Numbers with Decimals in Microsoft Excel
Prevent Duplicate Values being entered in a List - Allow only unique
values to be entered.
Generate Random Numbers (Integers) in Microsoft Excel
Extract Text from cells in Excel - How to get any word from a cell
in Excel
Data Validation with a List - Only allow values contained in a list
to be entered into a cell
Dynamically Update a Drop Down Menu/List - Data Validation &
OFFSET() Function
Quickly Copy/Paste Data, Formulas, Text in Excel Using the Quick
Fill Handle
Absolute and Relative Cell References in Excel and INDIRECT()
Function
Group/Ungroup Data in Excel - How to Easily Hide Data
Match() and Index() Functions in Excel - Thorough Explanation
Combine/Consolidate Data Dynamically Between Seperate Excel files
How to use the Insert Function Formulas Lookup feature in Excel. Use
formulas in Excel.
Named Ranges in Excel - Use in formulas and functions. - Saves Time
Named Ranges in Excel - Use for Databases and Updating Information -
Saves Time
IF Statement and Nested IF Statement in Excel
How to combine cells - Concatenate cells in Excel
Linking Cells in Separate Worksheets in Excel
Format & Compile Data in Excel - left right mid functions
How to make text cells all lower case or upper case in Excel
Assign Macros to Toolbar Buttons in Excel
Assign Keyboard Shortcuts to Macros in Excel
How to link two (multiple) workbooks and cells in Excel
Make a Dynamic Gantt Chart in Excel- Dates Update Daily (Part 2 of
2)
Make a Dynamic Gantt Chart in Excel - Dates Update Daily (Part 1 of
2)
Record and use a Macro - Excel
Protect a Worksheet and Workbook in Excel
Arrays in Excel - Introduction and How-to Guide
Hide Rows, Columns, and Tabs in Excel
Import and Link Web Data and Information in Excel
Formulas and Functions in Excel - Introduction
Goal Seek - Function in Excel
Adjust multiple columns and rows at once in Excel
Locking and Unlocking cells in Excel
Password Protect Excel Documents & Remove Personal Information -
Security
Excel Tips 9 - Change The Color Scheme in Excel and Microsoft Office
Excel Tips 10 - Change Default Excel Spreadsheet Settings - #
Sheets, Font, Size, etc.
Excel Tips 11 - Paste Values in Excel - Replace Formulas with Values
Excel Tips 12 - Add Background Pictures to Excel Spreadsheets
Excel Tips 13 - Text Box in Excel - Put Large Amounts of Text into
Excel
Excel Tips 14 - Links in Excel to Quickly Navigate Between
Worksheets with Buttons
Excel Tips 15 - Add Comments, Remove Comments, Find Comments, and
Edit Comments
Excel Tips 16 - Add Pictures to Excel Comments and Change The
Background Color
Excel Tips 17 - Quickly Add Headers to Pages in Excel 2007
Excel Tips 18 - Quickly Sort Data Alphabetically and Numerically in
Excel 2007
Excel Tips 19 - Hide and Unhide Worksheets in Excel
Excel Tips 20 - Display In Cell Message when a Cell is Selected in
Excel Not a Comment
Excel Tips 21 - Automatically Display the Current Date in Excel
Excel Tips 22 - Keyboard Shortcuts for Excel 2007 The Easy Way to
Learn Them
Excel Tips 23 - Change Column Headings from Letters to Numbers in
Excel for Quick Referencing
Excel Tips 24 - Quickly Add Buttons to and Customize the Quick
Access Toolbar in Excel 2007
Excel Tips 25 - Change the Direction of the Active Cell after
Pressing Enter in Excel 2007
Excel Tips 26 - Quickly Search for and Use Complicated Functions in
Excel
Excel Tips 27 - Quickly Locate All Formulas Functions in an Excel
Worksheet or Workbook
Excel Tips 29 - Disable the Quick Fill Handle and Drag and Drop in
Excel
Excel Tips 30 - Install Add-Ins to Excel - Analysis Toolpak - Solver
- Etc..avi
Excel Tips 31 - Add Multiple Lines to Text within Cells - Use the
Enter key within a cell
Microsoft Excel Forms
Add a Text Label to an Excel UserForm
Add a Combo Box to a UserForm in Excel
More Than 60 Hours of
Free Computer Training
GETTING STARTED
Getting Started Tips
My
Training Approach
Student Feedback Please
Computer Training Classes
Click On The Class to
Register
Word: Top Twenty Tips
Excel: Top Twenty Tips
Outlook: Top Twenty Tips
PowerPoint: Top Twenty Tips
Google: Top Twenty Tips
QuickBooks: Top Twenty Tips
ACT: Top Twenty Tips
Full Class List & Schedule
Free Video Library
INTRODUCTION TO COMPUTERS
Basics: Computers 101
Introducing
Windows 8
THE INTERNET
Google Search Tips
Free Google Tools
YouTube Tips
Facebook
Twitter
MICROSOFT OFFICE
2007 & 2010
The Ribbon & more
Microsoft Word
Microsoft
Excel: Introduction
Microsoft Excel: Advanced
Microsoft
Outlook
Microsoft PowerPoint
Microsoft Access
Microsoft Publisher
Microsoft SharePoint
Microsoft FrontPage
Microsoft Expression
MICROSOFT OFFICE 2003
Microsoft Word 2003
Microsoft Excel 2003
Microsoft Outlook 2003
Microsoft PowerPoint 2003
SALES & ACCOUNTING
ACT!
QuickBooks
APPLE iPhone Training
iPhone Tips and Tricks
WEB DESIGN & Graphics
Microsoft FrontPage
Microsoft Expression
Photoshop
FREE RESOURCES
Khan Academy
Harvard and MIT
TED: Ideas Worth Spreading
Smarterer: Test Yourself
