The Advanced Excel Course 2021 is the a gateway to a world where mastery of Excel It is not just a competitive advantage, but a a key requirement in any professional sector. With the growing demand for experts able to handle large volumes of data and carry out complex analyses, to develop advanced Excel skills positions you as a a valuable asset in the labour market. This course, entirely online, offers you the the flexibility to learn from anywhere, ranging from from the basics to the use of macros and Visual Basic for Applications (VBA). Throughout the programme, you’ll immerse yourself in the graphics design, the data analysis using tools such as Solver, and the advanced Excel customisation, ensuring that you can optimise processes y make informed decisions. With our a practical and in-depth approach, you’ll be ready to take on challenges y come up with innovative solutions in your workplace, standing out as a a versatile and efficient professional.
Advanced Excel 2021
Digital Skills
30 hours
Introduction
Objectives
-
Develop advanced skills in the creating and customising graphics in Excel.
-
Learning how to use complex formulas and logical functions for the data analysis.
-
Managing and working with pivot tables to obtain summary of actual data.
-
Implement data analysis tools, such as Solver, for the decision-making.
-
Automate repetitive tasks by means of the creating and managing macros.
-
Using VBA for programme bespoke solutions in Excel.
-
Configure document security y manage access to information.
Table of Contents
TEACHING UNIT 1. BASIC CONCEPTS
Interface elements
Data entry and editing
Formatting
Working with multiple sheets
Creating graphics
Customisation
Aid: an important resource
TEACHING UNIT 2. FORMATTING DATA AND FORMULAS
Data types
Data entry
Cell references
Introduction
TEACHING UNIT 3. TABLES AND LISTS OF DATA
Initial data
Tally up and summarise
Filtering and grouping data
Pivot tables
Data tables
TEACHING UNIT 4. DATA ANALYSIS
Configuring analysis tools
Tables with variables
Functions for making forecasts
Scenario simulation
Pursuit of objectives
The Solver tool
Other data analysis tools
TEACHING UNIT 5. DATABASES
Data collection
Database editing
Database functions
XML mapping
TEACHING UNIT 6. GRAPHS AND DIAGRAMS
Graph generation
Inserting mini-charts
Customising maximum and minimum values
Inserting shapes
Images
Graphic elements and interactivity
SmartArt
TEACHING UNIT 7. DATA PUBLICATION
Printing sheets
Publish Excel workbooks
TEACHING UNIT 8. LOGICAL FUNCTIONS
Relationships and logical values
Decision-making
Nesting of expressions and decisions
Conditional operations
Selecting values from a list
TEACHING UNIT 9. DATA SEARCH
Handling references
Data retrieval and selection
Transposing tables
TEACHING UNIT 10. OTHER FUNCTIONS OF INTEREST
Text manipulation
Working with dates
Miscellaneous information
TEACHING UNIT 11. ACCESSING EXTERNAL FUNCTIONS
Registration of external functions
Function calls
Excel 4.0-style macros
Books containing macros
TEACHING UNIT 12. MACROS AND FUNCTIONS
Recording and playing back macros
Macro management
Definition of functions
TEACHING UNIT 13. INTRODUCTION TO VBA
The Visual Basic editor
The code editor
The ‘Immediate’ window
A case study
TEACHING UNIT 14. VARIABLES AND EXPRESSIONS
Variables
Expressions
TEACHING UNIT 15. CONTROL STRUCTURES. THE EXCEL OBJECT MODEL
Conditional values
Suspended sentences
Repetition structures
Key Excel objects
Other Excel objects
TEACHING UNIT 16. DATA MANIPULATION
Selecting a data table
Data manipulation
Entering new data
The complete solution
TEACHING UNIT 17. DIALOGUE BOXES
Pre-designed dialogue boxes
Customised dialogue boxes
A more attractive and user-friendly macro
Opening the dialogue box
TEACHING UNIT 18. GROUP WORK
Share a book
Notes on the data
Exchange control
Proofreading tools
TEACHING UNIT 19. DOCUMENTS AND SECURITY
Restricting access to a document
Digital security
TEACHING UNIT 20. CUSTOMISING EXCEL
Parameters applicable to books and sheets
Environment options
The ribbon
Create your own cards and groups