Here are some easy solutions to Excel cell formatting frustrations that I get asked about all the time.
Enter your email address below to download the sample workbook.
1. Force text to wrap to the next line
How often do you add extra spaces between your text to force it to wrap just the way you want, only to change the column width and to have to do it all over again? It’s frustrating and a waste of time.
Thankfully there’s a better way to get your text to wrap to the next line in Excel. Simply press ALT+Enter after each word you want to wrap. This forces the text onto the next line.
Tip: don’t add a space after the last word on each line otherwise it won’t be centered properly.
2. To Merge & Center or To Center Across Selection
How often do you get that error message ‘Cannot change part of a merged cell’?
Or have you ever been in the process of highlighting a series of cells and all of a sudden your mouse scrolls over some merged cells and BAM, the area you’re highlighting is three times the size you want?
I’m sure you’ve had moments where you’re cursing the merged cells for one reason or another. Well there’s a better way to merge cells too.
And it’s called Center Across Selection. To use Center Across Selection:
a. Enter your text in the left most cell that you want your heading in
b. Highlight the cells you want your heading centered across
c. CTRL+1 to open the Format Cells dialog box
d. On the Alignment Tab select Center Across Selection from the Horizontal drop down list
The differences between Merged Cells and Center Across Selection is:
- Merged Cells converts multiple cells into one big cell.
- Center Across Selection keeps the underlying cells as individual cells and simply centers the text for you.
The limitation of Center Across Selection is that in can only center across one row because it’s simply horizontal text alignment. Ah, but there’s a solution to this if you really want your headings to straddle more than one row.
3. Excel Shapes As An Alternative to Merge & Center
Shapes can be found on the Insert tab under Illustrations.
a. Choose the shape you want
b. Drag the mouse pointer to draw the shape the size you want
c. Format it using the new ‘Format’ tab that appears when the shape is selected
d. Write your text in the shape and format your text as you normally would
You can see in the example above that the shape appears to be floating above the cells. This is partly because it has a shadow effect, but also because it actually is floating.
You can still type text underneath the shape (a handy place to put information required for the worksheet, but not required for the reader of the report).
I’m going to do a tutorial dedicated to Shapes, so I’m not going to say much more here. But needless to say, shapes are fast becoming one of my favourite features in Excel for making my reports look professional, while at the same time eliminating the restrictions of Merge & Center.
4. Bonus Excel Cell Formatting Tip – Fill
While fiddling with the cell formatting options I stumbled upon ‘Fill’ in the Alignment Tab.
You can see in the formula bar that I’ve only typed in ‘Fill’ once and Excel has repeated it across the selection. Note: I actually typed in ‘Fill ’ with a space after it so you could read it without going cross-eyed! Mind you, I’m not sure it helped all that much 🙂
At first I couldn’t think of why I’d use the Fill formatting style, but then I thought it would be handy to use it as a dot leader.................................................like this.
Or a star leader ********************************like this, and so on.
Of course this would require you to have at least three columns. The first one with your text in it, the next one with your dots or stars, or whatever you choose, and the third one with your text in.
Alternatively you could use one of the custom cell formats in one of my previous tutorials.
I must say it has been quite a challenge to spell 'center' with an 'er' rather than an 're' since I speak English English, not American English. So in case you're wondering, I did this simply to be consistent with the spelling in Excel 🙂
Got any formatting tips you'd like to share? Please tell us in the comments below.