Excellency AcademyOpen the app

Administration and Executive Assistant

110 lessons across 14 tiers. Start at Tier 0 with the basics, then work up.

Tier 0

  1. The Grid: Navigation at SpeedGrid navigation, Go To dialog, keyboard shortcuts
  2. Entering Data So Excel Understands ItCell formatting, data types, entry prefixes
  3. File Formats: When to Use WhatSave As, file format options
  4. Recovery: Undo, Backups and AutosaveUndo, AutoSave, version history, recovery options
  5. Printing Something LegiblePage Setup, print area, page breaks
  6. Orientation: Your First HourRibbon, backstage, search, customization
  7. Bold, Italic and Underline for a Simple ReportBold, Italic, Underline
  8. Alignment and Wrap TextHorizontal Alignment, Wrap Text
  9. Font Colour and Background FillFont Colour, Background Fill
  10. Insert and Delete Rows and ColumnsInsert Row, Delete Column
  11. Copy, Cut and Paste SpecialCopy, Cut, Paste Special

Tier 1

  1. Operator Precedence and ParenthesesOperator precedence, formula evaluation
  2. Relative References: One Formula, Filled DownRelative references, fill down, order of operations
  3. SUM and COUNT FamiliesSUM, SUMIF, COUNT, COUNTA, COUNTIF, SUMIFS, COUNTIFS, SUMPRODUCT
  4. Rounding and Number FormatsROUND(), ROUNDUP(), ROUNDDOWN(), number formats, custom formats
  5. Fill and Flash FillFill Down, Fill Series, Flash Fill, fill handle
  6. Sorting Safely Without Breaking LinksSort dialog, custom sort, sort keys
  7. AutoFilter: The 80/20 ToolAutoFilter, filter dropdowns, filter criteria
  8. Choosing an Honest ChartColumn, Line, Pie, Scatter charts, axis scaling
  9. Introduction to FormulasFormulas, function syntax, formula bar, evaluation
  10. Lock It With $: Fill Down and Across$A$1, $A1, A$1, F4, fill down and across
  11. Stock Value: Quantity Times CostMultiplication, fill down, SUM, SUMPRODUCT

Tier 2

  1. Structured Tables and Named RangesTable feature, table references, named ranges, Define Name
  2. Data Validation and Dependent DropdownsData Validation dialog, INDIRECT(), dependent lists
  3. Conditional Formatting with Formula RulesConditional Formatting, formula rules, color scales, data bars
  4. Text Cleaning: TRIM, SUBSTITUTE, FINDTRIM, SUBSTITUTE, FIND, SEARCH, REPLACE, UPPER, LOWER, PROPER
  5. Text Splitting and JoiningTEXTSPLIT, TEXT, CONCATENATE, &, TEXTJOIN
  6. How Excel Stores DatesDATE(), DATEVALUE(), TODAY(), serial number system
  7. Date Maths with South African HolidaysNETWORKDAYS, NETWORKDAYS.INTL, date arithmetic
  8. IF, IFS, and SWITCHIF, IFS, SWITCH, AND, OR, NOT
  9. SUMIFS, COUNTIFS, and MAXIFS FamilySUMIFS, COUNTIFS, MAXIFS, MINIFS, AVERAGEIFS, AVERAGEIF
  10. Working with Real Data: Messy ImportsText to Columns, Remove Duplicates, Find & Replace, formulas
  11. Reorder Points with IFIF, comparison operators, COUNTIF

Tier 3

  1. VLOOKUP and Why It BreaksVLOOKUP, IFNA
  2. INDEX and MATCHINDEX, MATCH
  3. XLOOKUPXLOOKUP, XMATCH
  4. Band LookupsVLOOKUP with TRUE, XLOOKUP match_mode -1, MATCH type 1
  5. Error Handling That Teaches Rather Than HidesIFNA, IFERROR, ISNUMBER, ISBLANK, AVERAGE
  6. PivotTables, Part 1: Summarise by Two FieldsPivotTable, SUMIFS, mixed references
  7. PivotTables, Part 2: Grouping and Percent of TotalPivotTable grouping, Show Values As, EOMONTH, SUMIFS
  8. Slicers and TimelinesSlicers, Timelines, Report Connections
  9. Building a Report Someone ReadsLayout, number formats, conditional formatting, freeze panes
  10. Stock Count Variances with XLOOKUPXLOOKUP if_not_found, SUMIFS, variance

Tier 4

  1. UNIQUE for a Genuine Distinct CountUNIQUE, ROWS, SUMPRODUCT
  2. SORT and SORTBY Wrapped in INDEXSORT, SORTBY, INDEX, MATCH, MAX
  3. FILTER for Multi-Condition Counts and TotalsFILTER, ROWS, SUM
  4. LET for Readable, Reliable FormulasLET
  5. LAMBDA for a Reusable Custom FunctionLAMBDA
  6. TEXTBEFORE and TEXTAFTER for One-Step SplittingTEXTBEFORE, TEXTAFTER, TEXTSPLIT
  7. REGEXEXTRACT for Pattern-Based ExtractionREGEXEXTRACT
  8. PMT and Loan Repayment MathsPMT, FV, NPV

Tier 5

  1. NPV and the Period-Zero TrapNPV
  2. IRR vs NPV for Ranking ProjectsIRR, NPV
  3. IPMT and PPMT: Splitting a Loan PaymentIPMT, PPMT, PMT
  4. Straight-Line vs Declining-Balance DepreciationSLN, DDB
  5. Break-Even AnalysisROUNDUP, basic arithmetic
  6. Payback Period from Cumulative Cash FlowMATCH, cumulative sums
  7. Financial Ratios from a Mini StatementBasic arithmetic, structured references
  8. NPER: How Long to Reach a Savings GoalNPER

Tier 6

  1. MEDIAN vs AVERAGE with an OutlierMEDIAN, AVERAGE
  2. Measuring Consistency with STDEV.SSTDEV.S
  3. Quartiles with QUARTILE.INCQUARTILE.INC
  4. Forecasting with FORECAST.LINEARFORECAST.LINEAR
  5. Ranking with RANK.EQRANK.EQ
  6. Probability with NORM.DISTNORM.DIST
  7. Cutoffs with NORM.INVNORM.INV
  8. SUBTOTAL Function NumbersSUBTOTAL

Tier 7

  1. DATEDIF Argument OrderDATEDIF
  2. NETWORKDAYS.INTL Weekend PatternsNETWORKDAYS.INTL
  3. WORKDAY vs Calendar DaysWORKDAY
  4. EDATE for Calendar MonthsEDATE
  5. Splitting Text with TEXTSPLITTEXTSPLIT
  6. Joining Text with TEXTJOINTEXTJOIN
  7. MAP: Sum of SquaresMAP, LAMBDA
  8. TAKE: Most Recent vs FirstTAKE

Tier 8

  1. SCAN: Running BalanceSCAN, LAMBDA
  2. CHOOSECOLS Column OrderCHOOSECOLS
  3. TOCOL and Blank CellsTOCOL
  4. BYROW: Per-Person TotalsBYROW, LAMBDA
  5. MROUND vs ROUNDMROUND
  6. COVARIANCE.PCOVARIANCE.P
  7. T.TEST TailsT.TEST
  8. NUMBERVALUE and Number FormatsNUMBERVALUE

Tier 9

  1. EXPAND's pad_with ArgumentEXPAND
  2. DROP's Sign ConventionDROP
  3. WRAPROWS WidthWRAPROWS
  4. REDUCE's Initial ValueREDUCE, LAMBDA
  5. TRIMMEAN vs AVERAGETRIMMEAN
  6. VALUETOTEXT Strict FormatVALUETOTEXT
  7. CONCAT vs "&" on a RangeCONCAT
  8. ISOWEEKNUM at a Year BoundaryISOWEEKNUM

Tier 10

  1. Bold for EmphasisBold
  2. Horizontal AlignmentHorizontal Alignment
  3. Font Colour for Negative ValuesFont Colour
  4. Background Fill for a Status HighlightBackground Fill
  5. Wrap Text and Vertical AlignmentWrap Text, Vertical Alignment
  6. Underline for a Subtotal BreakUnderline
  7. Font Size for a Report TitleFont Size
  8. A Complete Report Header StyleBold, Horizontal Alignment, Font Size

Tier 11

  1. Protect a Finished ReportProtect Sheet
  2. Protect a Tracker, but Allow SortingProtect Sheet - Allow Sort
  3. Protect a Tracker, but Allow FilteringProtect Sheet - Allow Filter
  4. Protect a Template, but Allow New RowsProtect Sheet - Allow Insert Row
  5. Knowing When Not to ProtectProtect Sheet - When Not To
  6. Protect a Master List with Two Deliberate ExceptionsProtect Sheet - Multiple Exceptions

Tier 12

  1. Block Duplicate EntriesData Validation - Custom Formula
  2. Cap a Request Against a Live BudgetData Validation - Custom Formula
  3. Restrict a Date to Weekdays OnlyData Validation - Custom Formula
  4. Require Each Entry to Exceed the One Before ItData Validation - Custom Formula

Tier 13

  1. Capstone: Build a Complete Expense ReportSUMIF, Cell Formatting, Data Validation, Protect Sheet

Start this track