ONE DAY COURSE OUTLINE
Prerequisites
An assumption is made that the learner has the skills and knowledge required to create, edit, print and chart simple worksheets.
Important: participants must know how to create formulas to gain the most from the intermediate training.
| Review | • Overview of topics covered in Basic course |
| Worksheet Techniques | • Group worksheets • Freeze rows and columns • Hide/Unhide rows and columns • Custom formats |
| Cell Referencing | • Absolute and relative cell referencing • Trace precents and formula auditing |
| Essential Functions | • IF Functions and nesting IF Functions plus IFERROR • Lookup Functions (CHOOSE, VLOOKUP) • Counting Functions (COUNT, COUNTA, COUNTBLANK, COUNTIF) • ROUND Functions (ROUND, ROUNDUP, ROUNDDOWN) • Manipulative Functions (MOD) • Date Functions (TODAY, NOW, DATE) • Financial Function (PMT) |
| Complex Functions | • Introduction to embedding functions (nesting) • Documenting formulas |
| Named Ranges | • Create and apply named ranges • Use of the Name Manager |
| Goal Seeking | • Using Goal Seek to find the correct input for the required value |
| Filling Data | • Methods to fill a series including a new feature Flash Fill |
| Excel Database Concepts | • Working with lists formatted as a Table |
| Excel Charts | • Apply charting features |
| Conditional Formatting | • Methods of formatting data according to a condition • Sparklines to show trends (mini charts) |
| Quick Analysis Tool | • Understanding Quick Analysis (2013 and later) |
| Printing large worksheets | • Setting print areas • Page breaks • Print titles • Add and edit Headers and Footers • Use of different Excel views • Strategies for printing larger worksheets |
