The Excel Habits That Slow Analysts Down (and How to Fix Them)

A lot of the time that analysts lose in Excel isn't from hard problems. It's from habits that made sense once -- or that just happened without thinking about them -- and got repeated until they became the default way of working.

Most of these habits create friction that's invisible until you look at someone else's spreadsheet and immediately see what's wrong. Here are the ones that slow people down most, and what to do instead.

Table of Contents

• Entering the same value multiple times
• Using the mouse for everything
• Hardcoding values that might change
• Not using Excel Tables
• Formulas that are hard to audit
• Nesting everything into one formula
• Keeping the same number in multiple places
• VLOOKUP with the wrong setup
• FAQ

Entering the same value multiple times

If you're typing the same value into multiple cells, you're creating work. Every repeated entry is a potential mismatch: one cell says "Q1 2024" and another says "Q1-2024" and a third says "Q1/2024" and suddenly filters don't work and VLOOKUPs return errors.

The fix is to let a formula or feature do the repeating:

• If it's a label that should match a date, derive it with TEXT() or a formula that reads from the date column
• If it's a category that should be consistent, use a dropdown list (Data Validation) so entries are always identical
• If it's a value that should propagate down, use a formula that references the first instance

Using the mouse for everything

The mouse is slow for everything Excel does regularly. Selecting ranges, navigating between cells, applying formats, running common operations -- all of these are faster with keyboard shortcuts once you build the muscle memory.

A small set of keyboard moves covers most of it:

• Ctrl+Arrow: jump to the last non-empty cell in a direction
• Ctrl+Shift+Arrow: select from current cell to last non-empty cell in a direction
• Ctrl+D / Ctrl+R: fill down / fill right
• Ctrl+T: create a Table from the current selection
• F2: edit the current cell without clicking
• Alt+Enter: insert a line break inside a cell
• Ctrl+1: open Format Cells dialog

You don't need to memorize all of these at once. Pick two or three that would replace things you do constantly and use only those until they're automatic.

Hardcoding values that might change

Hardcoded values are numbers typed directly into formulas instead of referenced from a cell. They work until something changes -- the tax rate is adjusted, the target threshold moves, the discount percentage is updated -- and then you have to find every formula where you hardcoded that value and update them individually.

Put the number in its own cell and reference it instead:

• Create a parameters section at the top or side of the sheet for inputs that might change
• Name the cell using Define Name (Formulas tab) so formulas can reference it by name instead of address
• When the value changes, update it once in the parameters cell and all formulas update automatically

Not using Excel Tables

Excel Tables (Insert, Table or Ctrl+T) do several things that manually formatted ranges don't:

A Table expands automatically when you add data below it -- formulas, charts, and named references that point to the table update without manual adjustment. Tables also make formulas more readable: instead of =SUM(B2:B500), you get =SUM(Sales[Amount]). Column references are structured and don't break when you insert or delete rows.

Common culprits that happen without Tables:

• Formulas that reference a fixed range (B2:B500) and stop including new data when you add rows
• VLOOKUP source ranges that need to be manually extended every time the data grows
• Charts that don't update when new data is added because the source range is hardcoded

Formulas that are hard to audit

A formula that works is not the same as a formula you can check. If you can't look at a formula and verify it's doing what you think, you have no reliable way to catch errors.

The common culprits:

• Formulas that reference cells you can't see from the current view -- linked ranges on other sheets, external workbook references, cells that are off-screen
• Calculations split across many cells with no documentation of what each intermediate result represents
• Magic numbers embedded in formulas (=A1*0.23) with no indication of what 0.23 means

Audit tools that help: Formula Auditing (Formulas tab) shows you which cells a formula references and which cells reference it. Tracing precedents and dependents visually draws arrows to connected cells. Useful for verifying that a formula is connected to what you think it is.

Nesting everything into one formula

Nested formulas -- IF inside IF inside VLOOKUP inside IFERROR -- are hard to write, hard to read, and hard to fix. When they work, they feel clever. When they break, nobody knows why.

Break it into helper columns instead:

• Give each calculation step its own column, even if the column is hidden later
• Label the column so the intermediate result is named and visible
• Debug each step independently before combining

Helper columns are not a sign of weak Excel skills. They're a sign of readable design. The goal is a spreadsheet that someone else (or you, six months later) can open and understand without needing the original author present.

Keeping the same number in multiple places

If the same value lives in two cells, eventually they'll disagree. One will be updated and the other won't. The source of truth becomes unclear, and whoever reads both will trust neither.

Keep each fact in exactly one place:

• Store the source value once and reference it everywhere else
• If a number has to appear in multiple sheets, link the other sheets to the original cell rather than copying the value
• If a number comes from an external source, note where it came from and when it was last updated

VLOOKUP with the wrong setup

VLOOKUP has one structural limitation that causes most of its problems: it can only look to the right. The lookup column must be the leftmost column in the lookup range, and the return value must be to the right of it. When your data isn't arranged that way, VLOOKUP forces you to rearrange columns or wrap in INDEX/MATCH.

XLOOKUP (available in Excel 365 and Excel 2019+) removes this constraint. It can look in any direction, return from any column, and handles missing values with a built-in if-not-found argument that's cleaner than wrapping in IFERROR.

If you're on a version with XLOOKUP, use it instead of VLOOKUP for new work. If you're not, INDEX/MATCH is the standard workaround for situations where VLOOKUP's column constraint is a problem.

FAQ

Is it okay to use helper columns?
Yes. Helper columns make formulas readable, easier to audit, and faster to fix when something breaks. The only reason to avoid them is when the sheet layout genuinely can't accommodate extra columns -- which is rare.

When does VLOOKUP break?
VLOOKUP breaks when the lookup value isn't found and there's no error handling, when the fourth argument is TRUE (approximate match) and the table isn't sorted, and when columns are inserted to the left of the lookup column after the formula is written. XLOOKUP avoids most of these.

How do I fix a slow workbook?
Check volatile functions first (NOW, TODAY, OFFSET, INDIRECT, RAND -- these recalculate every time anything changes). Then check conditional formatting on large ranges. Then check whether any formulas reference entire columns (A:A) instead of the actual data range. Any of these can be sources of slowness.

Should I use macros for repetitive tasks?
If you find yourself doing the same sequence of steps repeatedly, a macro can automate it. The threshold for whether it's worth it: if you do the task more than once a week and it takes more than a few minutes, a macro probably pays off. If you do it rarely, the time to build and maintain the macro may not be worth it.