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