Excel Tips

Use VLOOKUP to match order numbers, invoice IDs, or any lookup value across two spreadsheets. Step-by-step guide with examples and common fixes.

INDEX/MATCH can look up in any direction, handles multiple criteria, and won’t break when columns change. Step-by-step guide with comparison table.

Three methods to find matches, missing items, and duplicates between two Excel lists. COUNTIF, Conditional Formatting, and INDEX/MATCH approaches.

Use SUMIFS to total sales by region and month, count transactions by category, or build summary reports with multiple conditions.

XLOOKUP searches any direction, has built-in error handling, and returns multiple columns. Complete guide with comparison table.

Fix extra spaces, inconsistent casing, text-formatted numbers, and mixed date formats from CSV and CRM imports using TRIM, PROPER, VALUE, and more.

Four methods to split full names into separate columns: formulas, Flash Fill, Text to Columns, and handling middle names and suffixes.

Use conditional formatting to automatically color-code overdue (red), approaching (yellow), and on-track (green) items based on due dates.

Three methods to find and remove duplicate rows based on specific columns in Excel. COUNTIFS flagging, concatenation keys, and the built-in Remove Duplicates tool.

Six methods to convert text dates to real Excel dates. Covers YYYYMMDD, DD/MM/YYYY, dot separators, date-time stamps, and mixed formats.

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

Use NETWORKDAYS to count working days excluding weekends and holidays. Includes WORKDAY for due dates and custom weekend schedules.

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

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

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

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

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

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

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

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

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

Three methods to consolidate data from multiple sheets: Power Query, manual copy-paste, and VSTACK. Includes adding a source identifier column.

Protect formulas and structure while letting users type in designated input cells. Step-by-step sheet protection setup with best practices.

Make a second dropdown change its options based on the first selection using Named Ranges and INDIRECT. Step-by-step with screenshots.

Assign status labels automatically based on scores, dates, or multiple conditions. Nested IF, IFS, and lookup table approaches compared.