Practical Excel guides for real business problems. Each tip includes step-by-step formulas, examples, and advice on when to automate with VBA.
-
How to Pull Matching Data from Another Spreadsheet in Excel
Use VLOOKUP to match order numbers, invoice IDs, or any lookup value across two spreadsheets. Step-by-step guide with examples and common fixes.
-
Why INDEX/MATCH is Better Than VLOOKUP for Complex Lookups
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.
-
How to Compare Two Lists and Find Differences in Excel
Three methods to find matches, missing items, and duplicates between two Excel lists. COUNTIF, Conditional Formatting, and INDEX/MATCH approaches.
-
How to Total Values Based on Multiple Criteria with SUMIFS
Use SUMIFS to total sales by region and month, count transactions by category, or build summary reports with multiple conditions.
-
How to Use XLOOKUP to Replace VLOOKUP in Excel 365
XLOOKUP searches any direction, has built-in error handling, and returns multiple columns. Complete guide with comparison table.
-
How to Clean Messy Imported Data in Excel
Fix extra spaces, inconsistent casing, text-formatted numbers, and mixed date formats from CSV and CRM imports using TRIM, PROPER, VALUE, and more.
-
How to Split Full Names into First Name and Last Name in Excel
Four methods to split full names into separate columns: formulas, Flash Fill, Text to Columns, and handling middle names and suffixes.
-
How to Automatically Highlight Overdue Items and Approaching Deadlines
Use conditional formatting to automatically color-code overdue (red), approaching (yellow), and on-track (green) items based on due dates.
-
How to Find and Remove Duplicate Rows Based on Multiple Columns
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.
-
How to Fix Dates That Excel Doesn’t Recognize
Six methods to convert text dates to real Excel dates. Covers YYYYMMDD, DD/MM/YYYY, dot separators, date-time stamps, and mixed formats.
-
How to Build a Monthly Summary Report from Daily Transaction Data
Turn hundreds of daily transactions into an auto-updating monthly summary using SUMIFS, COUNTIFS, and AVERAGEIFS. Includes Pivot Table alternative.
-
How to Calculate Business Days Between Two Dates in Excel
Use NETWORKDAYS to count working days excluding weekends and holidays. Includes WORKDAY for due dates and custom weekend schedules.
-
How to Calculate Running Totals That Reset Each Month in Excel
Build running totals that accumulate within each month and reset on the first of the next month. Two formula approaches plus category breakdowns.
-
How to Show Percentage Change and Growth Rates in Business Reports
Calculate percentage change, year-over-year growth, and CAGR correctly in Excel. Handles zero baselines, negative numbers, and visual indicators.
-
How to Convert Between Units, Currencies, or Formats in Bulk
Use CONVERT for weight, distance, and temperature. Build rate tables for currencies. Format phone numbers, pad zeros, and handle mixed-unit columns.
-
How to Count Unique Values in a Column in Excel
Three ways to count distinct values: UNIQUE+COUNTA for Excel 365, SUMPRODUCT for all versions, and Pivot Table Distinct Count.
-
How to Automatically Color-Code Rows Based on Cell Values
Use conditional formatting to color entire rows based on status, amount thresholds, or multiple conditions. Includes zebra striping and combined rules.
-
How to Build a Searchable Dropdown List in Excel
Four ways to make large dropdown lists searchable: AutoComplete, FILTER with validation, Combo Box controls, and helper column filtering.
-
How to Create an Automatic Invoice Number Generator in Excel
Four methods to auto-generate unique invoice numbers: sequential, year-prefix, MAX-based gap-proof, and date-based with daily reset.
-
How to Extract Numbers, Codes, or IDs from Mixed Text Strings
Extract invoice numbers, SKUs, zip codes, and other values from mixed text using LEFT, MID, RIGHT, FIND, Flash Fill, and TEXTAFTER.
-
How to Pull Data from a Closed Workbook Without Opening It
Reference cells in closed workbooks using file-path formulas, VLOOKUP across files, and Power Query. Three methods with broken link management.
-
How to Combine Data from Multiple Sheets into One Master List
Three methods to consolidate data from multiple sheets: Power Query, manual copy-paste, and VSTACK. Includes adding a source identifier column.
-
How to Lock Formulas but Allow Data Entry in Specific Cells
Protect formulas and structure while letting users type in designated input cells. Step-by-step sheet protection setup with best practices.
-
How to Create Dependent Dropdown Lists in Excel
Make a second dropdown change its options based on the first selection using Named Ranges and INDIRECT. Step-by-step with screenshots.
-
How to Build a Status Column with Nested IF Statements and IFS
Assign status labels automatically based on scores, dates, or multiple conditions. Nested IF, IFS, and lookup table approaches compared.
“I used David over 10 years ago to build some bespoke Excel software which is still going strong and used daily within a thriving business. David is everything you’d want in a freelancer — clear communication, excellent time management, skilled and trustworthy.”
Tommy W. · Client since 2011 · PeoplePerHour
Need More Than Formulas?
We build custom VBA tools that automate what formulas can’t handle.
Start a Project