Search
Generic filters
Topics
Certifications
Industries
Advanced Microsoft Excel
Course information

This course provides users with the knowledge to use the more complex features of Excel. It is designed to give an insight into some of the more advanced features in Excel and will be of particular interest to those who want to build or work with financial models, carry out detailed data analysis and automate tasks by recording macros.

Course Objectives

By the end of the program, participants will be able to:

  • Develop skills for advanced use of excel
  • Identify the different use of excel functions and shortcuts
  • Learn how to perform complex functions
  • Develop skills in analysis using excel
  • Learn how to present data using charts and tables
  • Master the use of excel macros and pivot tables.
Course Outline

Day 1:

Module 1: Advanced Excel Shortcuts 

  • Extensive exercise on excel shortcuts
  • Identify the different use of excel functions and shortcuts
  • Mastering the shortcut window

Module 2: Data Presentation with charts 

  • Extensive exercise on excel shortcuts
  • Types of charts and their specific uses- Trend line and advanced charts.
  • Modifying and formatting existing charts.

Module 3: Sorting And Filtering data

  • Sort data as per value, color, icons
  • Filter based on Font/Cell color
  • Turning Auto Filter off
  • Use subtotal features and work with database functions Understanding References
  • Relative, Absolute & Mixed reference
  • Referencing different workbooks & Merging
  • Consolidating data, linking cells in different work books
  • Utilizing Dynamic Named Ranges for writing formulas and functions

Module 4: Manipulating Dates

  • Dates formats
  • Extracting Day, Month or Year from a date value
  • Extracting Day, Month or Year from a date value using TEXT Formula
  • Extracting end month from a date value

Module 5: Advanced Excel Formulas  

  • Logical Functions
  • Ifs and Nested Ifs Statements
  • AND, OR and NOT Functions
  • Error handling using IFERROR, ISNA and ISERROR

Module 6: Text Formulas    

  • Logical functions
  • Ifs and nested ifs statements
  • Text formulas
  • Concatenate
  • Find v/s search
  • Left, right, mid
  • Upper, proper, lower lookup formulas
  • Vlookup & Hlookup
  • Index & match
  • Sumproduct function

Module 7: Data Validations and Protection      

  • Specifying a valid range of values for a cell
  • Specifying a list of valid values for a cell
  • Creating Drop down list
  • Managing Themes, creating and use of templates

Day 2:

Module 8: What-if Analysis

  • Using the Scenario Manager
  • Creating and editing scenario
  • Creating a Scenario Report
  • Using Goal Seek

Module 9: Data Tables

  • The single-variable data table
  • The two-variable data table
  • Adding conditional formatting

Module 10:  Working with Outlines

  • Applying an outline
  • Automatic outlining
  • Displaying and collapsing levels
  • Modifying outline settings
  • Creating subtotals

Module 11:  Analysing Data with Pivot Tables

  • Creating a pivot table
  • Defining the source range
  • moving and deleting fields
  • Showing and hiding fields
  • Grouping and ungrouping fields
  • Renaming fields
  • Defining field properties
  • Formatting a pivot table
  • Refreshing a pivot table
  • Drilling down

Module 12: Excel Macros

  • About macros and VBA
  • Macro security
  • Recording macros
  • Running a Macro
  • Absolute and relative references
  • Saving and distributing macros
  • Assigning a macro to a button
  • Editing macro code
  • Automatic macros
CERTIFICATION

DISS CERTIFICATE

Date: 11 July 2020

Date: 15  August 2020

Date: 11 September 2020

Duration: 12 hours (2 hours per day)

Course Timing: 

11:00am – 12:00pm

 

TESTIMONIAL

Click edit button to change this text. Lorem ipsum dolor sit amet consectetur adipiscing elit dolor. Click edit button to change this text. Lorem ipsum dolor sit amet consectetur adipiscing elit dolor

Person name

Click edit button to change this text. Lorem ipsum dolor sit amet consectetur adipiscing elit dolor. Click edit button to change this text. Lorem ipsum dolor sit amet consectetur adipiscing elit dolor

Person name

Click edit button to change this text. Lorem ipsum dolor sit amet consectetur adipiscing elit dolor. Click edit button to change this text. Lorem ipsum dolor sit amet consectetur adipiscing elit dolor

Person name
Meet THE INSTRUCTORS
instructor
Instructor Name
Profession

Click edit button to change this text. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Click edit button to change this text. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

instructor2
Instructor Name
Profession

Click edit button to change this text. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Click edit button to change this text. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Related Courses