Contents
Preface
Acknowledgments
About the Author
CHAPTER 1:OVERVIEW OF EXCEL 2021
Introduction
Structure
Objectives
Components of the Excel Window
Backstage View
Saving and Sharing Files Online
Interacting with Excel
Working with Default Settings
Formatting of Tables
Paste Special Preview
Flash Fill
Quick Data Analysis
Data Mining
TAT Saving Techniques
Conclusion
Exercises
CHAPTER 2:CELL REFERENCES AND RANGE
Introduction
Structure
Objectives
Using Different Types of References
Types of Cell Reference
Relative Cell Reference
Absolute Cell References
Mixed Cell Reference
Named Range
Creating a Named Range
Editing Named Ranges
Deleting Named Ranges
Conclusion
Exercises
CHAPTER 3:WORKING WITH FORMULAS AND FUNCTIONS
Introduction
Structure
Objectives
Using Formulas in a Worksheet
Array Formula
Using Functions
Example
IF Function
Example
Nested IF
Example
IF With AND
Syntax
IF With OR
IF With NOT
Lookup Functions
VLOOKUP
HLOOKUP
Making VLOOKUP Dynamic
Using the Column Function in VLOOKUP
Using the Match Function in VLOOKUP
Index
Index-Match
Conclusion
Exercise
CHAPTER 4:DATA VALIDATION
Introduction
Structure
Objectives
Trace Precedents
Trace Dependents
How to Use Trace Dependents
Setting Data Validation Rules
Methods of Data Validation
Creating a List
Conclusion
Exercises
CHAPTER 5:PROTECTION
Introduction
Structure
Objectives
Employee Information System
Protecting a Worksheet by Using Passwords
Protecting a Workbook
Protecting a Part of a Worksheet
Password Protecting a File
Conclusion
Exercises
CHAPTER 6:SORTING A DATABASE
Introduction
Structure
Objectives
Definition of Sorting
Simple Sort
Multilevel Sort
Customized Sort
Conclusion
Exercises
CHAPTER 7:FILTERING A DATABASE
Introduction
Structure
Objectives
Filters
AutoFilter
Number, Text, or Date Filters
Filtering a List Using Advanced Filter
Filtering Unique Records
Conclusion
Exercise
CHAPTER 8:SUBTOTALS AND DATA CONSOLIDATION
Introduction
Structure
Objectives
Subtotals
Display Subtotal at a Single Level
Displaying Nested Subtotal
Consolidate Data
Example of Consolidated Data
Conclusion
Exercises
Region: East
Region: West
Region: South
CHAPTER 9:PIVOT TABLES
Introduction
Structure
Objectives
Examining Pivot Tables
Recommended Pivot Table
Creating a Pivot Table
Percent of Grand Total
Group Items in a Pivot Table
Grouping of Dates
Monthly Report
Create a Graph Using Pivot Data
Weekly Report
Grouping of Numbers (Creating Slabs)
Slicer
Timeline
Power View
Power Pivot
Benefits of Data Model
Creating a Pivot Table Using Power Pivot
Conclusion
Exercises
CHAPTER 10:CONDITIONAL FORMATTING
Introduction
Structure
Objectives
Conditional Formatting
Conditional Formatting Using Cell Values (Column-based Conditional Formatting)
Conditional Formatting Using Formula (Record-based Conditional Formatting)
Icon Set
Formulas with Multiple Conditions
Apply a Conditional Formula Based on a Different Sheet’s Cell Reference
Conclusion
Exercises
CHAPTER 11:WHAT-IF ANALYSIS
Introduction
Structure
Objectives
Goal Seek
Using the Goal Seek Command
Projecting Figures Using a Data Table
One-Variable Data Tables
Two-Variable Data Tables
What-if Scenarios
Creating Scenarios
Create a Scenario Summary Report
Delete a Scenario
Display a Scenario
Merge Scenarios from Another Worksheet
Protecting Scenarios
Conclusion
Exercises
Task 1: Goal Seek
Task 2: Data Table
Task 3: Scenario Manager
CHAPTER 12:WORKING WITH MULTIPLE WORKSHEETS, WORKBOOKS, AND APPLICATIONS
Introduction
Structure
Objectives
Links Between Different Worksheets
Sheetname!Reference
Creating Links Between Different Software
Auditing Features
Dependent and Precedent Cells
Workgroup Collaboration
Sharing Workbooks
Merging Workbooks
Tracking Changes
Creating Hyperlinks
Creating Links to a Different File
Conclusion
Exercises
CHAPTER 13:WORKING WITH CHARTS
Introduction
Structure
Objectives
Creating Charts Using Chart Tools
Chart Designs
Adding Titles and Values in Charts Using Chart Tools
Formatting Charts
Charts for Data
Chart Templates
Chart Filter Option
Waterfall Chart
Recommendations
Sparklines
Create a Sparkline
Customize Sparklines
Change the Style of Sparklines
Conclusion
Exercises
CHAPTER 14:CREATING AND RECORDING MACROS IN VBA
Introduction
Structure
Objectives
Introduction to VBA
Uses of VBA
Introduction to Macros
Creating a Macro
Adding a Developer Tab on the Ribbon
Recording a Macro
Defining a Macro
Macro Storage
Macro Shortcut
Macro Description
Stop Recording
Relative Reference Macro
Scenario 1
Running Your Macro
Running the Macro by Name
Scenario 2
Scenario 3
Conclusion
Exercises
CHAPTER 15:ASSIGNING BUTTONS TO MACROS
Introduction
Structure
Objectives
Creating Buttons on the Quick Access Toolbar
Modifying Menus or Buttons
Scenario 4
Creating a Button in the Excel Worksheet
Scenario 5
Editing the Recorded Macros
Scenario 6
Scenario 7
Scenario 8
Practice 1
Practice 2
Conclusion
Exercises
CHAPTER 16:FUNCTIONS AND SUBROUTINES IN VBA
Introduction
Structure
Objectives
Writing Procedures
Visual Basic Editor
Project Explorer Keyboard Shortcuts
Inserting Modules
Writing Code Inside Modules
Sub Procedure
Macro
Function Procedure
Scenario 9
Branching a Procedure
Use If…Then...Endif
Use If...Then...Else…Endif
Use If...Then...Elseif…Then…Else…Endif OR Select Case… End
Scenario 10
Scenario 11
Scenario 12
Scenario 13
Scenario 14
Conclusion
Exercises
CHAPTER 17:CONDITIONAL STATEMENTS IN VBA
Introduction
Structure
Objectives
If…End If
Example
Select Case
Example
Select Case vs. If … End If
Conclusion
Exercises
CHAPTER 18:VARIABLES AND DATA TYPES IN VBA
Introduction
Structure
Objectives
Variables and Constants
Variables
Constant
Declaring Variables and Constants
Data Types of Variables and Constants
Using the Option Explicit Statement
Message Box and Input Box
Selecting and Activating Cells
Selecting and Activating Rows and Columns
Working with Sheets
Working with a Workbook
Working with the Application Object
Scenario 15
Scenario 16
Conclusion
Exercise
CHAPTER 19:LOOPING STRUCTURES IN VBA
Introduction
Structure
Objectives
Using Loops (Repeating Action)
Choosing a Loop to Use
Using Do…Loop Statements
Repeating Statements While a Condition is True
Checking Condition Before You Enter the Loop
Checking Condition After the Loop Has Run at Least Once
Scenario 17
Using For…Next Statements
Syntax
Scenario 18
Using For Each… Next Statements
Syntax
Scenario 19
Scenario 20
Scenario 21
Scenario 22
Scenario 23
Scenario 24
Auto-Executed Macros
Practice 3
Practice 4
Scenario 25
Scenario 26
Scenario 27
Conclusion
Exercises
CHAPTER 20:ARRAYS AND COLLECTIONS IN VBA
Introduction
Structure
Objectives
Arrays
Declaring the Arrays
Syntax
Example
Using Arrays
Array Indexing
Declaring a Dynamic Array
Syntax
Resizing a Dynamic Array
Array Example
Conclusion
Exercises
CHAPTER 21:DEBUGGING AND ERROR HANDLING IN VBA
Introduction
Structure
Objectives
Errors
Error Handling
Scenario 28
Error Number
Scenario 29
Debugging the Macro
Conclusion
Exercises
CHAPTER 22:USER FORMS AND USER INPUT IN VBS
Introduction
Structure
Objectives
User Forms
Creating User Forms
Adding Other Controls
Handling Events for the Control
Scenario 30
Conclusion
Exercises
CHAPTER 23:ADVANCED VBA TECHNIQUES AND BEST PRACTICES
Introduction
Structure
Objectives
Code to Set Initial Values for the Control
Code for Option Buttons
Code for Insert Button
Double-click Insert Button
Code to Show User Form
Add-Ins
Scenario 31
Code for the Change Case Form
Creating Menu with Code
Conclusion
Exercises
CHAPTER 24:BUILDING CUSTOM ADD-INS WITH VBA
Introduction
Structure
Objectives
Protecting Your Add-Ins with a Password
Using Add-Ins
Conclusion
Exercises
CHAPTER 25:CHATGPT WITH EXCEL
Introduction
Structure
Objectives
Using ChatGPT With Excel
Conclusion
Exercises
INDEX