Common How to Use Excel Mistakes & How to Fix Them

VLOOKUP Returning #N/A When You Can See the Match

Honestly, this one drove me nuts for years. You've got two lists, the lookup value is right there in both columns, staring you in the face, but Excel stubbornly returns #N/A. Nine times out of ten the problem is invisible — trailing spaces, numbers stored as text, or a sneaky non-breaking space copied from a web page. I wasted so many hours rebuilding formulas that were never broken in the first place.

The fastest fix I've found is wrapping both sides in TRIM and VALUE. Instead of =VLOOKUP(A2, Sheet2!A:B, 2, FALSE), use =VLOOKUP(VALUE(TRIM(A2)), Sheet2!A:B, 2, FALSE). That handles the text-as-number problem and the extra spaces in one shot. If it still fails, highlight the lookup column and check the Number Format dropdown — if it says "Text" and your reference data is "General," there's your mismatch. So annoying, but at least it's fixable.

Another culprit people miss: the lookup column has to be the leftmost column in your table array. VLOOKUP can only look right. So if your match key is in column C and your return value is in column A, VLOOKUP will never work no matter what you try. Switch to INDEX/MATCH or XLOOKUP instead. Saved me more times than I can count.

If you're getting #N/A after copy-pasting stuff from a website, check for CHAR(160) — those non-breaking space characters that look like regular spaces but aren't. Find and replace with nothing. Works almost every time. And if some rows work but others don't, scan for merged cells in your lookup range. Unmerge them and fill the values down manually. Merged cells are the devil in lookup ranges, I swear.

SUM Giving a Number That Makes No Sense

So you've selected a range, hit AutoSum, and Excel spits out a number that's obviously wrong — way too high or too low. Before you doubt your math, check whether someone (maybe you, late at night) left a hidden row filtered out. AutoSum includes filtered-out rows unless you use SUBTOTAL. Took me embarrassingly long to figure that one out.

=SUBTOTAL(9, A2:A100) sums only visible cells. =SUBTOTAL(109, A2:A100) goes further and ignores manually hidden rows too. I default to 109 in every summary sheet now. It just works and I don't have to think about it.

But the dumber reason this happens? Someone typed a number into a cell that's formatted as text. Excel won't sum it, won't warn you, won't show any visual cue unless you've got error checking turned on — which most people don't. Select the range, hit Ctrl+1, and scan for cells where the alignment doesn't match. Text defaults to left-aligned, numbers to right. That visual tells you more than any formula checker ever could. I've caught so many "missing" numbers this way that it's become the first thing I check now.

Deleting Rows and Breaking Every Formula That References Them

This is the mistake that makes people terrified of touching a shared workbook. I've seen it happen to senior analysts who absolutely should know better. You delete a row you thought was useless, and suddenly half the sheet shows #REF! errors. The fix isn't undoing — it's stopping yourself from deleting rows in the first place.

If you need to remove data but keep formulas intact, clear the cell contents with the Delete key (not right-click Delete Row) and use IFERROR or IF(ISBLANK(...)) wrappers on downstream formulas. If you truly need to reorganize, convert the range to a Table first with Ctrl+T. Tables use structured references that survive row deletion because they reference column names, not cell addresses. That one habit — Ctrl+T before touching anything — has probably saved me from approximately a thousand awkward conversations about why the monthly report is broken.

And here's the thing about #REF! errors nobody tells you: once they appear, Excel can't auto-recover the reference. You have to manually re-point each one. There's no magic undo beyond the immediate Ctrl+Z. If you spot a #REF! cascade, stop everything and Ctrl+Z immediately. Don't save. Don't do anything else. Just undo. The longer you wait, the more screwed you are.

Date Formats Shifting When You Open the File on Another Computer

This one's insidious because the numbers look fine until you sort or filter and realize March 4th became April 3rd. The root cause: Excel stores dates as serial numbers and only displays them based on your system locale. A file created on a US machine (MM/DD/YYYY) opened on a UK machine (DD/MM/YYYY) silently swaps month and day for any date where both numbers are 12 or under. I learned this the hard way after sending a budget file to a client in London and getting a very confused email back.

The only reliable fix I've found is storing dates as unambiguous text strings in the source data — YYYY-MM-DD format specifically — then converting them with =DATEVALUE() or =DATE(LEFT(A2,4), MID(A2,6,2), RIGHT(A2,2)) inside Excel. That way the serial number is calculated correctly regardless of locale. Clunky, but it works.

For existing sheets that are already corrupted, tbh there's no universal fix. You have to manually identify which dates got swapped by cross-referencing with a known-good source. Prevention beats cure by a mile here and I'm not sure there's ever going to be a better solution given how deeply the locale system is baked into Excel's core.

"Cannot Shift Objects Off Sheet" Error

You try to insert a row or hide a column and Excel throws this cryptic message at you. There's nothing visibly wrong with your sheet and you're sitting there wondering if Excel just decided to ruin your afternoon. What's actually happening: somewhere in the far-right columns or way down past row 1000, there's a comment box, shape, or embedded image that's sitting in the way. Excel considers objects to occupy the cells they overlap, and if an object touches the last row or column, Excel refuses to shift anything. Absolutely maddening.

Fix it in under a minute: press Ctrl+G (Go To), click Special, select Objects, hit OK. This selects every shape, comment, and embedded object on the sheet. Press Delete. If you need those objects, you can instead move them inward, but honestly it's usually faster to just delete and re-add them. I've done this dozens of times and never once regretted deleting the objects.

Stuff I Do to Avoid These Headaches

I convert data ranges to Tables (Ctrl+T) before building anything with formulas. Structured references are more resilient and the formula column auto-fills, which is just nice. I never type dates by hand anymore — Data Validation to restrict input to actual date values, or a date picker control if I'm feeling fancy. Before sharing a workbook, I run Formulas > Error Checking > Trace Error on every sheet. Catches #REF! and inconsistent formulas before someone else finds them, and finding your own mistakes is way less embarrassing.

I keep raw data, calculation logic, and presentation in separate sheets now. When the boss asks for a "quick change" to formatting, I don't accidentally corrupt my source numbers because they're on a different tab entirely. I also keep a sandbox sheet in every workbook where people can copy-paste and experiment without touching live data. And on critical summary cells I use =IFERROR(formula, "check source") — a clear message beats a silent wrong number every single time.

PivotTable Not Refreshing After You Changed the Source Data

PivotTables don't auto-refresh when you edit their source range. I still forget this sometimes and stare at the numbers wondering why they didn't change. You add five rows, refresh, and the new data simply isn't there. First check: did you actually expand the data source? Right-click the PivotTable, go to Data Source, and verify the range includes your new rows. If you used a fixed range like Sheet1!A1:G200, those new rows at 201-205 weren't included. Simple, but easy to miss.

The permanent fix: convert your source data to a Table before creating the PivotTable. Tables auto-expand, so the PivotTable source is always TableName rather than A1:G200. Next time you add rows, just Refresh and the data appears. Once you start doing this you'll never go back.

But also check something subtler: if your PivotTable is grouped by date and you added data for a month that's collapsed in the grouping, the numbers are actually there — they're just hidden inside a collapsed group field. Expand all groups before panicking. I may or may not have spent 45 minutes debugging a PivotTable that was working perfectly fine the whole time...

Formula Dragging Changes the Wrong Cell References

You write =A2*B2, drag it down, and row 3 correctly shows =A3*B3. Great. But now drag it right across columns and it becomes =B2*C2 instead of staying on A and B. That's because you need dollar signs on the column references: =$A2*$B2 locks the column, =A$2*B$2 locks the row, and =$A$2*$B$2 locks both. Kinda confusing the first time you see it, but it becomes second nature pretty fast.

The mental shortcut I use: F4 cycles through the four reference types while editing a formula. Click on the cell reference in the formula bar and tap F4 repeatedly — it toggles between A1, $A$1, A$1, and $A1. No memorization needed. I use this probably 50 times a day.

So the next time someone sends you a workbook that "just stopped working," check for merged cells first, then number formats, then hidden characters. In that order. Skip the rabbit hole of formula auditing until you've ruled out the invisible data quality issues. It's almost never the formula.