You might think they’re harmless, but they can generate errors you might miss. 4: Use descriptive text in headers, not numbersĪvoid using just numbers in header cells. You can leave them as text or convert them. Figure A Excel warns you when you store numeric values as text. In this case, the solution is adequate training. When you do run into numeric data stored as text, you can quickly convert it by choosing Convert To Number. Mistaking numeric values for text is harder to do in recent versions because Excel tags them, as shown in Figure A. They just know that their data isn’t adding up right. This problem is easily resolved, but some users don’t realize that Excel isn’t evaluating all their data. Unfortunately, it’s easy to import numeric values as text and not know it.
Some numeric values, such as street addresses, ZIP codes, phone numbers, and part numbers, can be stored as text because you won’t evaluate them in mathematical equations. Avoid hiding rows and columns unless you must. Hidden rows and columns confuse users, cause functions and formulas to seemingly return erroneous data, and can even play havoc with macros and import tasks. Hidden rows and columns create problems because, well, they’re hidden! You can’t consider what you can’t see, but Excel can still evaluate all that data. In non-Ribbon versions, Center Across Selection is a reasonable alternative to merging. You can’t even select a single-column range if there’s a merged cell in it - go ahead, try!ĭon’t hesitate to use merged cells if you really need them, but they will limit what you can do to the cells and even the columns involved. You can’t sort a column with merged cells. In addition, not all cell formats stick once you unmerge a cell. For instance, Excel won’t apply column formats to a merged cell unless you select all the columns that comprise the merge. Merged cells can help you arrange values in a meaningful way, but they come with problems - numerous problems. There’s nothing inherently wrong with them, but they can have far-reaching repercussions. You can reduce future problems by avoiding the following features and behaviors. Usually, it’s because you’ve forgotten about decisions that made sense at the time but that don’t accommodate your changes. Things just don’t work the way you expected. Later, when you make modifications, you run into problems. It’s easy to create a sheet that works perfectly - at first. Little decisions you make when you first build a worksheet can lead to big problems down the road.
Watch the video below to learn how to use the Center Across Selection feature.10 ways to keep Excel from biting you in the butt This means you'll still be able to work with the cells without encountering any of the problems associated with merged cells.Ĭenter Across Selection only works horizontally, so for vertical groups of cells you may still need to merge cells. When you click OK, your cells will look like they've been merged, but they're actually separate cells. In the dialog box that appears, open the Horizontal menu and select Center Across Selection. On the Home tab, first click the small arrow in the bottom-right corner of the Alignment group. Luckily, the Center Across Selection feature can give the same basic look as merged cells without creating problems in your spreadsheet. Because of this, it's generally best to avoid merging cells unless you absolutely have to. However, merged cells are notorious for creating problems in spreadsheets especially if you're trying to sort, copy, paste, or move data. en/excel-tips/find-a-sum-without-using-a-formula/content/ Why you should avoid merging cellsĮxcel allows you to merge several cells into a single large cell, which can be used to center text across several rows or columns. Lesson 10: Why You Should Avoid Merging Cells