LearnKey Training

Excel 2010 Course (77-882)

Excel 2010 Course (77-882)

Back to Product Page


Excel 2010 Course (77-882)

5 Sessions -
17 Hours of Interactive Training

In this course expert Michael Meskers will be covering all of the newest features of Excel 2010, giving you in depth explanations to create a better understanding of material. This course will provide you with 100's of tips and techniques that are taught in “Plain English” allowing you to increase efficiency and productivity in your day-to-day Excel use.

Benefits
  • 100s of tips and tricks to help you increase speed and efficiency
  • In-depth explanation of all topics taught
  • Learn all of the new and greatest features of Excel 2010

About The Author
Michael Meskers is more than just a trainer. With a B.A., two Masters Degrees and fluent in six languages, Michael is highly adept at helping students understand the complexities of applications in many languages. With 19 years of combined experience as an investment banker, supervisor and manager for commercial airlines, Michael brings real-world experience into his instruction. Michael’s clients include William H. Mercer Inc., Sanford Bernstein Inc., The New School University, Digi-Com Group and the Learning Annex. Michael also authored “The Learning Line: Windows Vista” published by J. Wiley & Sons Inc.

Session 1

Section A: Introduction

  • Course Overview
  • Excel 2010 Introduction
  • Quick Access Toolbar
  • Toolbar Customization
  • Additional Customization Buttons

Section B: Navigating Excel

  • Ribbon Organization
  • Function Grouping
  • Multiple Function Access
  • The Document Window
  • Basic Navigation
  • Workbook Options

Section C: Excel 2010 Shortcuts

  • Best Practice Guidelines
  • Common Hot Keys
  • Speed Keys
  • Function Keys
  • CTRL Keys
  • ALT Keys
  • Visible Speed Keys

Section D: Creating and Manipulating Data

  • Entering Data
  • Auto Fill a Series
  • Creating Custom Lists
  • Fill Adjacent Cells
  • Fill Non-Adjacent Cells
  • Auto Fill on Multiple Sheets

Section E: Managing Worksheets

  • Copying/Moving Worksheets
  • Renaming Worksheets
  • Inserting/Deleting Worksheets
  • Hiding Worksheets

Section F: Data Validation

  • Range Criteria
  • List Criteria
  • Extending the List
  • Input Message
  • Validation Error Alerts
  • Validation by Character

Section G: Modifying Cell Content

  • Copy/Paste Options
  • Paste
  • Inserting Columns
  • Transporting Data
  • Copy Cell Formatting
  • Removing Duplicates

Section H: Changing Views

  • Zoom
  • Workbook Views
  • Page Layout
  • Freeze Panes
  • Split Windows
  • Viewing Multiple Windows

Session 2

Section A: Formatting Data

  • Formatting Worksheets
  • Cell/Font Color
  • Background Images
  • Watermarks
  • Worksheet Tab Color

Section B: Formatting Numbers

  • Modifying Cell Formats
  • Format Painter
  • Date Formatting
  • Formatting Multiple Cells
  • Numerical Cell Values
  • Special Formats

Section C: Formatting Text and Tables

  • Formatting Fonts / Fills
  • Cell Height/Width
  • Noncompatible Commands
  • Cell Alignment
  • Inserting Comments
  • Merge and Center
  • Tables

Section D: Understanding Formulas

  • Operations
  • Arithmetic Operations
  • Additional Operation Symbols
  • SUM Function
  • Ranges in Functions
  • AutoSum
  • Average
  • Min/Max Functions
  • Count Formula
  • AutoCalculate
  • Modifying AutoCalculate
  • Copying Formulas
  • Cell Multiplication
  • Relative Cell References
  • Absolute Cell References

Session 3

Section A: Referencing Formulas

  • Totaling Multiple Sheets
  • AutoFit Multiple Cells
  • Linked Formulas
  • Grand Totals
  • Sparkline Graphics
  • Sparkline Colors
  • Consolidation
  • Consolidating Data
  • Linking to Source Data
  • Hierarchical Outline
  • Consistency

Section B: Ranges and Dates

  • Naming Ranges
  • Adding Named Ranges
  • Dates
  • Date Speed Keys
  • Formula Calculations
  • Formula Auditing
  • The Watch Window
  • Formula Evaluation

Section C: Subtotals

  • Using Subtotals
  • Subtotal Options
  • Using Data Hierarchy
  • Selecting Visible Data

Section D: Using VLOOKUP

  • VLOOKUP
  • VLOOKUP Type I
  • VLOOKUP's Three Arguments
  • VLOOKUP Type I Methodology
  • VLOOKUP Type II

Section E: Using Nested Formulas

  • Nested Formulas
  • Absolute Numbers
  • Nested Absolutes
  • Nested Formula Logic
  • FIND
  • INDIRECT
  • ROW/COL

Section F: Conditional Logic

  • The IF Formula
  • The IF Statement
  • Nested IF
  • Nested IF Syntax

Section G: More Conditional Logic

  • Insert Wizard
  • AND
  • OR
  • NOT
  • IFERROR
  • SUMIF / AVERAGEIF / COUNTIF
  • SUMIFS
  • AVERAGEIFS
  • COUNTIFS

Session 4

Section A: Financial Formulas

  • Working with Financial Formulas
  • Future Value Function
  • Function Categories
  • Calculating Interest Rate
  • Calculating Total Payment
  • Calculating Monthly Payment
  • Using Goal Seek

Section B: What-If Functions

  • Solver Add-in
  • Using Solver
  • Constraints
  • Reports
  • Comparing Options
  • Scenario Manager
  • Scenario Report
  • Data Tables
  • Applying Data Tables
  • Comparing Data Tables

Section C: Working with Text Formulas

  • Concatenate
  • Text to Columns
  • Extraction Formulas
  • Combining with Previous Formulas
  • UPPER/LOWER/PROPER
  • Converting Formulas with Function Keys

Section D: Paste Special

  • Pasting Values
  • Pasting Formats
  • Quick Multiplication
  • Addition
  • TRIM
  • Substitute

Section E: Introduction to Charts

  • Charts
  • Chart Types
  • Instant Chart
  • Updating Charts
  • Add Secondary Y/Value Axis
  • Adding Data Labels
  • Fill
  • Picture Fill
  • Line/Scatter Charts

Section F: Formatting Charts

  • Chart Styles
  • 3-D Charts
  • Quick Layouts
  • Formatting Chart Labels
  • Layout
  • Chart Title
  • Legends

Section G: Conditional Formatting

  • Conditional Formatting Options
  • Selecting Cells to Format
  • Top/Bottom Rules
  • Data Bars/Color Scales/Icon Sets
  • Managing Rules
  • Wildcards
  • Alternative Row Shading via Formulas

Section H: Adding Graphics to Spreadsheets

  • Inserting Pictures
  • Modifying Pictures
  • Inserting Shapes
  • Inserting SmartArt
  • Modifying SmartArt
  • Themes

Session 5

Section A: Outlining, Sorting, and Filtering

  • Grouping
  • Ungrouping
  • Sorting Data
  • Sorting Levels
  • Filtering Data
  • Selecting Filtered Rows
  • Advanced Filtering

Section B: PivotTables and PivotCharts

  • PivotTables
  • Creating PivotTables
  • PivotTable Layout
  • Defaults
  • Updating/Modifying Data
  • Calculated Fields
  • Calculated Items
  • Filtering PivotTables
  • Slicer
  • PivotCharts

Section C: Protecting Data

  • Locking Cells
  • Protect Workbook
  • Hiding Cells
  • Hiding Worksheets

Section D: Introduction to Backstage

  • Document Properties
  • Titles/Tags/Categories
  • Related Dates / People
  • Formatting Comments
  • Document Inspector
  • Sparklines/Slicer
  • Compatibility Checker
  • Document Inspection

Section E: Collaboration

  • Sharing Documents
  • Sharing Workbooks
  • Protecting Shared Workbooks
  • Tracking Changes
  • Accept/Reject Changes
  • Information Rights Management
  • Document Signature
  • Mark as Final

Section F: Saving Workbooks

  • Save as Previous Version
  • File Sharing with Previous Excel Version
  • Customization/Compatibility
  • Excel Options
  • Templates
  • Opening/Deleting Templates
  • Save as a Web Page
  • Single File Web Page

Section G: Printing Workbooks

  • Page Orientation
  • Margins
  • Print Area/Page Breaks
  • Page Sailing
  • Headers/Footers
  • Row/Column Headings
  • Other Print Options

Section H: Macros with VB for Applications

  • Macros
  • Recordable Macros
  • Non-Recordable Macros
  • Creating a Macro
  • Naming Macros
  • Shortcut Keys
  • Storage Options
  • Recording a Macro
  • Testing Macros
  • Editing Macros with Visual Basic
  • Testing Edited Macros
  • Relative Reference Macros
  • Assigning Buttons to Macros
  • Modifying Button Icons
  • Macro-Enabled Workbooks
  • Save Workspace