Learning paths

Two short tracks that put the functions in a sensible order. Each step says why to learn it now and links to the full page with four worked examples.

Audit basics

Six functions that cover most of the totals, tests and lookups in an audit file. Work through them in this order.

  1. SUM
    Difficulty rating

    Total a column of amounts: the first check on any extract or trial balance.

  2. IF
    Difficulty rating

    Flag each line that fails a test, so exceptions stand out in the list.

  3. COUNTIF
    Difficulty rating

    Count how often a supplier, account or status appears, for example to find duplicate postings.

  4. SUMIFS
    Difficulty rating

    Add the amounts that meet several conditions at once, such as one customer in one month.

  5. IFERROR
    Difficulty rating

    Keep a lookup or a division from filling your report with error values.

  6. XLOOKUP
    Difficulty rating

    Pull a matching value from another list, such as the name that belongs to an account number.

Clean text from exports

Exports from accounting systems arrive with stray spaces, codes attached to names and numbers stored as text. These five functions clean that up.

  1. TRIM
    Difficulty rating

    Remove the extra spaces that make identical names or codes look different.

  2. LEFT
    Difficulty rating

    Cut a code of fixed length, such as a year or an account prefix, off the front of a text.

  3. FIND
    Difficulty rating

    Locate a separator or another character, so you know where to cut.

  4. SUBSTITUTE
    Difficulty rating

    Replace or remove characters such as dots, hyphens or currency symbols in one step.

  5. VALUE
    Difficulty rating

    Turn text that looks like a number into a real number you can add up.