Group Services: Technology Consulting
phone +91-9999-283-283/9540-283-283
email info@sisoft.in

Course Details

Course outline Advance MS Excel

MS 101: Advance Excel

  • MS Excel
    • Overview Ribbon Menu
    • Customize Excel
    • Worksheet (Insert/Delete)
    • Templates
  • Formatting
    • Font
    • Alignment
    • Number
    • Styles
      1. Cell Styles
      2. Format as Table
      3. Conditional Formatting
  • Conditional formatting of cells
    • Creating, Managing and Merging Styles for Cell Formatting
  • Data filters: AutoFilter and advanced filters

    (Advanced Filter can extract data to a different sheet, filter list in place, extract list of unique records to eliminate duplicates.)

    • Sorting, customize sorting
    • (You can use a custom list to sort or fill in a user-defined order.)

    • 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

(Data Validation is only one of the wonderful functionalities of Microsoft Excel. Data Validation allows the creator of an Excel file to limit the values that may be entered into any cell.)

  • Working with functions (based on your requirements)
    • Financial functions
    • Date and time functions
    • Statistical functions
    • VLookup & HLookup and reference functions
    • Database functions
    • Text manipulation functions
    • Logical functions
    • Worksheet and cell information functions
  • Chart Data Techniques
    • The Chart Wizard
    • Chart Types
    • Adding Title,Legends,Lables
    • Printing Charts
    • Adding Data to Chart
    • Formatting,Renaming,Deleting Data Series
    • Changing the Order of Data Series
  • Pivot Tables for MIS reporting & data summarization
    • 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

(Goal Seek is part of a suite of commands sometimes called what-if analysis tools. When you know the desired result of a single formula but not the input value the formula needs to determine the result)

  • Auditing
    • Tracing precedents and dependents
    • Tracing errors
  • Managing add-ins
    • Customizing toolbars and menus
  • Customizing views
    • Customizing calculations and iterations
    • Settings
    • Creating custom lists
  • MS Excel - Macros topics
    • Recording and running macros
    • Types of references in macros
    • Assigning a macro to a toolbar or menu
  • User-defined functions
    • Adding descriptions to user-defined functions