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.Stop merging cells just to make your headers look pretty 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.Related 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.Posts 1 By Tony Phillips 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 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.Subscribe for smarter Excel habits and cleaner workbooks Ready to stop brittle Excel tricks? Subscribe to the newsletter for practical, actionable Excel best practices—safer alternatives to merged cells, VLOOKUP traps, manual formatting, and mega-formulas—plus related spreadsheet and productivity guidance.Get Updates By subscribing, you agree to receive newsletter and marketing emails, and accept our Terms of Use and Privacy Policy.
You can unsubscribe anytime.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.Related 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.Posts By Tony Phillips 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 Excel, not it.
Microsoft 365 Personal 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.$100 at Microsoft Expand Collapse
Read More
