Name: Introduction To VBA in Advance Excel Course in Boston | Henry Harvin
Uploaded: 2023-05-23 21:31:24

Module 1: Introduction to MS Excel
- Advantages of using MS Excel & Various purpose to use Excel
- Understanding of Workbook, Worksheet, columns, rows & Cells
- Ribbon & Tabs
- Quick access bar, Formula bar, Name bar, Status bar
- Keyboard Shortcuts & Navigation while using large data
- Different Use of Mouse
- Insert Comment
- Quiz Module 1
Module 2: Working With Tables
- Advantage of Converting data into tables
- Structured formula
- Use of Slicers
- Dynamic Range
- Quiz Module 2
Module 3: Understanding Cell References
- Absolute Reference
- Relative Reference
- Mixed Reference
- 3D Reference
- Quiz Module 3
Module 4: Basics Functions of Excel
- Text to Column
- Flash Fill
- Auto Fill
- Quiz Module 4
Module 5: Sorting Data
- In Ascending & Descending Order
- Sort with Text, Numbers & Date
- Sort with formatting (Cell, Color, Font Color)
- In Multiple Order
- In Custom Order
- Quiz Module 5
Module 6: Filtering Data
- Applying Filters by text, numbers, or date on the Data
- Advanced Filter: Setting up Criteria to Filter the data
- Applying Complex Criteria
- Clearing the existing criteria
- Use Wildcard characters in Filter Criteria
- Quiz Module 6
Module 7: Data Consolidation/ Basics of Formula/ Functions
- Form multiple Sheets
- Form multiple Files
- Understanding Syntax, Intellisense, Suggestion box & optional argument
- Basic Calculations & BODMAS Rule
- Calculate Square, cube & Raise Number to Power
- Quiz Module 7
Module 8: If Functions and Test Functions
- Simple If
- Combination of If with OR, AND & NOT
- Nested If Function
- LEN, TRIM, LEFT, RIGHT, MID, FIND, SEARCH, CONCATENATE, UPPER, LOWER, PROPER
- REPLACE, SUBSTITUTE, SEARCH, FIND
- Quiz Module 8
Module 9: Working With Dates, Countifs and Sumifs
- Date & Time Formula
- COUNTIF, COUNTIFS, SUMIF, SUMIFS
- Quiz Module 9
Module 10: Lookup and information Functions
- Lookup Functions - VLOOKUP. HLOOKUP. LOOKUP
- Disadvantage / Limitations of Vlookup
- Reference Functions - MATCH INDEX
- Simple XLookup
- Exact Match
- Approx Match
- Nested XLookup
- Case Sensitive Xlookup
- Multiple Answer column Xlookup
- Lookup Repeating Value (Using Filter)
- Quiz Module 10
Module 11: Using Named Ranges
- Defining and Using Range Names
- Using Named Ranges in Functions
- Quiz Module 11
Module 12: Data Validation
- By Numbers, Text Length, Date, List (dropdown)
- Input Message and Error Message
- Dependent Data Validation
- Dynamic Dropdown range with Table
- Quiz Module 12
Module 13: Dynamic Array Formula
- Spilled Ranges & use of #
- Sort
- Sort by
- Unique
- Filter
- Sequence
- Ifs
- Quiz Module 13
Module 14: Using Conditional formatting
- Applying Conditional Formatting
- Applying Multiple Rule Sets
- Creating Custom Rule Sets
- Viewing and Managing Rules
- Clearing Custom Rules
- Quiz Module
- Quiz Module 14
Module 15: Macros (only Recording, non VBA)
- Displaying the Developer Tab
- Review and Purpose of Macros
- Where to Save Macros
- Absolute and relative record
- Running macros: Assigning to Quick Access Toolbar, shapes
- Pictures and keyboard shortcuts
- Macro recording with charts & sorting Data
- Alter the recorded code
- User-Defined Function (UDF)
- Use pre built VBA code
- Quiz Module 15
Module 16: Summarizing Data With Pivot Tables
- Creating Pivot Tables
- Rearranging Pivot Table Data
- Modifying Pivot Table calculations
- Drill-Down
- Filtering the Pivot data
- Summarizing Values by Sum. Count. Average. Max. and Product
- Show Values As % of Grand Total, % of Column Total, % of Row Total
- Grouping Fields
- Creating Calculated Column(s) in Pivot
- Creating Calculated Item(s) in Pivot
- Showing and Hiding the Grand Totals
- Refreshing data in pivot table
- Changing the scope of data source
- Quiz Module 16
Module 17: Working With Slicers
- Inserting and Using a Slicer
- Renaming the Slicer
- Changing Slicer Settings
- Formatting a Slicer
- Clearing the Slicer
- Quiz Module 17
Module 18: Power Pivot / Power Query
- Enable PowerPivot
- Understand Import Data, Transform data, Data Model to create a report
- Use Load, Load to & Transform Data
- Use of Power query to record steps for all files from a folder
- Add column, Update Column with Text, Numbers, & date
- Quiz Module 18
Module 19: Charts
- Selecting data
- Formatting your Chart
- Understanding Different Types of Charts
- Combo Chart
- Creating Custom chart types
- Use of Secondary Axis
- Using Trend Line in Charts
- Quiz Module 19
Module 20: Other Topics
- Format Painter
- Freeze Pans
- Removing Duplicates
- Transposing Data
- Paste Special
- Print Settings
- Custom Formatting
- Quiz Module 20
Module 21: Excel Dashboard
- Objective to Create Dashboard
- Create Pivot Table & Charts
- Design Dashboard
- Use Macro to Automatically Refresh all when row data is changed or new data is added
- Quiz Module 21
Module 22: Protecting and Security
- Password Protect workbook (file) for open & modify
- Password Protect Sheet
- Password protect workbook structure (Review => protect Sheet)
- Allow Edit Range with password
- Quiz Module 22
Module 23: Introducing Visual Basic for Applications
- What is VBA?
- What is a Macro?
- What can Macros do?
- To use or not to use Macros?
- Macro – The 5 Tenets
- What is the Excel Object Model?
- Your Personal.XLSB file
- Need to check your security options
- How do I access VBA?
- Macro Security Settings
- Displaying and reviewing the Developer Tab in the Ribbon
- Quiz Module 23
Module 24: Modules and Procedures
- Modules and Procedures
- Program design and concepts
- A Good Spreadsheet Application
- Code Format / Layout
- To Dim or Not to Dim? In other words Why Dim?
- How to Declare a Variable / Dim / Private /Public
- Understanding Constants and how to Declare them
- Run Timing Test Macro
- Modules – Understanding how to Create,
- Name, Edit, Copy and Remove
- Practical on Modules
Module 25: Controlling Flow and Make Decision
- How to work with Loops & Conditional Statements
Module 26: Using Message Boxes, Input Boxes, and Running Macros
- Creating and Using Message Boxes
- Types Of Message Box
- Using Input Box
- Running a Macro from within Excel
- Assigning a Keyboard Shortcut to a Macro
- Assigning and launching a Macro from a shapes
Module 27: Working With Cells and Ranges
- Inserting Rows & Columns
- Hiding Rows & Columns
- Deleting Rows & Columns
- Height & Width, Autofit
- Formatting
Module 28: Working with Rows & Columns
- Inserting Rows & Columns
- Hiding Rows & Columns
- Deleting Rows & Columns
- Height & Width, Autofit
- Formatting
Module 29: Working With Sheets
- Adding Sheets
- Deleting Sheets
- Hiding & Unhiding Sheets
- Activating Sheets
- Protecting Sheet

VBA in Advance Excel Course in Boston

25 Nov 2024

32 Hours

25 Nov 2024

32 Hours

Book a Live Class, For Free!

Accreditations & Affiliations

Know the complete offerings of our VBA in Advance Excel Course in Boston

Key Highlights

32 Hours of Instructor-Led Sessions

11 Hours of Live Interactive Doubt Solving Sessions

6 Hours of Live Master Sessions by Industry Experts

192 Hours of Self-Paced Learning

Job-Ready Portfolio of 3 Capstone Projects

172 Auto-Graded Assessments

6+ Industry Case Studies

48 Guided Hands-On Exercises

3+ Assignments and Mini Projects

1 Mock Interview and 1 Hackathon

Get 1-Year Gold Membership of Henry Harvin® School of Accounts

Get a guaranteed Internship with Henry Harvin® and in top MNCs like J.P. Morgan, Accenture & many more via Forage

Get 3 in 1 Placement support through Placement Drives, Premium access to Job portal & Personalized Job Consulting

2 Hours of Mentorship by Industry Experts

Earn Certification of Course from Henry Harvin®, Govt of India recognized & Award-Winning Institute and NSDC Certification

Our Placement Stats

Access the best jobs in the industry

You Get 10-In-1 Program

About the VBA in Advance Excel Course in Boston

What is VBA in Advance Excel?

Who Should Attend?

AGOTA™ Framework

Trainers at Henry Harvin®

Alumni Status

Gold Membership Benefits

Learning Benefits

Projects covered (Hands-on Practical Experience)

Curriculum For VBA in Advance Excel Course in Boston

Module 1: Introduction to MS Excel

Module 2: Working With Tables

Module 3: Understanding Cell References

Module 4: Basics Functions of Excel

Module 5: Sorting Data

Module 6: Filtering Data

Module 7: Data Consolidation/ Basics of Formula/ Functions

Module 8: If Functions and Test Functions

Module 9: Working With Dates, Countifs and Sumifs

Module 10: Lookup and information Functions

Module 11: Using Named Ranges

Module 12: Data Validation

Module 13: Dynamic Array Formula

Module 14: Using Conditional formatting

Module 15: Macros (only Recording, non VBA)

Module 16: Summarizing Data With Pivot Tables

Module 17: Working With Slicers

Module 18: Power Pivot / Power Query

Module 19: Charts

Module 20: Other Topics

Module 21: Excel Dashboard

Module 22: Protecting and Security

Module 23: Introducing Visual Basic for Applications

Module 24: Modules and Procedures

Module 25: Controlling Flow and Make Decision

Module 26: Using Message Boxes, Input Boxes, and Running Macros

Module 27: Working With Cells and Ranges

Module 28: Working with Rows & Columns

Module 29: Working With Sheets

Admission Closes On 25 Nov 2024

Upcoming Cohort

Henry Harvin® Ranks #1

Starts in 3 day. 25 Nov 2024

Upcoming Cohorts

Skills Covered

Certifications

Get Ahead with Henry Harvin® VBA in Advance Excel Course in Boston Certification

What you'll Learn in this course

Fundamentals of Visual Basic Analysis

Creating Custom Forms, Graphs, and Reports

VBA Programming & Formulas

Handling Complex Tasks with Excel Features

Use of Pivot Tables for Data Analysis