Unleash the power of Excel for advanced data analysis, automation, and productivity KEY FEATURES ● Build a solid foundation in Excel by learning the basics, including the creation and formatting cells, sheets, and workbooks. ● Explore advanced Excel techniques, including formulas, data analysis, and VBA macros. ● Discover the potential of ChatGPT for Excel automation, that enables you to automate tasks and generate insightful reports. DESCRIPTION Advanced Excel is a powerful tool that goes beyond basic spreadsheet functions, allowing users to perform complex calculations, manipulate large datasets, create interactive visualizations, automate tasks, and conduct in-depth data analysis. If you want to enhance your productivity, accuracy, and decision-making skills in Excel, look no further. This book is a comprehensive guide that explores the advanced features of Microsoft Excel. From creating macros with VBA to automating tasks, working with large datasets, creating visualizations, and conducting data analysis, this book covers it all. Additionally, it also introduces ChatGPT, an AI chatbot that enhances Excel automation. With practical examples and clear instructions, this guide empowers users to maximize their productivity, efficiency, and decision-making skills in Excel. By the end of this book, you will have developed the expertise and confidence to tackle complex tasks in Excel with ease. WHAT YOU WILL LEARN ● Develop proficiency in working with large datasets. ● Conduct data analysis using powerful tools like Vlookup and Advanced Pivot Tables. ● Create impactful charts and graphs that effectively communicate your findings and insights. ● Seamlessly integrate Excel with other applications to streamline your workflows and enhance collaboration. ● Discover time-saving techniques and shortcut keys to improve your productivity and efficiency in Excel. WHO THIS BOOK IS FOR For individuals seeking to enhance their Excel skills for advanced tasks, this extensive book serves as a valuable asset. It also caters to the needs of accountants, financial analysts, business analysts, and data scientists who aspire to boost their efficiency and productivity in Excel. TABLE OF CONTENTS 1. Overview of Excel 2021 2. Cell References and Range 3. Working with Formulas and Functions 4. Data Validation 5. Protection 6. Sorting a Database 7. Filtering a Database 8. Subtotals and Data Consolidation 9. Pivot Tables 10. Conditional Formatting 11. What-if-Analysis 12. Working with Multiple Worksheets, Workbooks and Applications 13. Working with Charts 14. Creating and Recording Macros in VBA 15. Assigning Buttons to Macros 16. Functions and Subroutines in VBA 17. Conditional Statements in VBA 18. Variables and Data Types in VBA 19. Looping Structures in VBA 20. Arrays and Collections in VBA 21. Debugging and Error Handling in VBA 22. User Forms and User Input in VBS 23. Advanced VBA Techniques and Best Practices 24. Building Custom Add-ins with VBA 25. ChatGPT with Excel
Author(s): Ritu Arora
Publisher: BPB Online
Year: 2023
Language: English
Pages: 333
Cover
Title Page
Copyright Page
Dedication Page
About the Author
Acknowledgements
Preface
Table of Contents
1. Overview of Excel 2021
Introduction
Structure
Objectives
Components of the Excel window
Back stage view
Saving and sharing files online
Interacting with Excel
Working with default settings
Formatting of table
Paste special preview
Flash fills
Quick data analysis
Data mining
TAT saving technique
Conclusion
Exercise
Answers
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
Exercise
3. Working with Formulas and Functions
Introduction
Structure
Objectives
Using formulas in a worksheet
Array formula
Using functions
IF function
Nested IF
IF with AND
IF with OR
IF with NOT
Lookup functions
V Lookup
H-lookup
Example of Vlookup with Range 0 (False):
Example of Vlookup with Range Non-Zero (True)
Making V-Lookup Dynamic
Using Column function in Vlookup
Using Match function in V-Lookup
Index
Index-Match
Conclusion
Exercise
4. Data Validation
Introduction
Structure
Objectives
Trace Precedents
Trace Dependents
Setting Data Validation rules
Methods of Data Validation
Creating a list
Conclusion
Exercise
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
Exercise
6. Sorting a Database
Introduction
Structure
Objectives
Definition of sorting
Simple sort
Multilevel sort
Customized sort
Conclusion
Exercise
7. Filtering a Database
Introduction
Structure
Objectives
Filters
Auto filter
Number, text or date filters
Filtering a list using advanced filter
Filtering unique records
Conclusion
Exercise
8. Subtotals and Data Consolidation
Introduction
Structure
Objectives
Subtotals
Display Subtotal at Single Level
Displaying nested subtotal
Consolidate data
Example of consolidated data
Conclusion
Exercise
9. Pivot Tables
Introduction
Structure
Objectives
Examining Pivot Tables
Recommended Pivot Table
Creating a Pivot Table
% of Grand Total
Group items in a PivotTable
Grouping of Dates
Month Wise 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 pivot table using power pivot
Conclusion
Exercise
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
Formula with multiple conditions
Conclusion
Exercise
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
Exercise
12. Working with Multiple Worksheets, Workbooks and Applications
Introduction
Structure
Objectives
Links between different worksheets
Creating links between different software
Auditing features
Dependent and precedent cells
Workgroup collaboration
Sharing workbooks
Merging workbooks
Tracking changes
Creating hyperlink
Creating links to a different file
Conclusion
Exercise
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 my data
Chart templates
Chart filter option
Waterfall Chart
Recommendations
Sparklines
Create a Sparkline
Customize Sparklines
Change the Style of Sparklines
Conclusion
Exercise
14. Creating and Recording Macros in VBA
Introduction
Structure
Objectives
Introduction to VBA
Uses of VBA
Introduction to Macros
Creating a Macro
Adding Developer tab on 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
Exercise
15. Assigning Buttons to Macros
Introduction
Structure
Objectives
Creating buttons on quick access Toolbar
Modifying menus or buttons
Scenario 4
Creating a button on Excel worksheet
Scenario 5
Editing the recorded macros
Scenario 6
Scenario 7
Scenario 8
Practice 1
Practice 2
Conclusion
Exercise
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
Syntax to write functions
Scenario 9
Branching a Procedure
Scenario 10
Scenario 11
Scenario 12
Scenario 13
OR operator
Scenario 14
AND Operator
Conclusion
Exercise
17. Conditional Statements in VBA
Introduction
Structure
Objectives
If…End If
Example
Select Case
Example
Select Case Vs. If … End If
Conclusion
Exercise
18. Variables and Data Types in VBA
Introduction
Structure
Objectives
Variables and constants
Variable
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 WorkBook
Working with application object
Scenario 15
Scenario 16
Conclusion
Exercise
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
Scenario 18
Using For Each… Next statements
Scenario 19
Scenario 20
Scenario 21
Scenario 22
Scenario 23
Scenario 24
Auto-Executed Macros
Practice 3
Practice 4
Scenario 25
Scenario 26
Solution 26
Scenario 27
Solution
Conclusion
Exercise
20. Arrays and Collections in VBA
Introduction
Structure
Objectives
Arrays
Declaring the arrays
Using arrays
Array indexing
Declaring a dynamic array
Resizing a dynamic array
Array example
Conclusion
Exercise
21. Debugging and Error Handling in VBA
Introduction
Structure
Objectives
Errors
Error handling
Scenario 28
Solution
Error number
Scenario 29
Debugging the macro
Conclusion
Exercise
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
Solution
Conclusion
Exercise
23. Advanced VBA Techniques and Best Practices
Introduction
Structure
Objectives
Code to give initial values to the control
Code for option buttons
Code for Insert button
Code to show User Form
Add-ins
Scenario 31
Solution
Code for the Change Case form
Creating menu through code
Conclusion
Exercise
24. Building Custom Add-ins with VBA
Introduction
Structure
Objectives
Protecting your Add-ins with a password
Using Add-ins
Conclusion
Exercise
25. ChatGPT with Excel
Introduction
Structure
Objectives
Using ChatGPT with Excel
Conclusion
Exercise
Index
