Course Material for
Advanced Microsoft Excel 2010
Yashwantrao Chavan Academy of Development Administration
Rajbhavan Complex, Baner Road, PUNE - 411 007, Maharashtra State, India
(For free internal distribution only. Not for sale or resale)
, About this Course Material
This course material is developed for Microsoft Excel 2010 training and will
teach y o u the subject step-by-step. With comprehensive instructions and
objectives checklists, it provides easy-to-follow hands-on exercises and a
glossary of Excel-specific terms.
When you return to your home or office, you will find this book to be a valuable
resource for reviewing exercises and applying the skills you have learned. Each
lesson concludes with review questions that review whatever you have learnt from
the lesson. Lesson review questions are provided as a study resource only.
They do not guarantee a passing score on any certification exam(s).
Course Prerequisites
This Course Material is designed for participants who possess Intermediate Level
knowledge in Microsoft Excel 2010.
, Acknowledgements and Disclaimer
This course material is a compilation of internal knowledge base and content from various
sources, websites, booklets, documents, etc. Same goes with various screen shots used in it,
and the accompanying exercise files. Most of the content and exercises have been taken from
websites like microsoft.com, excelexperts.com, excel-formulas.com, gcflearnfree.org, and
homeandlearn.co.uk. Yashwantrao Chavan Academy of Development Administration
(YASHADA) duly and thankfully acknowledges these sources.
The aim of this course material is to provide a preliminary understanding on the subjects and
areas covered as part of YASHADA's training programs. While working on Microsoft Excel, this
document should be treated only as a quick reference on the topics, and should not be treated as a
guideline and / or instructions.
The document by no means has any commercial intention and is solely compiled for the
purpose of knowledge sharing. It is meant for free of cost distribution amongst participants of
YASHADA’s training programs.
YASHADA shall have no liability for errors, omissions or inadequacies in the
information contained herein or for interpretation thereof. The reader is solely responsible for the
selection of this material to achieve its intended results.
The content provided herein is subject to change without prior notice.
, C o n t e n t s a t a G l a n c e
1 Unleash the Power of Excel with VBA
2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar?
3 Referring to Ranges
4 User-Defined Functions
5 Looping and Flow Control
6 R1C1-Style Formulas
7 What Is New in Excel 2010 and What Has Changed
8 Create and Manipulate Names in VBA
9 Event Programming
10 Userforms—An Introduction
11 Creating Charts
12 Data Mining with Advanced Filter
13 Using VBA to Create Pivot Tables
14 Excel Power
15 Data Visualizations and Conditional Formatting
16 Reading from and Writing to the Web
17 Dashboarding with Sparklines in Excel 2010
18 Automating Word
19 Arrays
20 Text File Processing
21 Using Access as a Back End to Enhance Multiuser Access to Data
22 Creating Classes, Records, and Collections
23 Advanced Userform Techniques
24 Windows API
25 Handling Errors
26 Customizing the Ribbon to Run Macros
27 Creating Add-Ins
Index
Advanced Microsoft Excel 2010
Yashwantrao Chavan Academy of Development Administration
Rajbhavan Complex, Baner Road, PUNE - 411 007, Maharashtra State, India
(For free internal distribution only. Not for sale or resale)
, About this Course Material
This course material is developed for Microsoft Excel 2010 training and will
teach y o u the subject step-by-step. With comprehensive instructions and
objectives checklists, it provides easy-to-follow hands-on exercises and a
glossary of Excel-specific terms.
When you return to your home or office, you will find this book to be a valuable
resource for reviewing exercises and applying the skills you have learned. Each
lesson concludes with review questions that review whatever you have learnt from
the lesson. Lesson review questions are provided as a study resource only.
They do not guarantee a passing score on any certification exam(s).
Course Prerequisites
This Course Material is designed for participants who possess Intermediate Level
knowledge in Microsoft Excel 2010.
, Acknowledgements and Disclaimer
This course material is a compilation of internal knowledge base and content from various
sources, websites, booklets, documents, etc. Same goes with various screen shots used in it,
and the accompanying exercise files. Most of the content and exercises have been taken from
websites like microsoft.com, excelexperts.com, excel-formulas.com, gcflearnfree.org, and
homeandlearn.co.uk. Yashwantrao Chavan Academy of Development Administration
(YASHADA) duly and thankfully acknowledges these sources.
The aim of this course material is to provide a preliminary understanding on the subjects and
areas covered as part of YASHADA's training programs. While working on Microsoft Excel, this
document should be treated only as a quick reference on the topics, and should not be treated as a
guideline and / or instructions.
The document by no means has any commercial intention and is solely compiled for the
purpose of knowledge sharing. It is meant for free of cost distribution amongst participants of
YASHADA’s training programs.
YASHADA shall have no liability for errors, omissions or inadequacies in the
information contained herein or for interpretation thereof. The reader is solely responsible for the
selection of this material to achieve its intended results.
The content provided herein is subject to change without prior notice.
, C o n t e n t s a t a G l a n c e
1 Unleash the Power of Excel with VBA
2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar?
3 Referring to Ranges
4 User-Defined Functions
5 Looping and Flow Control
6 R1C1-Style Formulas
7 What Is New in Excel 2010 and What Has Changed
8 Create and Manipulate Names in VBA
9 Event Programming
10 Userforms—An Introduction
11 Creating Charts
12 Data Mining with Advanced Filter
13 Using VBA to Create Pivot Tables
14 Excel Power
15 Data Visualizations and Conditional Formatting
16 Reading from and Writing to the Web
17 Dashboarding with Sparklines in Excel 2010
18 Automating Word
19 Arrays
20 Text File Processing
21 Using Access as a Back End to Enhance Multiuser Access to Data
22 Creating Classes, Records, and Collections
23 Advanced Userform Techniques
24 Windows API
25 Handling Errors
26 Customizing the Ribbon to Run Macros
27 Creating Add-Ins
Index
