Course Details
This page covers the technical scope of the MyExcelSkills course.
Levels
MyExcelSkills progresses users through three levels of capability:
Level 1 Foundation
Competent Excel User
- Knows some key Excel functions and is comfortable applying them in straightforward situations.
- Can create basic PivotTables and apply conditional formatting.
- Tends to encounter ‘blockers’ due to skillset limitations, requiring advice from colleagues to get the job done.
Typical Excel formula: VLOOKUP with a fixed column number
Level 2 Intermediate
Respected Excel Utilizer
- Able to combine functions to write formulas that handle more complex real-world data situations.
- Takes things beyond basic usage when working with PivotTables and conditional formatting.
- Utilizes functionality to achieve more professional results.
Typical Excel formula: INDEX/MATCH combination
Level 3 Advanced
Distinguished Excel Analyst
- Has a flexible, robust Excel skillset that can adapt to a multitude of circumstances.
- Writes formulas that are dynamic and handle errors gracefully.
- Able to deliver confident solutions when others cannot.
Typical Excel formula: SUMIFS incorporating * wildcard character
Modules
MyExcelSkills teaches and tests users across eight subject area modules.
Modules 1-4 are focussed on data preparation (cleansing and enhancement):
-
Module 1 Beyond VLOOKUP
-
Level 1:
Ensuring understanding of what lookup formulas are used for, and how to handle a simple use case (using the most universally-popular lookup function,
VLOOKUP). -
Level 2:
Recognizing the shortcomings of
VLOOKUPand progressing to more robust, flexible solutions such asINDEX/MATCHandXLOOKUP. -
Level 3:
Overcoming situations where data does not contain an obvious suitable field on which to base a lookup formula (using a helper column and
&concatenation, orCONCATENATEorTEXTJOIN).
-
Level 1:
Ensuring understanding of what lookup formulas are used for, and how to handle a simple use case (using the most universally-popular lookup function,
-
Module 2 IF Statements
-
Level 1:
Identifying situations where the
IFfunction can assist data preparation, and ensuring simple such formulas can be confidently written. -
Level 2:
Handling multiple criteria situations using
ANDandORfunctions within theIFformula. -
Level 3:
Using
IFSor nestedIFstatements to specify 3 or 4 different output values rather than just 2, and simplifying such formulas usingSWITCHwhere suitable.
-
Level 1:
Identifying situations where the
-
Module 3 Text Manipulation
-
Level 1:
Splitting consistently-formatted text strings using
LEFT,RIGHT,MIDandLEN, and modern alternativesTEXTBEFOREandTEXTAFTER. -
Level 2:
Incorporating
FINDandSEARCHinto the functions introduced in Level 1 to produce dynamic, flexible text isolation formulas capable of handling less clean-cut text strings. -
Level 3:
Overwriting values using
SUBSTITUTEorREPLACE, removing leading/trailing spaces withTRIMand considering the order when chaining text functions.
-
Level 1:
Splitting consistently-formatted text strings using
-
Module 4 Cell Reference Control
-
Level 1:
Controlling the ‘anchoring’ of cell references horizontally and vertically using the
$sign (relative, absolute and mixed references). - Level 2: Defining names for simple fixed cell ranges and referring to them in formulas.
- Level 3: Swapping single-cell references to ranges to convert traditional formulas to modern dynamic array formulas.
-
Level 1:
Controlling the ‘anchoring’ of cell references horizontally and vertically using the
Modules 5-8 are focussed on data presentation (analysis and summarization):
-
Module 5 PivotTables
- Level 1: Creating basic PivotTables to summarize datasets across different time periods and categorizations (Sum, Count, Average).
- Level 2: Furthering PivotTable capability with sorting and filtering techniques to create ‘Top 10’ type reports with qualifying criteria such as minimum spend.
- Level 3: Controlling the structure of the PivotTable (tabular vs outline form, row repeats, subtotals etc.) and applying rolling totals for Pareto type analysis.
-
Module 6 SUMIFS, COUNTIFS etc.
-
Level 1:
Introducing simple use of
SUMIFS,COUNTIFS,AVERAGEIFS,MINIFSandMAXIFS. -
Level 2:
Building more complex formulas with the functions introduced in Level 1, by for example concatenating notation like
">="with cell values using the&operator. -
Level 3:
Utilizing wildcard characters like
?and*to extract insight from less clean-cut data.
-
Level 1:
Introducing simple use of
-
Module 7 Conditional Formatting
- Level 1: Implementing straightforward highlighting of certain values/occurrences in the data.
-
Level 2:
Incorporating formulas to highlight cells based on multiple criteria in a single conditional formatting rule (
AND,OR). - Level 3: Highlighting changes in different versions of datasets (for example, latest vs previous) by referencing other cells elsewhere in the workbook when composing conditional formatting rules.
-
Module 8 Professional Touches
- Level 1: Following a series of good practice guidelines for saving workbooks in a good state for others to open.
-
Level 2:
Handling errors gracefully with functions such as
IFERROR, and understanding the implications for dependent formulas. - Level 3: Protecting worksheets and workbooks by locking non-input cells, preventing structural changes and encrypting/password-protecting the file.
Underlying Principles
In delivering the content detailed above, some fundamental underlying principles are emphasised throughout:
- Keep it simple—encouraging users towards efficient, readable Excel formulas
- Modern best practices—embracing the latest Excel formulas and functionality, whilst flagging backwards compatability implications for users of earlier versions
- Holistic capability—signposting the interlinked nature of the course modules to develop rounded technique
- Data separation—insisting that the dataset resides in a dedicated worksheet and is analyzed/summarized in another
- Futureproofing—promoting solutions that are less likely to ‘break’ further down the line