Microsoft Excel - Level 4
Upcoming Microsoft Excel Level 4 courses:
Microsoft Excel - Level 4
2 daysMicrosoft Excel - Level 4
2 daysMicrosoft Excel - Level 4
2 daysMicrosoft Excel - Level 4
2 daysMicrosoft Excel - Level 4
2 daysMicrosoft Excel - Level 4
2 daysMicrosoft Excel Level 4
Course Aim
The course will provide the delegates with the opportunity to explore the more specialised and advanced features and facilities of Excel. It will show how repetitive tasks can be automated and, using pivot tables, show how data can be further summarised and analysed.
Who Should Attend?
Everyday users with an extremely good working knowledge of Excel who want to develop their knowledge further. Should be competent at creating formulae and functions covered in the previous levels of training.
Prerequisites
Attended the Level 3 Excel course or been using Excel for a long period of time.
Course Content
Customising the Ribbon
- Creating new custom tabs and groups
- Renaming a tab, group or command button
- Adding a command button to a custom group
- Changing the symbol for a command button
- Removing a tab, group or command button
Formulae and Functions
- Text functions
- Date and time functions
Auditing Tools
- Displaying more than one formula at the same time
- Identifying the structure of calculations
- Tracing precedents and dependents
- Removing tracing arrows
Data Management
- Sorting records using a customised list
- Advanced filtering and filtering using slicers
- Performing database statistical functions
- SUMIF and COUNTIF functions
- COUNT and COUNTA functions
Analysing and consolidating information
- Using the data consolidation feature
- Consolidating data by position
- Consolidating data by category
Subtotalling Data
- Setting up outlines for rows and columns
- Creating subtotals
- Subtotal outlines
- Displaying or Hiding outline symbols
- Creating nested subtotals
- Creating multiple summary functions
- SUBTOTAL function
Working with Data
- Pick from list
- Data Validation
Conditional Formatting
- Setting up simple conditional formatting
- Using predefined conditional formatting
- Conditional formatting based on the cell content
- Conditional formatting using a calculation
- Deleting conditional formatting
Charting Worksheet Data
- Revising creating, formatting and modifying charts
- Adding additional data to a chart
- Creating combination charts
- Incorporating a secondary axis
- Adding trendlines to a chart
- Marking high and low points on a chart
- Creating sparklines
Analysing and Consolidating Data using Pivot Tables
- Creating and working with pivot tables
- Naming a pivot table database
- Adding, moving and deleting fields
- Refreshing a pivot table
- Displaying sub and grand totals
- Showing pivot tables in compact, outline or tabular format
- Grouping date fields to show other time intervals
- Changing number formats
- Using functions in a pivot table
- Showing data as a percentage of other data
- Filtering data with a slicer
- Pivot table options
- GETPIVOTDATA function
Macros
- Using macros to automate repetitive tasks
- Recording and running a macro
- Assigning a macro to the toolbar, menu or keyboard