CLASSROOM
TENTATIVE

SCHEDULES


Feb 10, 2018 9:00 am 18:00 pm
Feb 17, 2018 9:00 am 18:00 pm
REGISTER NOW

MICROSOFT

MS ADVANCED EXCEL 2010

DESCRIPTION
COURSE CONTENT

Duration: (24 hours)

 

Module 1 : Working with Excel Documents

I. The Excel 2010 Interface

  • The 2010 Ribbon, Tabs, and the Quick Access Toolbar
  • The Backstage View
  • Customizing the Interface    

           - Exploring Excel Options

           - Customizing Quick Access Toolbar

  • Customizing Views

           - Freezing/Splitting Windows

           - Exploring Different Worksheet Views

           - Saving Workspaces

II. Formatting Cells

  • Review on Basic Formatting and Changes in 2010
  • Using Table Styles and Cell Styles
  • Using Themes

III.  Adding and Editing Images

  • Inserting Images
  • Using Picture Tools and Picture Effects
  • Using SmartArt Graphics
  • Using the Screenshot Tool
  • Adding Headers and Footers

IV. Security Options

  • Protecting Cell Contents and Worksheets
  • Editing Permissions
  • Protecting the File
  • Using the Signature Line
  • Creating Digital Signatures

V. Sharing Options

  • Allowing Multiple Users to Edit Workbooks
  • Tracking Changes

VI. Printing Options

  • Exploring the Page Layout Tab
  • Using the Print Titles Tool

Module II : Working with Excel Data

VII.  Review on Basic MS Excel Functions

o Review of Basic Arithmetic Functions

  • Mathematical Functions
  • The COUNT Family

o Review on Cell Content- Related Functions/Tools

  • Concatenating and Splitting Cell Contents
  • Extracting Cell Contents

o Creating IF Statements

o Dealing with Errors

 

VIII.  Working with Lookups and References

o The Lookup Functions

  • VLOOKUP and HLOOKUP
  • MATCH and INDEX
  • INDIRECT

IX. Working with Date and Time

o Time and Date Formulas

 

X. Combining Formulas

o The Boolean Functions

o Advanced Creating of Formulas Exercise

 

XI. Formula Auditing

o Tracing Dependents and Precedents

o Evaluating Formulas

o Using the Watch Window

o Calculation Options

 

XII.  Analysis Tools

o Using Goal Seek and Solver

o Using the Scenario Manager

o The Analysis ToolpakTM

 

XIII.  Conditional Formatting

o Exploring Conditional Formatting in Excel 2010

  • Review on Conditional Formatting
  • Data Bars, Color Scales, and Icon Sets
  • Creating Formula- Based Formatting

 

XIII. Creating Charts

o Changes in Making Charts in 2010

o Designing Combination Charts

o Introduction to Sparklines

 

XIV.  Database Management

o Review on Data Validation Options

  • Adding Input Messages
  • Designing Error Alerts

o Working with Duplicates

  • Review on Dealing with Duplicates
  • Introduction to Advanced Filtering Tool
  • Using a Function to Identify Duplicates
  • Remove Duplicates Tool

o Using the Consolidate Tool

o Using the Outline Tools

o Advanced Filtering

  • Creating Criteria Ranges
  • Using Advanced Filtering

 

XV. PivotTables and PivotCharts

o Creating PivotTables and PivotCharts

o Designing PivotTables and PivotCharts

 

XVI.  Exporting and Importing Data

o Exporting and Importing Text Files

o Microsoft Query Overview

o Using Connection Files

 

XVII.  Web and Internet Features

o Saving as a Webpage

o Using Hyperlinks

 

Module III : Macros

 

XVIII.  Creating Macros

o Exploring Macro Security

o Recording and Running a Macro

  • Using Relative Reference

o Assigning a Macro to an Image or Button

   Exploring Ways to Use Macros