Excel tutorials are everywhere, but many “pro tips” are actually bad habits in disguise. From broken formulas to messy data formatting, here are the popular tips that are secretly ruining your workbooks and what to do instead.
Use Center Across Selection to keep your data functional
You see it in almost every “aesthetic” Excel tutorial: someone selects a range of cells across the top of a report and hits the Merge & Center button. It might look great, but merging cells is one of the most disruptive formatting choices you can make in a dataset. The moment you do this, you disrupt the grid structure, making it harder to sort columns, write clean formulas, and reliably use tools like PivotTables or Power Query without cleanup.
Instead, use Center Across Selection (Ctrl+1 > Alignment > Horizontal > Center Across Selection). It looks identical to merged cells, but every cell remains independent. If you use this often, you can even add it to your Quick Access Toolbar to save time.
When it’s okay: Merging cells is fine for one-off presentation covers or printable forms that will never be used for actual data analysis.
Ditch the outdated VLOOKUP function for something more modern
Switch to XLOOKUP for more resilient spreadsheets
For decades, VLOOKUP was the gold standard—but it can be fragile. Because it relies on a static column index number, your formula can break if you insert or delete columns. It also only searches from left to right, which is a massive limitation in complex datasets.
That’s why you should switch to XLOOKUP. It doesn’t require a column index, can search in any direction, and can be adapted to handle multiple criteria or perform two-way lookups. It’s a more robust and readable way to manage data relationships.
When it’s okay: Use VLOOKUP if you’re sharing files with people stuck on Excel 2019 or earlier versions that don’t support newer functions.
Why your old Excel spreadsheet is “legacy code” (and how to fix it)
Transform legacy spreadsheets into maintainable, automated tools that scale and survive the test of time.
Quit manually color-coding your cells
Automate your visuals with built-in tools
The internet loves the paint bucket tool for color-coding, but manual coloring is static and hard to maintain if a project status changes, or you need to change your whole workbook’s color themes. Aside from that, manual painting is more likely to result in a spreadsheet that becomes misleading over time as the data changes.
The better way is a mix of cell styles and conditional formatting. Use the Cell Styles gallery in the Home tab for static headers and input cells, so everyone knows exactly where they can type and what to leave alone. These will update automatically if you later change your workbook theme. For data that changes based on logic, use Conditional Formatting, also in the Home tab, so cells turn a different color automatically based on their values. This ensures your visual cues are always accurate to the actual data.
When it’s okay: Manual coloring is fine for a quick fix, a temporary note to yourself that isn’t part of the data’s official record or permanent structure, or a carefully created homepage worksheet or dashboard that’s designed to look like a website or app.
Avoid hiding rows and columns to clean up your interface
Use the grouping tool to keep your workspace toggleable
Right-clicking a column and selecting Hide might seem like a quick fix for a cluttered sheet, but it’s not always the most reliable way to manage your layout—especially in collaborative workbooks. While the column headings usually offer a subtle hint that something is missing, those visual indicators can be easy to miss, making hidden data easier to overlook.
You should group your columns (Data > Outline > Group) instead of hiding them. Grouping is often more user-friendly, with clear toggles to expand or collapse data. Unlike hiding, grouping lets you create multilevel subgroups, giving you granular control over what is visible.
If you find yourself hiding massive chunks of data just to see your results, you might be better off using the three-tab rule and structuring your spreadsheet like a developer. Often, the data you feel the need to hide actually belongs on a separate background sheet entirely.
When it’s okay: Hiding is fine when preparing a final, static report for a PDF export, where the viewer won’t be interacting with the actual grid.
Stop building massive mega-formulas that no one can read
Simplify complex logic with helper columns, LET, and Power Query
Mega-formulas—those nested monstrosities that are 10 lines long—are a nightmare to debug. If the result is wrong, you have to parse through layers of parentheses just to find the culprit. They’re the spreadsheet equivalent of writing a novel as one giant, run-on sentence that nobody can follow.
The first alternative is to use helper columns. Some people see them as amateur, but the opposite is true. They make your logic traceable and easier to fix, and they also turn otherwise invisible math into usable figures you can interact with and feed into other tools like PivotTables.
If your logic must be kept in a single cell, however, use the LET function, which lets you assign names to calculations inside a formula, making complex logic easier to read and more efficient.
Arguably the most powerful alternative approach is to use Power Query to create conditional columns. This moves your logic into a dedicated interface, keeping your main sheet clean and your formulas short.
When it’s okay: In most cases, there’s little reason to rely on massive mega-formulas in Excel. That said, it’s important to distinguish between a long formula and a nested one. Many modern functions require several arguments to work correctly, which naturally lengthens the formula—and that’s fine. The issues arise when you start nesting multiple logical tests.
Don’t hard-code your input values directly into formulas
Use named ranges and a dedicated variable table
Typing a value directly into a formula—like =[@Sales]*0.07—is a common cause of stale data. If that tax rate changes to 8%, you have to hunt down every formula to update it manually. Miss even one, and your entire workbook could be wrong.
Rather than hard-coding values, place your variables in a dedicated place in your workbook and assign names to these cells (via Formula > Name Manager or the Name Box in the top-left corner) so your formulas look more like =[@Sales]*TaxRate. This is easier to understand and ensures your workbook updates automatically when you change a single input cell.
Better still, you can save a massive amount of time by using the Create from Selection tool to name multiple variables at the same time.
How to name Excel objects like a software dev
Transition from user to developer through consistent notation, table-based architecture, global constants, and self-documenting logic.
When it’s okay: Hard-coding is safe for universal constants that will never change, such as 24 hours in a day, 100 cents in a dollar, or 360° in a circle.
It’s easy to fall for flashy spreadsheet tricks, but your future self will thank you for keeping things simple and logical. Lots of these problems can be avoided by starting with a solid foundation. Structuring your data correctly—like using columns for fields and rows for records—ensures you’re working with Excel, not against it.
- OS
-
Windows, macOS, iPhone, iPad, Android
- Free trial
-
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
Stephan is the sports journalist for the Maple Grove Report.
