• The individuals with some working experience in Excel should opt for this course.

The candidates willing to learn Advanced Excel should have working experience with Excel.

Detailed Course Topics

ADVANCED EXCEL

1. Getting started with Excel

• Identifying the different Excel programs
• Identifying new features of Excel 2016
• If you are upgrading from Excel 2013
• If you are upgrading from Excel 2010
• If you are upgrading from Excel 2007
• If you are upgrading from Excel 2003
• Working with the ribbon
• Customizing the Excel program window
• Zooming in on a worksheet
• Arranging multiple workbook windows
• Adding buttons to the Quick Access Toolbar
• Customizing the ribbon
• Maximizing usable space in the program window
• 4 Creating workbooks
• Modifying workbooks
• Modifying worksheets
• Inserting rows, columns, and cells
• Merging and unmerging cells

2. Working with data and Excel tables

• Entering and revising data
• Managing data by using Flash Fill
• Moving data within a workbook
• Finding and replacing data
• Correcting and expanding upon worksheet data
• Defining Excel tables

3. Performing calculations on data

• Naming groups of data
• Creating formulas to calculate values
• Summarizing data that meets specific conditions
• Working with iterative calculation options and automatic workbook calculation
• Using array formulas
• Finding and correcting errors in calculations

4. Changing workbook appearance

• Formatting cells
• Defining styles
• Applying workbook themes and Excel table styles
• Making numbers easier to read
• Changing the appearance of data based on its value
• Adding images to worksheets

5. Focusing on specific data by using filters

• Limiting data that appears on your screen
• Filtering Excel table data by using slicers
• Manipulating worksheet data
• Selecting list rows at random
• Summarizing worksheets by using hidden and filtered rows
• Finding unique values within a data set
• Defining valid sets of values for ranges of cells

6. Reordering and summarizing data

• Sorting worksheet data
• Sorting data by using custom lists
• Organizing data into levels
• Looking up information in a worksheet

7. Combining data from multiple sources

• Using workbooks as templates for other workbooks
• Linking to data in other worksheets and workbooks
• Consolidating multiple sets of data into a single workbook

8. Analyzing data and alternative data set

• Examining data by using the Quick Analysis Lens
•  Defining an alternative data set
• Defining multiple alternative data sets
• Analyzing data by using data tables
• Varying your data to get a specific result by using Goal Seek
• Finding optimal solutions by using Solver
• Analyzing data by using descriptive statistics

9. Creating charts and graphics

• Creating charts
• Customizing the appearance of charts
• Finding trends in your data
• Creating dual-axis charts
• Summarizing your data by using sparklines
• Creating diagrams by using SmartArt
• Creating shapes and mathematical equations

10. Using PivotTables and Pivot Charts

• Analyzing data dynamically by using PivotTables
• Filtering, showing, and hiding PivotTable data
• Editing PivotTables
• Formatting PivotTables
• Creating PivotTables from external data
• Creating dynamic charts by using Pivot Charts

11. Printing worksheets and charts

• Adding headers and footers to printed pages
• Preparing worksheets for printing
• Previewing worksheets before printing
• Changing page breaks in a worksheet
• Changing the page printing order for worksheets
• Printing worksheets
• Printing parts of worksheets
• Printing charts

12. Working with other Office programs

• Linking to Office documents from workbooks
• Embedding workbooks into other Office documents
• Creating hyperlinks
• Pasting charts into other Office documents

13. Collaborating with colleagues

• Sharing workbooks
• Saving workbooks for electronic distribution
• Managing comments
•  Tracking and managing colleagues’ changes
• Protecting workbooks and worksheets
• Authenticating workbooks
• Saving workbooks as web content
• Importing and exporting XML data
• Working with SkyDrive and Excel Web App

EXCEL VBA

1. Getting Started

• Introducing Visual Basic for Application
• Displaying the Developer Tab in the Ribbon
• Recording a Macro
• Saving a Macro-Enable Workbook
• Running a Macro
• Editing a macro in the Visual Basic Editor
• Understanding the Development Environment
• Using Visual Basic Help
• Closing the Visual Basic Editor
• Understanding Macro Security

2. Working with Procedures and Functions

• Understanding Modules
• Creating a Standard Module
• Understanding Procedures    
• Creating a Sub Procedure
• Calling Procedure
• Using the Immediate Window to Call Procedures
• Creating a Functions Procedure
• Naming Procedures
• Working with the code Editor

3. Understanding Objects

• Understanding Objects
• Navigating the Excel Object Hierarchy
• Understanding Collections
• Using the Object Browser
• Working with Properties
• Using the With Statement
• Working with Methods
• Creating an Event Procedure

4. Using Expressions, Variables and Intrinsic Function

• Understanding Expressions and Statements
• Declaring Variables
• Understanding Data Types
• Working with Variables Scope
• Using Intrinsic Functions
• Understanding Constants
• Using Interinsic Constants
• Using Message Boxes
• Using Input Boxes
• Declaring and Using Object Variables

5. Controlling Program Execution

• Understanding Control-of-Flow Structures
• Working with Boolean Expressions
• Using the If... End If Decision Structures
• Using the Select Case... End Select Structure
• Using the Do....Loop Structure
• Using the For...To...Next Structure
• Using the For Each....Next Structure
• Guidelines for use of control-of-Flow Structure

6. Working with Forms and Controls

• Understanding User Forms
• Using the Toolbox
• Working with User Form Properties, Events and Methods
• Understanding Controls
• Setting Control Properties in the Properties Windows
• Working with the Label Control
• Working with the Text Box Control               
• Working with Command Button Control
• Working with Combo Box Control
• Working with Combo Box Control
• Working with Frame Control
• Working with Options Control
• Working with Control Appearance
• Setting the Tab Order
• Populating a Control
• Adding Code to Control

7. Working with the PivotTable Object

• Understanding PivotTables
• Creating a PivotTable Using Worksheets Data
• Working with PivotTable Objects
• Working with the PivotTable Collection
• Assigning a Macro to the Quick Access Toolbar
• Debugging Code
• Understanding Errors
• Using Debugging Tools
• Setting BreakPoints
• Stepping through Code
• Using break Mode during Run mode
• Determining the Value of Expressions
• Handling Errors
• Understanding Error Handling
• Understanding BA’s Error Trapping Options
• Trapping Error with the On Error Statement
• Understanding the Err Object
• Writing an error-Handling Routing

EXCAL B.I WITH POWER PIVOT & POWER VIEW

1. Title

2. Microsoft business intelligence vision

• Business intelligence in three ways
• Tabular business intelligence semantic model
• Excel professional power tools

3. Excel PowerPivot

• Best things that PowerPivot brings to excel
• What will PowerPivot do for the analyst?
• New PowerPivot features
• PowerPivot and excel
• PowerPivot and SharePoint
• Importing data
• Data models
• Relationships
• Simple pivot table reports
• Calculated columns and calculated fields
• Refreshing data
• Calculations
• DAX

4. Power view

• Microsoft power view user interface
• Tables
• Tiles
• Charts
• Multiples
• Saving and sharing
• Demonstration

5. Power query

• Power query and power query formulas
• Ribbon
• Data import
• Data filtering
• Merging datasets

6. Power map

• What is power map?
• Power map ribbon
• Data preparation
• Tours and scenes

7. Concept reinforcement scenarios

• Preparation
• Scenario