Excel Master Online Training Package

COURSE INTRODUCTION

Learn the skills every Excel user needs to know--no matter how new (or how experienced) you are. Whether you want to learn Excel 2013, 2010, 2007, or upgrade from one version to the next, our expert instructors will use a hands-on, engaging approach to teach you all of Excel's powerful features . Watch a single title or watch them all, as many times as you like for an entire year--all at one low price.

Learn anytime, anywhere, at home or on the go.

Stream your training via the internet, or download to your computer and supported mobile device, including iPad™, iPhone™, iPod™ Touch and most Android devices.

Training Bundle Includes

• Microsoft Excel 2013 Training - 13 hours
• Microsoft Excel 2010 Essentials - 7 hours
• Microsoft Excel 2010 Advanced - 6 hours
• Microsoft Excel 2007 Essentials - 6 hours
• Microsoft Excel 2007 Advanced - 7 hours

* BONUS - Titles Include All Project Files to Work Along with the Instructor


COURSE OUTLINES

Microsoft Excel 2013

Chapter 1: GETTING ACQUAINTED WITH EXCEL (51 min)

1. If You're in a Hurry, Use a Template
2. What's on the Screen
3. File Management Basics
4. Editing the Content and Structure of a Worksheet
5. Creating a Sample Worksheet

Chapter 2: TECHNIQUES FOR EASIER DATA ENTRY (14 min)

1. Using Auto Fill
2. Selecting an Area for Faster Data Entry
3. Using Automatic Decimals
4. Using Flash Fill

Chapter 3: MANAGING LISTS WITH EXCEL (42 min)

1. Sorting a List of Names and Addresses
2. Formatting and Editing a Table
3. Advanced Filter
4. Finding and Replacing Data
5. Splitting Cells

Chapter 4: Basic Calculations with Formulas (46 min)

1. How Do You Write a Formula?
2. Adding, Subtracting and Basic Number Formatting
3. Squaring and Cubing Numbers
4. Absolutely Understand Absolute References
5. Formatting Numbers
6. The Order of Calculations is Critical
7. Joining Cells

Chapter 5: CALCULATING WITH FUNCTIONS (48 min)

1. Understanding How Functions Work
2. SUM Function and AutoSum Tool
3. Adding a Whole Worksheet with Just Two Clicks
4. Calculating an Average
5. Finding the Highest, Lowest and Quantity of Values
6. Inserting, Calculating and Formatting Dates
7. Automatic Subtotals
8. Calculations across Worksheets

Chapter 6: FUNCTIONS THAT MAKE DECISIONS (30 min)

1. IF: Values that Depend on a Condition
2. SUMIF and AVERAGEIF: Sums and Averages That Depend on a Condition
3. VLOOKUP: Extract Data from a Table

Chapter 7: MATH, STATISTICAL and FINANCIAL FUNCTIONS (39 min)

1. Square Roots and Logarithms
2. Correlation, Standard Deviation, Median and Large
3. Calculating Monthly Loan Payments
4. Using Net Present Value to Evaluate an Investment

Chapter 8: FORMATTING WORKSHEETS (72 min)

1. Row and Column Size and Cell Alignment
2. Using and Modifying Built-in Styles
3. Analyzing Data with Conditional Formatting
4. Inserting and Modifying Pictures
5. Inserting Pictures from Online Sources
6. Drawing and Modifying Shapes
7. Using Shortcuts to Draw and Modify Shapes
8. Creating Diagrams with SmartArt

Chapter 9: Managing Workbooks (37 min)

1. Arranging, Inserting, Moving and Copying Worksheets
2. Freezing Row and Column Titles in Place
3. Repeating Row and Column Titles
4. Hiding and Un-hiding Rows and Columns

Chapter 10: DATA ANALYSIS TECHNIQUES (30 min)

1. Goal Seek
2. Creating Scenarios for Multiple What-Ifs
3. Using the Quick Analysis Tool
4. Using the Inquire Add-in to Compare Worksheets

Chapter 11: CREATING CHARTS IN EXCEL (55 min)

1. Creating Charts Instantly: Two Methods
2. Creating a Column Chart Manually
3. Creating a Pie Chart Manually
4. Sparklines: Miniature Charts in Cells
5. Setting Up a GeoFlow 3D Map
6. Animating a GeoFlow 3D Map

Chapter 12: PIVOTTABLES and POWER VIEWS (42 min)

1. Overview of PivotTables
2. Inserting a Recommended PivotTable
3. Creating a PivotTable Manually
4. Modifying a PivotTable and Filtering with Slicers
5. Creating a PivotChart
6. Creating a Power View Report

Chapter 13: PRINTING A WORKSHEET (33 min)

1. Print Layout, What Should Fit and How
2. Inserting Headers and Footers
3. Printing and Print Settings

Chapter 14: COLLABORATION (81 min)

1. Writing and Editing Comments
2. Creating PDF Documents
3. Importing and Exporting Data
4. Restricting Editing of a Workbook
5. Protecting Workbook Structure, Finalizing and Password Protection
6. Tracking Changes Other People Make to a Workbook
7. Saving a Workbook in SkyDrive, SharePoint, or Dropbox
8. Allowing Others to Modify Your Open Workbook
9. Inviting Others to Share Your Workbook
10. Sharing a Workbook on a Social Network
11. Share Workbooks on the Web with the Excel Button

Chapter 15: AUTOMATING TASKS (31 min)

1. Overview of Automation
2. Installing and Using Office Apps
3. Creating Macros by Recording Keystrokes
4. Editing Macros in Visual Basic for Applications

Chapter 16: CUSTOMIZING EXCEL 2013 (24 min)

1. Modifying the Ribbon Bar and Quick Access Toolbar
2. Setting Excel's Options
3. Credits

Microsoft Excel 2010 Essentials

Chapter 1: GETTING COMFORTABLE WITH EXCEL (51 min)

1. Introducing Excel
2. Recognizing Interface Features Unique to Excel
3. Understanding Workbook Structure
4. Navigating through Workbooks
5. Making Workbook Selections

Chapter 2: THE BASICS OF DATA (41 min)

1. Entering Text
2. Entering Dates and Numbers
3. Editing Cell Entries
4. Copying and Moving Data
5. Filling a Series

Chapter 3: MANAGING WORKBOOK STRUCTURE (28 min)

1. Modifying Workbook and Worksheet Structure
2. Resizing Worksheet Elements
3. Hiding Workbook Components

Chapter 4: CREATING FORMULAS (33 min)

1. Excel Calculations 101
2. Entering Formulas
3. Enforcing Absolute Referencing
4. Controlling Calculation Options

Chapter 5: THE BASICS OF FUNCTIONS (26 min)

1. The Foundation of Functions
2. Using AutoSum and the Formulas Tab
3. Using Function AutoComplete

Chapter 6: WHAT'S IN A NAME? (43 min)

1. Creating Named Cells
2. Defining Names
3. Creating Names from Selections
4. Creating Named Formulas
5. Using Names as References
6. Managing and Displaying Names

Chapter 7: MAKING DATA LOOK GOOD (74 min)

1. Applying Basic Formatting
2. Formatting Numbers
3. Exploring the Format Cells Dialog Box
4. Dening Custom Formats
5. Creating and Applying Cell Styles
6. Conditional Formatting
7. Applying and Editing Conditional Formatting Rules

Chapter 8 A PICTURE IS WORTH A THOUSAND WORDS (38 min)

1. Getting In-Cell Summaries with Sparklines
2. Creating Charts
3. Modifying Chart Design
4. Working with Chart Layout and Format
5. Saving and Using a Chart Template

Chapter 9 THE SIMPLICITY OF TABLES (54 min)

1. Converting Data Ranges to Tables
2. Modifying Table Formatting
3. Working with Table Records
4. Using Table Calculations
5. Sorting Data
6. Creating Custom Lists
7. Filtering Data

Chapter 10 MANAGING THE EXCEL ENVIRONMENT (20 min)

1. Configuring Excel Options
2. Adjusting the View
3. Controlling Excel Windows

Chapter 11 PREPARING FILES FOR DISTRIBUTION (23 min)

1. Print Area, Print Titles and Sheet Options
2. Renaming Sheets and Adding Headers/Footers
3. Printing Worksheets
4. Credits

Microsoft Excel 2010 Advanced

Chapter 1: ADVANCED CALCULATIONS (97 min)

1. Using Lookup Functions
2. Using Text Functions
3. Breaking Apart Text Entries
4. Using Date and Time Functions
5. Using the Net Workdays Function
6. Using Logical Functions and Fixing Errors
7. Using Text in Calculations
8. Using Financial Functions
9. Using the Cumulative Interest Payment Function
10. Using Aggregate vs. Math Functions

Chapter 2: SUBTOTALS and OUTLINES (25 min)

1. Creating Subtotals
2. Working with Outlined Data
3. Manually Creating Subtotals and Outlines
4. Creating Custom Views

Chapter 3: PIVOTTABLES© (57 min)

1. Constructing PivotTables
2. Modifying PivotTable Calculations
3. Setting PivotTable Properties
4. Using Filters and Slicers in the PivotTable
5. Using the Report Filter and Updating Data
6. Working with PivotTable Data in Formulas and PivotCharts

Chapter 4: ADAPTING CHARTS FOR COMPLEX DATA (19 min)

1. Adding a Secondary Chart Axis
2. Working with a Scatter Chart
3. Hidden and Empty Cell Settings, Formatting Error Bars
4. Adding a Trendline

Chapter 5: ADVANCED DATA TOOLS (44 min)

1. Importing Data and Adding Hyperlinks
2. Converting Text to Columns
3. Linking to Databases and Removing Duplicate Records
4. Consolidating Data
5. Validating and Protecting Data

Chapter 6: AUDITING CALCULATED VALUES (34 min)

1. Working with Circular References
2. Tracing Precedents and Dependents
3. Evaluating Formulas
4. Using the Watch Window
5. Error Checking

Chapter 7: PERFORMING A WHAT-IF ANALYSIS (28 min)

1. Finding an Input Value with Goal Seek
2. Displaying Options via Scenarios
3. Including Variable Values through Data Tables

Chapter 8: MACROS, VBA and SECURITY 60 min)

1. Macro Concepts
2. Creating a Basic Macro
3. Manipulating Cell and Range References
4. A Brief Introduction to VBA
5. Viewing and Editing VBA in Excel
6. Making Macros Accessible
7. Applying Security Settings
8. Credits

Microsoft Excel 2007 Essentials

Chapter 1: WORKING WITH THE USER INTERFACE (25 min)

1. Introducing the Ribbon User Interface
2. Introducing Workbooks and Worksheets
3. Introducing the Formula Bar
4. Saving with Different File Extensions
5. Customizing the User Interface

Chapter 2: ENTERING and MANAGING DATA (47 min)

1. Getting Data into Excel
2. Importing Text Files
3. Using the Freeze Panes Functionality
4. Using the Zoom Functionality
5. Using Hot Keys
6. Managing Rows and Columns
7. Finding Data
8. Matching Case and Entire Cell Contents
9. Replacing Data
10. Finding and Replacing Empty Values
11. Sorting Data
12. Sorting Multiple Columns
13. Filtering Data

Chapter 3: USING FORMATTING TECHNIQUES (23 min)

1. Formatting Numbers and Dates
2. Formatting Cells
3. Setting the Font Type, Color and Size
4. Using Borders and Cell Styles
5. Adding Shapes and Pictures
6. Creating WordArt

Chapter 4: Formula Basics (34 min)

1. Constants, Formulas and Cell References
2. Understanding the Order of Operations
3. Using Formulas with Dates and Text
4. Linking Cells and Using Comparison Operators
5. Creating an Absolute Reference
6. Defining Named Ranges
7. Exploring the Function Library
8. Using the AutoSum Function

Chapter 5: USEFUL FUNCTIONS (40 min)

1. Using the INT Function
2. Using the Rounding Function
3. Using the ABS Function
4. Using the SUBTotal Function
5. Using Logical Functions
6. Using the AND and the OR Function
7. Using LEFT, RIGHT and MID Functions
8. Using the UPPER, LOWER, PROPER and TRIM Functions
9. Using COUNT Functions
10. Using the VLOOKUP Function

Chapter 6: CREATING REPORTS (33 min)

1. Creating a Subtotal Report
2. Using Conditional Formatting
3. Creating and Managing Conditions
4. Creating a Chart
5. Refining Charts
6. Using Basic Chart Types

Chapter 7: SHARING and DISTRIBUTING YOUR WORK (25 min)

1. Inserting Headers and Footers
2. Adding Comments
3. Adjusting Page Layout
4. Distributing Your Worksheets
5. Protecting Your Worksheets
6. Understanding Compatibility

Chapter 8: TIPS and TRICKS (53 min)

1. Using the Fill Handle
2. Filling in a Series of Numbers
3. Typing in Multiple Cells at Once
4. Leveraging a Custom List and Transposing a Range
5. Deleting and Inserting Blank Cells
6. Filling Holes in Your Data
7. Getting a List of Unique Records and Parsing Text
8. Rearranging Columns and Linking to Multiple Cells
9. Using Right Mouse Button Drag Options
10. Make Format Painter Persist and Paste Special Formats
11. Formatting Portions of Text and Multiple Sheets
12. Creating Cell Border Effects and Sorting on Color
13. Turning Formulas into Hard Data
14. Copy Formula References and Highlight Formulas
15. Creating Dynamic Charts and Combination Charts
16. Add a Secondary Axis, Average Line and Smooth Line
17. Comparing Data Dynamically with Charts
18. Final Comments and Credits

Microsoft Excel 2007 Advanced
...