Professional using a spreadsheet efficiently at a workplace computer.

5 Excel Formulas That Replace an Hour of Manual Data Entry

by

If you spend your afternoons copying data between sheets, fixing typos in imported CSVs, or scrolling through 5,000 rows hunting for duplicates — stop. There’s a formula for almost all of it.

These five cover roughly 80% of the manual cleanup work I see office workers doing by hand. Learn them once and you’ll never go back.

1. TRIM + CLEAN: Fix the Messy Imported Data

You get a CSV from a vendor. Names have trailing spaces. Some cells have line breaks. Excel’s VLOOKUP won’t match “John Smith” to “John Smith ” — and that’s a 30-minute rabbit hole.

Formula:


=TRIM(CLEAN(A2))
  • TRIM removes leading, trailing, and extra internal spaces.
  • CLEAN strips non-printable characters (line breaks, tabs).

Wrap it around your imported text and it normalizes everything for downstream matching. Use it as a helper column, copy, then paste as values to replace the originals.

Time saved: 15 minutes per messy import.

2. TEXTJOIN with IF: Build a CSV List Without Typing Commas

You need to email a list of all the active client names from column A. Typing them out — or copying one cell at a time — is tedious. TEXTJOIN with a conditional wrapper does it in one cell.

Formula:


=TEXTJOIN(", ", TRUE, IF(B2:B100="Active", A2:A100, ""))
  • TEXTJOIN(", ", TRUE, ...) joins everything with a comma and skips empty cells.
  • The IF filters to only “Active” rows.

Important: this is an array formula. In older Excel, press Ctrl + Shift + Enter. In Excel 365 / 2021+, just hit Enter.

Copy the result, paste it into your email, done.

Time saved: 10 minutes per list.

3. IFERROR + VLOOKUP: Stop the “#N/A” Spam

You’ve built a VLOOKUP to pull customer info. It works — except for the 40 rows that return #N/A because the lookup value doesn’t match. Now your report is unreadable.

Wrap it in IFERROR:

Formula:


=IFERROR(VLOOKUP(A2, Customers!A:D, 3, FALSE), "Not on file")

You get a clean report with "Not on file" instead of #N/A. Replace the fallback text with "Check" or "TBD" — whatever makes sense for the report.

Even better in Excel 365: swap VLOOKUP for XLOOKUP, which has IFERROR built in:


=XLOOKUP(A2, Customers!A:A, Customers!C:C, "Not on file")

Same result, fewer characters, no nested function.

Time saved: 20 minutes per report (no more manual cleanup of error rows).

4. UNIQUE + SORT: One-Click De-Duplication

Someone sent you a “cleaned” list. It has 200 names — but 40 of them are duplicates from people who registered twice. You need a deduplicated version, alphabetized.

Stop using Pivot Tables for this. Use:

Formula:


=SORT(UNIQUE(A2:A500))

That’s it. One cell, hit Enter, and you have a deduplicated, sorted list. No helper columns, no manual scanning, no “Data > Remove Duplicates” clicking through menus.

Bonus: combine with FILTER if you want to dedupe by status:


=SORT(UNIQUE(FILTER(A2:A500, B2:B500="Approved")))

Time saved: 25 minutes per messy list.

5. XLOOKUP: The Modern Replacement for VLOOKUP

If you’ve been using VLOOKUP since 2008, this one will feel like time travel. XLOOKUP solves almost every reason people hate VLOOKUP:

  • Look left. VLOOKUP can only look right. XLOOKUP doesn’t care which direction.
  • No column counting. Instead of VLOOKUP(A2, Sheet!A:D, 3, FALSE), you specify the lookup range and the return range separately.
  • Built-in error handling. No need to wrap it in IFERROR.
  • Exact match by default. No more accidentally sorting your lookup column and breaking the whole sheet.

Formula:


=XLOOKUP(A2, Customers!A:A, Customers!D:D, "No match")

If you’re still on Excel 2019 or older, XLOOKUP isn’t available — but INDEX(MATCH()) is the next best thing:


=INDEX(Customers!D:D, MATCH(A2, Customers!A:A, 0))

It’s slightly clunkier, but works in every version of Excel since 2007.

Time saved: indefinite. You’ll never write another broken VLOOKUP.

Putting It All Together

These five formulas aren’t isolated tricks. They chain together. A typical “clean this report” workflow now looks like:

  • TRIM(CLEAN(...)) your imported data.
  • XLOOKUP (or IFERROR + VLOOKUP) for lookups.
  • TEXTJOIN with IF to build lists.
  • UNIQUE + SORT for deduplicated outputs.

Five formulas, one hour saved per week, no copy-paste fatigue.

Related: Excel Shortcuts Cheat Sheet | Excel Auto-Fill in 60 Seconds

Leave a Comment