Advanced MS Excel

Advanced Excel Course Contents

Duration:    2 days 

Course Contents:

• Data filters: Auto Filter and advanced filters
  • Sorting, Customize sorting
  • Subtotals
• Cell level validations
  • Specifying a valid range of values for a cell
  • Specifying a list of valid values for a cell
  • Specifying custom validations based on formula for a cell
• Using data tables for data analysis
  • Mastering PivotTables
  • Using external data sources
  • Multiple consolidation ranges
  • Customizing PivotTable layout
  • PivotTable advanced options
  • Pivot Charts
• Working with external data (Requires Microsoft Query)
  • Querying external databases
  • Saving, editing and reusing external data queries
  • Web queries
  • Importing text files in Excel
  • Refreshing data
• Using data forms for managing tabular data
  • Specifying criteria for a form
  • Searching with a form
• Workbook sharing
  • Tracking changes
  • Merging workbooks
  • Workbook and sheet protection
• Goal Seek
  • Scenario Manager
  • Creating and editing scenarios
  • Merging scenarios
• Auditing
  • Tracing precedents and dependents
  • Tracing errors
• Managing add-ins
  • Customizing toolbars and menus
• Customizing views
  • Customizing calculations and iterations
  • Settings
  • Creating custom lists
• Conditional formatting of cells
  • Creating, managing and merging styles for cell formatting
• Working with functions (based on your requirements)
  • Financial functions
  • Date and time functions
  • Statistical functions
  • Lookup and reference functions
  • Database functions
  • Text manipulation functions
  • Logical functions
  • Worksheet and cell information functions
• MS Excel – Macros topics
  • Recording and running macros
  • Types of references in macros
  • Assigning a macro to a toolbar or menu