Sorting and filtering 7

  • Sorting:

    Reorders whole rows by one or more keys, including by cell colour. Lec 01

  • AutoFilter:

    Header drop-downs that hide non-matching rows; filters on different columns combine with AND. Lec 01

  • SUBTOTAL:

    Totals or averages only the rows left visible after a filter, with the function chosen by a number. Lec 01

  • Wildcard:

    * stands for any run of characters, ? for one character, ~ escapes them; works on text only. Lec 01

  • Advanced Filter:

    Filter driven by a criteria range, which can also copy the results to another place. Lec 01

  • Criteria Range:

    Block of cells with exact column headers and conditions beneath; same row means AND, different rows mean OR. Lec 01

  • FILTER:

    Dynamic-array function that spills the matching rows; multiply conditions for AND, add them for OR. Lec 01

Database functions 4

  • Database Function:

    D-functions that calculate over only the records matching a criteria range, taking database, field and criteria. Lec 01

  • DAVERAGE:

    Database function returning the average of a field over the records that match the criteria. Lec 01

  • DCOUNT:

    Database function counting the numeric cells of a field in the records that match the criteria. Lec 01

  • DSUM:

    Database function adding up a field over the records that match the criteria. Lec 01

Logical functions 5

  • IF Function:

    Returns one value if a test is TRUE and another if it is FALSE. Lec 02

  • Logical Test:

    An expression Excel answers with TRUE or FALSE, usually a comparison. Lec 02

  • AND Function:

    TRUE only when all of its tests are true. Lec 02

  • OR Function:

    TRUE when at least one of its tests is true. Lec 02

  • Nested IF:

    An IF placed inside another IF's true or false slot to handle several cases or bands; also written in VBA with ElseIf. Also in Lec 08. Lec 02

Dates 14

  • Date Serial Number:

    Excel stores a date as the count of days since 30 December 1899, so dates can be added, subtracted and compared. Also in Lec 03. Lec 04

  • DATE:

    Builds a real date from year, month and day; a month above 12 rolls into the next year. Also in Lec 04. Lec 03

  • DATEVALUE:

    Converts a date written as text into a real date value. Lec 04

  • DAY:

    Extracts the day of the month from a date; companion of MONTH and YEAR. Lec 04

  • MONTH:

    Extracts the month number from a date. Lec 04

  • YEAR:

    Extracts the year from a date. Lec 04

  • TODAY:

    Returns the current date, recalculated each time the workbook opens or recalculates. Also in Lec 04. Lec 03

  • NOW:

    Returns the current date and time, recalculated like TODAY. Lec 04

  • WEEKDAY:

    Returns the day of the week as a number; the return type picks the scheme (1 = Sunday first, 2 = Monday first). Also in Lec 04. Lec 03

  • WEEKNUM:

    Returns the week of the year for a date. Lec 04

  • EDATE:

    Shifts a date forward or back by a whole number of months. Lec 04

  • EOMONTH:

    Returns the last day of the month, shifted by n months; EOMONTH(date,-1)+1 gives the first of the month. Also in Lec 04. Lec 03

  • DATEDIF:

    Hidden function giving complete years, months or days between two dates; the start must be earlier than the end. Also in Lec 04. Lec 03

  • Calendar:

    A month grid built from formulas: pick a month and year from drop-downs, then DATE, WEEKDAY and EOMONTH place each day under the right weekday. Lec 03

Time 9

  • Time as a Fraction of a Day:

    Excel stores a time as the fractional part of a day (0.5 is noon), so times can be added, subtracted and compared; multiply by 24 for hours. Lec 05

  • TIME:

    Builds a time value from hour, minute and second numbers. Lec 05

  • TIMEVALUE:

    Converts a time written as text into a time value. Lec 05

  • HOUR:

    Returns the hour component of a time as a whole number. Lec 05

  • MINUTE:

    Returns the minute component of a time as a whole number. Lec 05

  • SECOND:

    Returns the second component of a time as a whole number. Lec 05

  • Time Format:

    A number format that changes how a time is displayed (h:mm, h:mm AM/PM and so on) without changing its value. Lec 05

  • Elapsed Time Format:

    The format [h]:mm, which shows total hours without wrapping at 24. Lec 05

  • Timesheet:

    Monthly attendance sheet that converts start and stop times into regular hours, overtime and pay. Lec 05

Names, validation and protection 4

  • Named Range:

    A label for a cell or range, set in the Name Box, that formulas can use instead of the address. Lec 04

  • Data Validation:

    Rules limiting what can be entered in a cell, such as a drop-down list or a date range. Also in Lec 03. Lec 04

  • Sheet Protection:

    Review > Protect Sheet, which enforces the Locked and Hidden flags; a password is optional. Lec 05

  • Locked and Unlocked Cells:

    Per-cell flag set in Format Cells > Protection; only takes effect once the sheet is protected. Lec 05

Lookups 9

  • XLOOKUP:

    Finds a key in one range and returns the matching item from another range of the same size. Lec 06

  • Exact Match:

    Lookup mode (0) that returns a result only if the key is identical. Lec 06

  • Approximate Match:

    Lookup that falls back to the nearest key when there is no exact one, for example to place a salary in a band. Lec 06

  • Match Mode:

    XLOOKUP argument: 0 exact, -1 next smaller, 1 next larger, 2 wildcard. Lec 06

  • Lookup Table:

    Small reference table (branches to area, salary bands to bonus) that a lookup reads. Lec 06

  • INDEX and MATCH:

    Pairing where MATCH finds a position and INDEX returns the cell at that position. Lec 06

  • MATCH:

    Returns the position of a value within a range. Lec 06

  • INDEX:

    Returns the cell at a given row and column of a range. Lec 06

  • HLOOKUP:

    Exact lookup across a row instead of down a column. Lec 04

Pivot tables 10

VBA macros 8

Formula basics 6