I've spent a frankly unreasonable amount of time in Excel, so I've had plenty of opportunities to find out which habits make my life easier—and which ones come back to haunt me later.Over the years, I've gradually ditched a handful of things that seemed harmless at the time but made my spreadsheets harder to sort, filter, update, or understand.Here are five things I stopped doing in Excel, and why my spreadsheets have become much more reliable as a result.
Merging cells It can make a tidy spreadsheet awkward Close I used to merge cells whenever I wanted a spreadsheet to look tidier.I'd merge headers when they needed to span two or more columns, merge cells across rows when a heading or label applied to several rows, or merge cells across columns when a data point applied across several columns in the same row.It was my way of keeping a spreadsheet tidy, and with the "Merge & Center" button so prominent on the ribbon, it was hard not to click it.
But merged cells cause a whole host of problems.Excel works best when each cell contains a single piece of information.Because of this, merged cells can interfere with filtering, sorting, PivotTables, and other Excel features that expect data to be laid out this way.
Now, I keep each row as a complete record and repeat the relevant values when necessary.I also turn my datasets into formatted Excel tables, which gives Excel the clear structure it wants.If I absolutely need something to appear centered across several columns, I use Center Across Selection instead.
My spreadsheets still look tidy, but Excel has a much easier time figuring out what I mean.Related Stop merging cells in Excel: Add this forgotten alignment trick to your toolbar Merged cells break sorting and tables—use a macro to apply Center Across Selection with one click instead.Posts 3 By Tony Phillips Leaving blank rows Excel doesn't always see an empty row the way I do Close A blank row can make a spreadsheet feel easier on the eye, especially when you're working with a long list of data.
I used to insert them to create a bit of breathing room, especially when I wanted different sections of a worksheet to be visually separated.The problem is that Excel can interpret a completely blank row as the end of one block of data and the start of another.Like merging cells, that can cause headaches when sorting and filtering, creating PivotTables, or loading the data into Power Query.
The empty row might look like harmless formatting to me, but Excel sees it as a boundary.Instead, I generally keep related records together and, if I need to separate genuinely independent sets of data, I'll create separate tables.If everything belongs to the same dataset, but I want to distinguish different groups visually, I'll use conditional formatting.
That way, I still get the visual distinction I want without putting potentially troublesome gaps in the middle of my data.Coloring cells by hand Manual formatting can quickly get out of sync Close Color can make a spreadsheet easier to understand, but nowadays, I try to avoid applying it manually.If a color means something—such as green for completed, orange for overdue, or red for something that needs attention—it's easy for the color to become out of date when the underlying data changes.
It's also easy for the formatting to become inconsistent, especially when different people are working on the same spreadsheet.That's where conditional formatting comes in.I can tell Excel to apply a particular format when a cell meets a certain condition, so the highlighting changes automatically as the data changes.
For everything else, cell styles make it easy to keep my formatting consistent.I can use different styles to show input cells, calculated cells, and cells that shouldn't be edited, then apply those styles whenever I need them.If I change the style later, I can update its definition rather than hunting through the worksheet to reformat individual cells.
I'm not saying using color in spreadsheets is a bad thing.In fact, it can actually help make them more reliable.The point is that Excel can handle the repetitive work for me, keeping the formatting consistent and up to date.
Hard-coding values They're problematic if assumptions change Close A formula like =[@[Total Sales]]*(1-8.25%) is perfectly capable of giving me the right answer.The trouble comes when that hard-coded value of 8.25% represents something that might change later, such as a tax rate, commission, discount, or pass mark.I prefer to put changeable values in their own cells and reference them from my formulas instead.
So, I might use =[@[Total Sales]]*(1-$N$2), with the current tax rate stored in N2.If the rate changes, I change N2 and the formulas update automatically.I can make those formulas even easier to understand by giving the cell a name.
Instead of referring to $N$2, I can name the cell "TaxRate" and use =[@[Total Sales]]*(1-TaxRate).That makes the formula much more self-explanatory, particularly when I come back to a spreadsheet months later.It also gives anyone else looking at my workbook some context.
A carefully named cell is much easier to understand than a mysterious cell reference.Related I stopped manually naming ranges in Excel—this little-known shortcut does it in seconds Create dozens of named ranges at once, write clearer formulas, and even update existing formulas automatically.Posts 2 By Tony Phillips Making one worksheet do everything It gets harder to manage as workbooks grow Close It's tempting to put everything on one worksheet.
Raw data, calculations, notes, lookup lists, and the final report can all live together, and for a small spreadsheet, that can be perfectly reasonable.As a workbook grows, though, it becomes much harder to tell what can be changed safely and what something else depends on.I now tend to give different parts of a workbook their own jobs: a Source sheet for the raw data, a Logic sheet for the calculations and transformations, and an Interface sheet for the results.
I call this the three-tab rule.I don't follow this as a rigid rule for every spreadsheet, but it works for anything I'll be updating, sharing, or coming back to later.It also ties together the other habits I've already mentioned.
My source data doesn't have merged cells or blank rows, my calculations don't have assumptions buried inside formulas, and my interface can use formatting without affecting the underlying data.Keeping those jobs separate makes it much easier for me to change one part of a workbook without accidentally breaking another.A little restraint goes a long way I'm not saying Excel is full of features that are waiting to break your spreadsheet.
Quite the opposite: the key is knowing which tool to use and when.There are also plenty of lesser-known Excel features that can help you stop doing things manually.Custom Lists can make Excel fill and sort data your way, the Camera tool can create live snapshots for dashboards, and Analyze Data can uncover useful insights from your data.
Read More
