Video summary

Excel 365 Beginner to Advanced - 12 Hours

Main summary

Key takeaways

Educational

Main ideas & lessons conveyed

Course purpose and structure (Excel 365 Beginner → Advanced → intermediate/advanced)

  • The instructor introduces Excel 365 as Microsoft’s “evergreen” version within Microsoft 365, emphasizing:
    • Always having the latest features
    • Accessing Excel via a web portal (online Excel) and optionally on-the-go
    • Collaboration features like real-time co-authoring
  • The training is organized into short lessons (5–10 minutes) in logical order:
    • Lesson files are provided per section, with start and finish files
    • Exercise tasks exist at the end of each section for practice
  • A second part of the subtitles shifts to intermediate and later advanced material, increasing complexity from formatting and formulas to dynamic arrays, Power Pivot, and Power Query.

Key concepts and methodologies explained

1) Using Excel online (portal) vs desktop

  • Access Excel through office.com (log in using Microsoft 365 credentials).
  • The Office hub provides:
    • Search across Microsoft 365 content
    • App launcher to open Excel online
    • File organization features like favorites, recently opened, filters
  • Online Excel behavior:
    • Many actions supported, but some complex desktop-only features may be missing
    • Autosave is automatic (no explicit Save button; Save As used for renaming)
    • Cloud saving occurs to OneDrive; reopening the workbook shows updated changes

2) Excel interface fundamentals (desktop)

  • Terminology:
    • A worksheet is a tab (e.g., “Sheet1”)
    • A workbook contains one or more worksheets
  • Interface tour highlights:
    • Title bar (file name)
    • Ribbons grouped into tabs: Home, Insert, Formulas, Data, Review, etc.
    • Groups on ribbons with a “diagonal arrow” to access more options
    • ScreenTips for commands
    • Quick Access Toolbar (customizable with frequent commands)
    • Name box (selected cell address), formula bar (formulas)
    • Worksheet grid: rows and columns (cells referenced like H8)
    • Status bar (undo/redo + view controls + sometimes sum/avg/min/max)
    • Zoom slider
  • Contextual UI:
    • Contextual menus change based on the clicked item type (text vs picture)
    • Contextual ribbons appear for objects like tables and pictures
    • Panes can open (e.g., Format Picture) depending on selection

3) Quick Access Toolbar customization (instructions)

  • Add commands from ribbon:
    • Right-click a command → Add to Quick Access Toolbar
  • Add via dropdown:
    • Quick Access Toolbar dropdown → common commands list
  • Remove commands:
    • Right-click → Remove
    • Or deselect in the list
  • Add “More Commands” if command not on ribbon:
    • Dropdown → More Commands… (Excel Options)

4) Keyboard shortcuts for efficiency (concept + examples)

  • Shortcut usage is framed as productivity improvement:
    • Use Ctrl/Shift combinations for selection, copy/paste, undo, formatting
  • Examples shown:
    • Select all: Ctrl + A
    • Select range within table: Ctrl/Shift + arrow keys to select rows/columns
    • Copy/Paste: Ctrl + C, Ctrl + V
    • Undo: Ctrl + Z
    • Bold/Italic/Underline: Ctrl + B, Ctrl + I, Ctrl + U
  • Discover shortcuts via:
    • ScreenTips on ribbon commands
    • Alt key tips (press Alt to reveal keytip letters/numbers)
    • F1 help search for shortcut lists
    • Optional: using ChatGPT to generate lists and copy into Word online
  • Exercise practice reinforces:
    • Identify interface elements
    • Customize Quick Access Toolbar with suggested commands
    • Use the Alt key to display keytips

Data entry, formatting, and worksheet design

5) Entering text, numbers, and dates (rules/quirks)

  • Navigation while entering:
    • Tab moves to the right cell
    • Enter moves down
  • Edit modes:
    • Double-click cell or F2
    • Ctrl + Enter exits edit mode but stays in the same cell
  • Date quirks:
    • Dates are numbers with formatting applied
    • If formatted as “General,” dates appear as serial numbers
    • Fix by applying Short Date/Long Date
  • Leading zeros in numbers:
    • Excel strips leading zeros when stored as numbers
    • Workaround: prefix with apostrophe to force text format
  • Number formatting:
    • Use Home tab → Number group formats:
      • General, Number, Currency, Accounting, Short Date, Long Date
  • Alignment behavior:
    • Text aligns left
    • Numbers/dates align right (in general)

6) AutoFill and custom lists (method)

  • Autofill handle (green square / black cross when hovered):
    • Fill down sequences such as days and months using built-in custom lists
  • Autofill “Fill Series” for years and numeric sequences:
    • When dragging years like 2008, use dropdown options → Fill Series
  • Creating your own custom list:
    • File → Options → Advanced → Edit Custom Lists
    • Import from worksheet cells or manually add entries
  • Exercise practice applies:
    • Create monthly templates
    • Import student lists and fill down using custom lists

7) Templates and saving as templates (instructions)

  • Create new workbook from template:
    • File → New → choose templates
  • Save a reusable workbook template:
    • File → Save As
    • Change “Save as type” to Excel Template (*.xltx)
    • Save into default Custom Office Templates folder
  • Reuse:
    • File → New → Personal section shows saved templates

8) Worksheets: moving, renaming, copying, inserting, deleting

  • Rename:
    • Right-click tab → Rename
    • Or double-click tab → edit name
  • Move:
    • Drag-and-drop tabs
  • Copy:
    • Drag while holding Ctrl
    • Or right-click tab → Move or Copy… → check “Create a copy”
  • Insert:
    • Home → Insert → Insert Sheet
    • Or shortcut Shift + F11
  • Delete:
    • Right-click tab → Delete
    • Or select and delete via keyboard/mouse options

9) OneDrive syncing with Excel (concept)

  • Saving to OneDrive enables:
    • Cloud access and portal access
    • Version continuity across devices
    • Collaboration/cross-platform editing
  • File Explorer:
    • Sync status icons show whether fully synced or cloud-only

Formulas and functions

10) Formula basics + order of operations (BODMAS/PEDMAS)

  • Always start formulas with =
  • Prefer cell references over hardcoding
  • Operators:
    • Addition: +
    • Subtraction: -
    • Multiplication: *
    • Division: /
  • Brackets/parentheses override order:
    • Example described: 10 + 2 / 2 vs (10 + 2) / 2

11) Constructing formulas using Sum/Insert Function

  • Sum function:
    • Use Insert Function (Shift+F3) or type directly
    • Syntax: =SUM(range)
  • Formula argument selection:
    • Use ranges like C6:C25
    • Use Ctrl+Shift+Arrow selection shortcuts

12) The “big six” common math/stat functions (concept)

  • SUM (total)
  • COUNT (numeric cells only)
  • COUNTA (counts numbers + text)
  • AVERAGE
  • MIN
  • MAX
  • Status bar can show totals when selecting ranges (after enabling relevant items).

13) Relative vs Absolute referencing (detailed method)

  • Relative references (default):
    • When copying down, references shift (e.g., F5→F6)
  • Absolute references:
    • Lock a cell reference using $
    • Shortcut: F4 toggles $ locking behavior
    • Example use:
      • Tax rate always in I1, so lock it (e.g., I$1)
  • Mixed referencing mentioned:
    • Lock row or column only (advanced)

14) AutoSum (instructions)

  • Use AutoSum button on Formulas tab:
    • Can calculate SUM/AVERAGE/COUNT/MIN/MAX
  • Shortcut:
    • Alt + = applies AutoSum to selected adjacent cells
  • Note/warning:
    • AutoSum can ignore non-numeric items; it may show warnings like “formula omits adjacent cells” when selecting ranges that include unrelated numbers.

Named ranges

15) Creating and using named ranges (methods)

  • Create via Name box (no spaces):
    • Select range → type name in Name box → Enter
  • Create via Formulas tab:
    • Define Name → verify “refers to”
  • Create from selection:
    • Select header + data → Create from Selection
  • Use:
    • Type the name in formulas (e.g., SUM(Revenue))
    • Or use F3 to browse named ranges
  • Benefits:
    • More readable formulas
    • Easier navigation

Formatting & presentation

16) Number formatting (custom formats as masks)

  • Formatting changes the display, not underlying value:
    • Formula bar still shows raw numeric content
  • Types covered:
    • Number with decimals and separators
    • Currency vs Accounting differences (alignment/placement of symbol)
    • Date and time formats
    • Percentage behavior:
      • Requires values like 0.5 to display 50%
      • Fixing existing percentage numeric lists requires converting values or using Paste Special (mentioned)
    • Negative numbers formatting:
      • Red and brackets
    • Phone number/zip/social security formatting exists but is US-specific
  • Custom formats:
    • Explain placeholder differences:
      • # variable placeholder
      • 0 fixed placeholder (preserves leading zeros/width)
    • Custom format sections separated by semicolons:
      • Positive ; Negative ; Zero ; Text

17) Manual formatting tools (instructions)

  • Autofit columns:
    • Double-click column boundary
    • Or Home → Format → AutoFit Column Width
  • Autofit rows similarly via row boundary double-click
  • Apply headings:
    • Bold + background fill + font color
  • Alignment:
    • Vertical: top/middle/bottom
    • Horizontal: left/center/right
  • “Center across selection” recommended over Merge&Center:
    • Merge&Center causes usability issues (selection/formula problems)
  • Borders:
    • Apply presets or “More Borders” to control which sides/inside lines

18) Find and Replace for formatting

  • Replace formatting based on:
    • Format → from cell or choose formatting rules
  • Scope:
    • Worksheet vs workbook
  • Caution:
    • If multiple number formats differ (e.g., currency columns), you may need multiple passes.

19) Format Painter / clearing formatting

  • Format Painter:
    • Copy formatting from selected cells to other cells
    • Double-click keeps it active for multiple applications
    • Esc ends mode (or click button again)
  • Clear formatting options:
    • Clear All (values + formatting)
    • Clear Formats (keep values)
    • Clear Contents (remove values only)
    • Remove Hyperlinks / Clear hyperlinks / remove underline by choice

Excel Tables, filtering, and structured formulas

20) What Excel tables are and why they matter

  • Data formatted as an Excel Table gains:
    • Table Design contextual ribbon
    • Auto-expand behavior (used by charts, pivot tables)
    • Filter dropdowns per column
    • Structured references in formulas (TableName[ColumnName])

21) Structured table formulas

  • When data is in a table:
    • SUM(Salary) replaces cell ranges like SUM(F4:F23)
  • Benefits:
    • Readability and maintainability
    • Cleaner sharing with others

Sorting & filtering (advanced features)

22) Sort by custom lists

  • Create custom sorting order using File → Options → Edit Custom Lists
  • Sort dialog:
    • choose Custom List as sort order

23) Dynamic array SORT and SORTBY

  • SORT(array, [sort_index], [sort_order], [by_col])
  • SORTBY(array, by_array1, order1, [by_array2, order2]...)
  • Supports:
    • Multi-column sorting
    • Using column names instead of index numbers (SORTBY)
  • Horizontal sort demonstrated:
    • Use SORT with parameters + supporting steps like TEXTJOIN and sorting results.

24) FILTER dynamic array

  • FILTER(array, include, [if_empty])
  • Supports:
    • Single criterion
    • Multiple criteria using:
      • * for AND logic
      • + for OR logic
    • Equality patterns:
      • “both/neither” style using = relationships described with brackets
    • “-” logic (one or the other but not both)
  • Combines well with other functions:
    • Filter then sort the output

25) UNIQUE dynamic extraction

  • UNIQUE(array, [by_col], [exactly_once])
  • Distinction:
    • Exactly_once = TRUE returns items that appear exactly once
    • Distinct/Unique behavior depends on “exactly_once”

Power Pivot (multi-table pivot analysis)

26) Power Pivot workflow (instructions)

  • Turn on add-in:
    • File → Options → Add-ins → enable Microsoft Power Pivot for Excel
  • In Power Pivot:
    • Add tables to data model:
      • Orders from the active workbook
      • Other Excel sources via “Get External Data”
    • Use “Diagram View” to link tables:
      • Drag common keys:
        • One-to-many relationships using shared fields (e.g., CustomerID, CoffeeType)
  • Add calculated columns:
    • Profit = RevPerCoffee − CostPerCoffee
    • Total Profit = Profit * UnitsSold (cross-table reference via “related”)
  • Build consolidated pivot table:
    • Use pivot table field list:
      • Can use fields across linked tables
  • Pivot chart and formatting:
    • Create charts and adjust style (bar chart example)

Power Query (importing/cleaning/combining data)

27) Power Query basics (instructions)

  • Import from Data → Get Data:
    • Example: from a text file
  • Use “Transform Data” to open Power Query Editor
  • Cleaning steps shown:
    • Remove empty columns
    • Use first row as headers
    • Remove top rows containing unwanted marker rows
    • Rename misaligned columns
    • Replace spelling errors (Replace Values)
    • Trim extra whitespace
    • Split column by delimiter (e.g., product-name/quantity separated by “-”)
    • Remove duplicates
    • Verify data types (Text vs Whole Number vs Currency vs Date)
  • Load back:
    • Close & Load → optionally into an Excel table

28) Combine and transform files from a folder

  • Steps:
    • Data → Get Data → From Folder
    • Confirm combined preview
    • In Power Query:
      • Remove “Source name” column if unnecessary
      • Ensure column data types are correct
    • Close & Load back into Excel
  • Adding new files:
    • When new files are added to the folder, refresh updates data.

29) Linking to additional lookup tables (Power Query + Power Pivot)

  • Add other Excel sources into data model
  • Create relationships via common keys (AccountNumber, ProductNumber)
  • Refresh pivots after adding new transaction files

Advanced Excel (dynamic arrays, LET/LAMBDA)

30) Dynamic array functions overview

  • Introduced as Excel 365 “dynamic array” formulas with spill behavior:
    • One formula outputs multiple results
    • Editing permitted only in the originating cell

31) UNIQUE and examples (dynamic array)

  • Supports:
    • Unique vs distinct/appearing once
    • Unique across multiple columns with by-column argument
  • Count example uses:
    • UNIQUE + COUNTA combined with a COUNT formula.

32) SORT / SORTBY mechanics

  • SORT supports:
    • sort_index, sort_order, by_row/by_col behavior (via boolean)
    • Multi-column sorting using curly-brace index arrays
  • SORTBY supports:
    • multiple sort keys with separate arrays and order per key
    • Uses index positions implicitly but can be used with more clarity by column arrays.

33) SEQUENCE function

  • Generates numeric sequences without manual dragging.
  • Used to create schedules (Fridays every week) by:
    • rows count via COUNTA
    • step = 7 for weekly dates
    • format applied afterwards as date.

34) LET function

  • Purpose:
    • Define variables to reuse repeated subexpressions
    • Makes formulas cleaner and more efficient
  • Example:
    • Use LET with PATH extraction so worksheet title updates dynamically.

35) LAMBDA function

  • Purpose:
    • Create user-defined reusable functions.
  • Example:
    • Price + Tax reusable custom function:
      • Define variables “price” and “tax” then compute
    • Example:
      • Custom reusable SORTBY logic for repeated sorting across datasets.
  • Steps summary:
    • Build a Lambda formula
    • Assign it a name using Name Manager
    • Call it like a normal Excel function: =YourFunction(arg1,arg2,...)

Lookup functions and multi-criteria lookups (xlookup focus)

36) VLOOKUP limitations vs XLOOKUP

  • VLOOKUP:
    • exact vs approximate match
    • approximate match uses bracket/range logic
    • can’t easily return values left of lookup column
  • XLOOKUP:
    • simpler arguments
    • supports more flexible search direction modes
    • allows specifying return array and includes “if not found” logic

37) Handling duplicates in lookups

  • Approach described:
    • For duplicate lookup keys (same employee name), build a helper unique key:
      • combine employee name + department
    • Use XLOOKUP/VLOOKUP with the helper to select the correct record.

38) Two-way lookups (multiple criteria)

  • Example structure:
    • Use month dropdown + company dropdown
    • XLOOKUP nested:
      • outer lookup by month
      • inner lookup by company within the month’s row/column slice
  • Index/match alternative also shown conceptually.

Exercises (practice tasks referenced repeatedly)

Across sections, exercises typically require:

  • Creating tables (Excel Table feature)
  • Designing dropdowns (Data Validation)

Original video