Course description
Most people only use a fraction of Excel’s capabilities. This workshop shows what you’ve been missing!
Upcoming start dates
Choose between 2 start dates
Outcome / Qualification etc.
- Nest formulas
- Get the most from pivot tables
- Use conditional formatting
- Write array formulas
- Explore the lookup functions
- Calculate by criteria
- Use ‘goal seek’ and ‘scenario manager’ for what-if analysis
- Record macros
Training Course Content
1 Nesting formulas
- Principles of nesting formulas together
- Using IF with AND or OR to answer questions
- Nesting an AND function in an IF
- Nesting an OR function in an IF
2 Advanced pivot table reports
- Grouping dates, numerical and text items
- Running percentage analyse
- Running analyses to compare data
- Inserting Field calculations
- Finishing off with a user-friendly dashboard
3 Advanced conditional formatting
- Colour table rows based on criteria in it
- Applying colour to approaching dates
- Exploring the different rule types
4 Lookup functions
- Going beyond the VLOOKUP function
- Lookups that retrieve data from left or right
- The versatile INDEX and MATCH functions
- Retrieving data from columns with duplicates
5 Calculate by criteria
- Using SUMIFS to sum by criteria
- Finding an average by criteria with AVERAGEIFS
- Use SUMPRODUCT to multiply then add different values
6 What-if analysis
- Use Goal Seek to meet targets
- Forecast reports with the Scenario Manager
7 Recording Macros
- Macro security
- Understanding a Relative References macro
- Recording, running and editing macros
- Saving files as Macro Enabled Workbooks
- Introduction to VBA code
- Making macros available across workbooks
- Add a macro button to the Quick Access toolbar
Course delivery details
A very practical, interactive one-day session for a maximum group size of 12. Comprehensive materials provided.
Request info
The In-House Training Company
22 South Burgundy House, The Foresters, High Street
AL5 2FB Harpenden
The In-House Training Company
We offer top-quality in-house training, in a wide range of subjects, at sensible prices. All of our programmes are delivered by independent subject specialists who also have outstanding training skills. We have rigorous standards and only work with the very...
Ads