Author name: David Gul

Reference cells in closed workbooks using file-path formulas, VLOOKUP across files, and Power Query. Three methods with broken link management.

Extract invoice numbers, SKUs, zip codes, and other values from mixed text using LEFT, MID, RIGHT, FIND, Flash Fill, and TEXTAFTER.

Four methods to auto-generate unique invoice numbers: sequential, year-prefix, MAX-based gap-proof, and date-based with daily reset.

Four ways to make large dropdown lists searchable: AutoComplete, FILTER with validation, Combo Box controls, and helper column filtering.

Use conditional formatting to color entire rows based on status, amount thresholds, or multiple conditions. Includes zebra striping and combined rules.

Three ways to count distinct values: UNIQUE+COUNTA for Excel 365, SUMPRODUCT for all versions, and Pivot Table Distinct Count.

Use CONVERT for weight, distance, and temperature. Build rate tables for currencies. Format phone numbers, pad zeros, and handle mixed-unit columns.

Calculate percentage change, year-over-year growth, and CAGR correctly in Excel. Handles zero baselines, negative numbers, and visual indicators.

Build running totals that accumulate within each month and reset on the first of the next month. Two formula approaches plus category breakdowns.

Turn hundreds of daily transactions into an auto-updating monthly summary using SUMIFS, COUNTIFS, and AVERAGEIFS. Includes Pivot Table alternative.