Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells
When you want to center a cell’s value across several columns, please avoid the Merge Cells method. Merged cells cause more programming and design headaches than any single feature in Excel.
There is a better way, by formatting the cells using “Center Across Selection”, shown by example in the following Before and After set of pictures.
To accomplish this, simply select the cells where you want the header value to be centered across. In this example, the header value is in cell A1, and range A1:D1 has been selected. Next, right-click the selected range, and from the popup menu, click on Format Cells.
In the Format Cells dialog box, click onto the Alignment tab.
Click the drop down arrow for the Horizontal field in the Text Alignment section.
Select “Center Across Selection”, and click OK.
Consider my mind blown! Honestly, how did I not already know about this?!?! Thanks for the great tip, Tom! Ohh, but that merge and center button is so tempting, and it’s right there…
Thanks for following my page and for your kind words. Avoiding merged cells is always a good idea. Merged cells cause more havoc behind the scenes than most users realize. Center across selection is a much better option.
Thank You for the tips, I used to follow merger option. Now I’ll follow Center across selection. Thank you
Thanks for your commen!
The logic behind this is all well and good, but I’ve gotten nothing but headaches trying to center across cells of different widths rather than merge. They format to a certain size and this does not change the presets for centering, so the title is thrown far beyond the center-point I was hoping to use. I found one solution but it was fairly poorly described and I would appreciate a better description of what to do to get it to truly center over the cells I explicitly selected in my particular format. Thank you.
Until your comment here, “nothing but headaches” is all I have ever heard and experienced myself as a developer about merged cells. They are the angel of death from a programmer’s standpoint. I have not experienced or seen what you are talking about, regarding problems with centering across columns of different widths. The distance between the left and right edges of the associated cells to the first and last characters of the text is the same meaning that yes, the text is centered across the range. There are two “centers” — one is the true center and the other is a visual center. Maybe the proportional fonts you are using are causing a perceived visual distortion, which can happen. In any case, in my 26 years of programming Excel, I have not run across a feature that causes more negative issues and is more avoidable than merged cells.
I am trying to highlight some of the text that is returned from an if statement but cannot figure out how. Could anyone help?
My If Statement code:
=IF(E29=”Yes”,I29=”Yes”,M29=”Yes”),”Please provide “&E21&” “&I21&” and “&M21&”‘s injury information:”,””)
I would like the returned text to be black and have “&E21&” show up as read when the message displays in the cell.
Formatting individual words or characters that are returned from a formula is not possible. The text to be selectively formatted must be constant text, not in a cell because a formula shows it there.