Advanced Excel training course (2 days)
If you have been using Microsoft Excel for a while now, maybe it is time to move on a bit. This two-day course shows how you can use Excel to manage a database, create advanced charts, use some of the advanced functions in Excel and much more besides! Why not spend two days finding out just what Excel can do?
Advanced Excel training - Course Contents
The contents of our Advanced Excel course are as follows (any items marked with a * will be covered if time allows):
Number formatting
- Creating custom formats
- The four parts of a format
- Scaling numbers
Dates and times (*)
- How dates and times are stored
- Useful date/time functions
- Formatting dates and times
Conditional formatting
- Creating/using cell rules
- Data bars and colour sets
Styles and themes (*)
- How themes work
- Using the default styles
- Creating custom styles
Validation and protection
- Setting cell validation
- Protecting cells/worksheets
- Grouping and outlining
- Cell comments
Range names and absolute references
- Absolute references ($ symbol)
- Fixing only the row/column
- Creating range names
- Labelling ranges automatically
IF and LOOKUP functions
- The conditional (IF) function
- Nested IFs are evil
- Lookup tables
Advanced lookup functions (*)
- Using MATCH to find values
- Combining MATCH and INDEX
- Using OFFSET
- The feared INDIRECT function
Linking to other data (*)
- Linking to Access
- Linking to web sites
Multiple worksheets
- Copying, moving and renaming
- The dangerous group mode
- Summing across worksheets
- 3-D range names
|
Basic tables
- Table styles
- Using calculated columns
- Header rows and total rows
- Sorting and simple filtering
Advanced Tables
- Removing duplicates
- Advanced filters
- Creating table styles
Pivot tables
- Creating pivot tables
- Swapping rows, columns and pages
- Grouping fields
- Drill-down
Slicers (*)
- Pivot table slicers
- Changing slicer properties
Charts
- Selecting data
- Quick ways to create charts
- Formatting your chart
Advanced charts
- Creating chart templates
- Combination charts
- Picture charts
- Custom chart types
- Regression and trendlines
Sparklines (*)
- Creating sparklines
- Changing chart types
- Formatting sparklines
Consolidation (*)
- Using formulae
- Using arrays
- Using data consolidation
- Using pivot tables
Recording Macros
- Absolute/relative recording
- Where macros are stored
- Assigning macros to buttons
|
Click on any link above to show free exercises for the topic in question. Delegates should already have a good working knowledge of Excel. There will be a maximum of 6 people on each course, and each person will have their own computer.
