Advanced Excel & VBA
These days, any managerial or analyst role requires that you work with data. Excel remains one of the most powerful and easy to use tools when it comes to crunching data. Learning Excel in a proper, structured way can not only help you save time, but it will also help you become better at your job and can give you time to focus on things and people that matter.
Advanced Excel
Introduction to Excel
- An overview of the screen, navigation and basic spreadsheet concepts
- Understanding workbooks, worksheets, rows, columns, cells various selection techniques
Entering Data
- Entering, Editing and Deleting Text, Numbers, Dates
- Using Auto Lists
- Moving and Copying data
- Inserting, Deleting and Hiding Rows & Columns
- Inserting, Deleting, Moving and Copying Sheets
- Using navigation techniques
Creating and Editing Formulae
- Concept of Formulae
- Creating Formulae, Editing Formulae
- Bodmas: Mathematical Order
- Copying Formulae
- Using Functions - Sum, Average, Max, Min, Count, Counta
- Applying Absolute (Fixed) Referencing
Formatting and Proofing
- Formatting Cells with Number formats, Font formats, Alignment, Borders, etc.
- Basic conditional formatting
- Copying and Clearing Formats
- Working with Styles
- Spell Check, Find & Replace and AutoCorrect
Range Names
- Concept and Purpose
- Naming individual or range cells
- Deleting and amending named ranges
- Using named cells/ranges in formulae
Conditional Functions & Formats and Date Calculations
- Benefits and purpose
- If Statements, Nested If
- And, Or, Not
- Combining If, And, Or, Not
- Sumif, Countif
- Conditional Formatting
- Entering Dates and Times
- Different date/time formats
- How dates are stored
- Calculating working days
- Using the DAY (), MONTH (), YEAR () functions
Formulae Auditing
- Formula Auditing Tools
- Revealing Formulae
- Tracing Precedents/Dependents
- Goto Special
Worksheet Management & Linking
- Linking sheets in the same file
- Linking different Excel files
- Using Edit, Links
- Viewing different files at once
- Saving a workspace
- Viewing different sheets at once
- Window Split
- Data consolidation
Basic List Management
- Sorting Data
- Adding Subtotals
- Auto filter
- Freeze Panes
- Group and Outline
- Data Form
Excel Charts / Graphs
- Using the Chart Wizard
- Editing and Formatting charts
- Saving custom chart types
- Setting a default chart type
Lookup & Information Functions
- Vertical Lookup (Vlookup)
- Horizontal Lookup (Hlookup)
- Match and Index
- IsText, IsNumber, IsDate , IsNull , IsErr, IsError, IsNa
Advanced List Management
- Advanced Filters
- Using List Features
- Dynamic Lists
Pivot Tables
- Using the Pivot Table Wizard
- Changing the pivot table layout
- Formatting
- Grouping items
- Inserting calculated fields
- Pivot Table Options
- Display and hide data in fields
- Lay out reports directly on worksheet
- Pivot Chart
What If Analysis
- Scenarios
- Goal Seek
- Solver
Protecting & Sharing Files
- Sharing a file
- Tracking changes
- Accepting or rejecting changes
- Applying Data validation rules
- Inserting comments
Introduction to Macros
- Purpose of Macros
- Recording macros
- Where to save macros
- Running macros:
- Custom buttons, menu items, keyboard shortcuts
EXCEL - VBA
Creating Record Macros
- Recording a Macro
- Running a Macro
- Running a Macro from the Macros Dialog Box
- Creating a Short-cut key to run a macro
- Running a Macro with a Shortcut key
- Assigning a Macro to a Menu or Toolbar
- Editing a Macro with Visual Basic
Visual Basic Editor
- Objects
- Methods
- Properties
- Programming Tools
- The Menu Bar
The Project Explorer
- Using the Project Explorer
Using Constants
- Excel Constants
- Variable Constants
Excel Objects
- Objects, Properties and Methods
- Getting & Setting Properties
- Calling Methods
- Passing Arguments
- Singular Objects & Collections of Objects
Using Visual Basic Functions
- InputBox Function
- MsgBox Function
- Using a Set Statement
Building Formular Control Structures
- If Then Decision Structures
- Logical Operators
- Select Case Decision Structures
- Case Else
- Comparison Operators with Select Case Structure
- For Loops
- Do Loops
- While...Wend Statement
Running Code
- Run mode and Design mode
- Running Code from the development environment
- Running Code from the host application
The Properties Window
- Changing a Property
Variables
- Dimensioning a variable
- Using variables in routines
- Object Variables
UserForms
- Designing & Creating Forms
- Working with Controls
- Creating Custom Dialog Boxes
- Userform Properties Methods & Events
Advanced VBA Programming Techniques
- Developing Excel Utilities with VBA
- Error Handling
- Using In-Built Excel Features in VBA
- Working with Pivot Tables
- Working with Charts
- Understanding Excel's Events
- Interacting with Other Applications
- Creating and Using Add-Ins
Developing Applications
- Working with the Ribbon
- Working with Shortcut Menus
- Providing Help for Your Applications
- Developing User Oriented Applications
Event Handling
- VBA Editing & Debugging
- Auto Macros
- Error Handling
Connecting to Database
- Connect to MS Access
- Access MS Access Database & Retrieve Data
- Create Table & Query Data Filter DB using Query
- Update and Modify DB
- Create Single and Multiple Mapping Tables
- Filter DB using Query
- Query data grouping
- Retrieve Data using Logical Access Query
Connect Excel to Outlook
- Connect to Outlook using VBA
- Query to retrieve Mail From, to and CC address
- Retrieve Mail Subject and Time
- Retrieve Mail Attachment