Microsoft Advance Excel Training

• Use the Expressions, Variables and Intrinsic Functions
• Understand the procedure of entering and editing data
• Learn to work with Forms and Controls
• Learn how to use multiple worksheets and workbooks
• Learn how to modify the a worksheet
• Learn to work with the PivotTable Object

ADVANCED EXCEL 

1. Getting started with Excel 

• Identifying the different Excel programs
• Identifying new features of Excel 
  • If you are upgrading from Excel 
  • 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 2013 professional power tools

3. Excel 2013 PowerPivot

• Best things that PowerPivot brings to excel
• What will PowerPivot do for the analyst?
• New 2013 PowerPivot features
• PowerPivot and excel 2013
• 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