Excel 365 – Advanced

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
- Assigning a data type
- Determining the data type
Data entry
- Entering text and numbers
- Entering dates and times
- Entering formulae
- Repeated entry of the same data
- Sequence generation
- The enhanced clipboard in Office
Cell references
- Multiple references and range references
- Two-dimensional, three-dimensional and other coordinates
- References and names
- Data validation
Introduction
- Custom formats
- Conditional formatting
- AutoFormat

TEACHING UNIT 3. TABLES AND LISTS OF DATA
Initial data
Tally up and summarise
- Total
- Planning
Filtering and grouping data
- Creating subtotals and groups
- Creating diagrams
- Data filtering
- Advanced filters
Pivot tables
- Designing a pivot table
- Customisation of elements
- Inclusion of additional fields
- Pivot table reports
- Dynamic graphics
- Using data from a pivot table in formulas
Data tables

TEACHING UNIT 4. DATA ANALYSIS
Configuring analysis tools
Tables with variables
- Table with one variable
- Table with two variables
Functions for making forecasts
Scenario simulation
- Scenario creation
- Use of the scenarios
Pursuit of objectives
The Solver tool
- Applying restrictions
- Reports and scenarios
- Resolution options
- Applications of Solver
Other data analysis tools
- Descriptive statistics
- Creating a histogram

TEACHING UNIT 5. DATABASES
Data collection
- Data sources
- Text files
- Data tables
- Database queries
- Connection parameters
- Online enquiries
- Microsoft Query
- Data properties
- Updating data
Database editing
Database functions
XML mapping

TEACHING UNIT 6. GRAPHS AND DIAGRAMS
Graph generation
- Creating the chart
- Areas of the graph
- Customisation of elements
Inserting mini-charts
Customising maximum and minimum values
Inserting shapes
- Using the controllers
- Text boxes
- Outlines and fills
- Shadows, edges, reflections and 3D rotation
- Compositions
Images
- Online image searches
- Image editing
Graphic elements and interactivity
SmartArt
- The Text Panel
- SmartArt format

TEACHING UNIT 7. DATA PUBLICATION
Printing sheets
- Selecting the data to be printed
- Page breaks
- Headers and footers
- Preview
Publish Excel workbooks
- Direct delivery to one or more recipients
- Web publication

TEACHING UNIT 8. LOGICAL FUNCTIONS
Relationships and logical values
- Comparison of qualifications
- Complex expressions
Decision-making
- Using decision-making to avoid mistakes
Nesting of expressions and decisions
Conditional operations
Selecting values from a list

TEACHING UNIT 9. DATA SEARCH
Handling references
- Number of rows and columns
- Addresses and non-addresses
- Offset of references
Data retrieval and selection
- A meeting of common ground
- Direct selection of a piece of data
- Searching by rows and columns
- THE XLOOKUP function
Transposing tables

TEACHING UNIT 10. OTHER FUNCTIONS OF INTEREST
Text manipulation
- Codes and characters
- Joining chains
- Character extraction
- Find and replace
- Conversions and other operations
Working with dates
- Information functions
- Operational functions
Miscellaneous information

TEACHING UNIT 11. ACCESSING EXTERNAL FUNCTIONS
Registration of external functions
- Macro sheets
- Registration process
Function calls
- Retrieving a function’s identifier
- Calls with automatic call logging
Excel 4.0-style macros
Books containing macros

TEACHING UNIT 12. MACROS AND FUNCTIONS
Recording and playing back macros
- A macro to insert subtotals
- Running the macro
Macro management
- Step-by-step modification and monitoring
- Macros and security
Definition of functions

TEACHING UNIT 13. INTRODUCTION TO VBA
The Visual Basic editor
- Project management
- Editing properties
The code editor
- Examining objects
The ‘Immediate’ window
A case study
- Workbooks, sheets, cells and ranges
- Drawing boxes
- Changes to the names of the sheets

TEACHING UNIT 14. VARIABLES AND EXPRESSIONS
Variables
- Defining variables
- The default type
- Matrices
- Issues relating to the scope
Expressions

TEACHING UNIT 15. CONTROL STRUCTURES. THE EXCEL OBJECT MODEL
Conditional values
Suspended sentences
- If/Then/Else
- Select Case
Repetition structures
- Loops by counter
- Conditional loops
- Browsing collections
Key Excel objects
- The app
- The book
- The leaf
Other Excel objects

TEACHING UNIT 16. DATA MANIPULATION
Selecting a data table
- Cells, ranges and selections
- Selection and activation
- Travel
- Selecting the table
Data manipulation
Entering new data
The complete solution

TEACHING UNIT 17. DIALOGUE BOXES
Pre-designed dialogue boxes
- How to display a dialogue box
- Confirmations and requests for information
Customised dialogue boxes
- Add a form to the project
- Working with components
- Order of access to components
A more attractive and user-friendly macro
Opening the dialogue box
- Adapting the process to the options

TEACHING UNIT 18. GROUP WORK
Share a book
Notes on the data
Exchange control
- Activation of the gear-shift control
- Summary of changes
- Review of the changes
Proofreading tools

TEACHING UNIT 19. DOCUMENTS AND SECURITY
Restricting access to a document
- Book protection
- Leaf protection
- Password-protected ranges
- Protection of other aspects
Digital security
- Obtaining a digital certificate
- Digital signature

TEACHING UNIT 20. CUSTOMISING EXCEL
Parameters applicable to books and sheets
- Default attributes for new books
- Options for books and individual sheets
Environment options
- The Quick Access Toolbar
The ribbon
Create your own cards and groups