Syllabus
Test. Train. Excel.
  
Username Password
Forgotten login details?
        

Syllabus




We believe that Excel is of huge importance in business, that it is used poorly in general but that most people could improve their proficiency level relatively easily by learning a set of core techniques. This philosophy underpins our teaching methods and learning materials and has culminated in two distinct, complementary services: training and tests.

Our syllabus is built on our analytical experience, wide-ranging research and thorough refinement to establish which of Excel's 500+ functions and techniques are most useful for business users. The 120 that made the final cut are split across five main categories: (i) Spreadsheet administration, (ii) Orientation and efficiency, (iii) Data handling, (iv) Data analysis and (v) Presentation. Each category includes a number of our 32 units and it is this highly-distilled syllabus which generates both our services.

Also available - our Core Syllabus: Excel Function List

Our Syllabus

Pre-syllabus: Fundamentals

1. The Excel Dashboard

Understanding and working with the Excel interface including: tabs, the Ribbon, the spreadsheet itself, Quick Access toolbar, and the Formula and Status bars

2. Basic Navigation & Editing

Moving, finding and selecting in a spreadsheet; making various changes to the look of cells (e.g. borders, fill) and cell contents (e.g. font colours and styles)

Spreadsheet administration: Your ability to manage and protect an Excel file and its contents

3. Customizing Excel

Changing permanent and default Excel settings including: font face, number of worksheets, frequency of autorecovery, default view, Autocorrect and Ribbon alterations

4. Housekeeping

Designing a system of file- and folder-naming; sensible folder hierarchies; keeping version control, backing up, choosing a password, and other types of spreadsheets

5. Connecting Workbooks

Referring from one workbook to another Excel workbook; updating external references when opening a workbook. Warnings: losing data through moving files and losing control through reliance on other workbooks.

6. Sharing and Protecting

How to share a file to collaborate with other users working on different machines, avoiding and resolving conflicts; protecting cells, sheets and workbooks

7. Excel Crashes

Avoiding Excel crashes in the first place by monitoring and reducing file size and complexity; troubleshooting; minimising fallout when an official crash occurs

Orientation and efficiency: Your knowledge of the basics of Excel and the efficiency with which you interact with it

8. Editing

Copying cells, rows, columns and entire worksheets; simple and 'special' pasting with associated issues; inserting cells, rows, columns and effect of using Shift to do so

9. Viewing

The two main views - Normal and Page Break Preview; freezing and unfreezing panes, tiling and arranging windows, new windows. Direct relevance to Unit 32 on Page and Print Setup

10. Outline

Grouping columns and rows (preferred to hiding); group layers and examples of good usage; subtotalling across categories and some warnings about what can go wrong with this

11. Cell References

Connecting (linking) cells and how connected cells behave; manipulating such cells; absolute references (dollar signs) for cells and columns and the "F4" shortcut

Data handling: How well you can manipulate different forms of data in Excel - sorting, cleaning and categorising it

12. Data Validation

Dropdowns to improve the integrity of your data; what's allowable; consideration of permissible values in a custom list; implementation; three levels of severity

13. Sorting & Filtering

Sorting data in various ways - quickly, 'properly', horizontally and vertically; Auto-, Multiple- and Advanced filters; alternative techniques to filtering

14. Date & Time Functions

Adding, subtracting and further manipulation of dates; NOW, TODAY; what the Serial Number is; SECOND through to YEAR functions; calculating NETWORKING days

15. Text Functions

FIND, LEN, LEFT, MID, RIGHT functions; THREE case Functions; CLEANing and TRIMming; & and CONCATENATE. This Unit is taught with a single, overarching example

16. Lookup & Reference Functions

VLOOKUPs (and HLOOKUPs); MATCHing (exactly) and INDEXing data separately; the extremely powerful INDEX-MATCH combination with warnings

17. Logical & Information Functions

TRUE, FALSE and comparison operators (<, >, =, etc); AND, OR,  ==; ISNUMBER, ISERROR, IF functions and the IF-ISERROR combo; nested functions

18. Named Ranges

The very practical benefits over normal cell references; how to set a named range up using the Name Box; using named ranges to navigate; Print Area as a named range

19. Macros

Preparation - adding the Developer tab, enabling macros, using Relative References; recording a macro with a simple formatting example explained step-by-step; running macros; editing and developing macro VBA code.

Data analysis: How well you can analyse and interpret data in Excel to produce useful results

20. Mathematical Functions

Arithmetic operations, use of brackets; various ways to sum; counting numbers, counting anything at all; SUMPRODUCT and its use in weighted averages

21. Summarizing Data

Data characteristics; Status Bar features such as AVERAGE, MAX and MIN; summing and counting conditionally over categories (SUMIF and COUNTIF), with many examples

22. Pivot Tables

Quick, efficient yet powerful analysis of large datasets; preparing source data e.g. headings; selecting the right data; changing the layout; advanced tips and pitfalls

23. Formula Auditing

Checking and understanding a spreadsheet (possibly someone else's); how to trace precedents and dependents; annotating directly in cells as an alternative to tracing

24. What-If Analysis

Excel's in-built modelling devices and their limitations in flexibility and sophistication; scenario modelling via Scenario Manager; optimisation of outcome via Goal Seek

25. Modelling Principles

What modelling is; modelling broken down into distinct stages; important principles - simplification, built-in checks; including a presentation-ready sheet

26. Modelling Techniques

Calculating growth with data points over several years; extrapolating that into the future; scenario modelling and optimisation; prioritisation (RANK); build process

Presentation: Your ability to format spreadsheets and present results within them to communicate those results powerfully

27. Cell Formatting

Good vs. Bad formatting; format painter; horizontal and vertical alignment, wrapping, merging, orientation, Alt-Return; consistent, sensible row heights & column widths

28. Number Formatting

The main formats - General, Percentage, Accounting, Comma Style; creating your own custom format from our recommendation; changing the number of decimal places

29. Conditional Formatting

How to apply conditional formatting for Greater than, Equal to, etc; limitations vs other methods; new-to-2007 relative conditions; icons, databars, etc.

30. Graphs & Charts

Examples of when to use each of the 5 main chart types; how to create them; formatting charts to requirement; converting charts to output in Excel or other applications

31. Review

Proofing spreadsheets e.g. through Spellcheck; Comments - when to use them, their limitations described, showing and hiding, printing, alternatives e.g. Notes field, track changes

32. Page & Print Setup

How to use the various Views; Page Setup - margins, orientation, scaling-to-fit a page; how to print very large sheets of data; how to repeat titles on each page