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

30 Dec 2024

Book a Live Class, For Free!

Accreditations & Affiliations

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

About 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)

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

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 30 Dec 2024

Upcoming Cohort

Henry Harvin® Ranks #1

Starts in 7 day. 30 Dec 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

Various Excel Functions

Our courses and Course Certificates are trusted by these industry leaders

Career Services By Henry Harvin®