Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells

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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
10 comments on “Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells
  1. Richard says:

    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…

    • Tom Urtis says:

      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.

  2. Banala Ramesh Babu says:

    Thank You for the tips, I used to follow merger option. Now I’ll follow Center across selection. Thank you

  3. Jordan says:

    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.

    • Tom Urtis says:

      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.

  4. Rod says:

    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.

    • Tom Urtis says:

      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.

  5. Juan Gonzalez says:

    This is great for a “label” but what if the merged cell is the data field?

    For example:

    Cell A2:A4 = OPERATOR NAME (the label spans 3 cells) Your solution works for the visual effect.

    Cell A5:A7 = Juan Gonzalez (the data spans 3 cells) Can’t paste into this field and have it centered.

    • Tom Urtis says:

      Why would you set up your sheet that way? Your example defies the logical rules of a table or source list design. Each column (field) should only have a unique label. Why you would have “OPERATOR NAME” in three cells is not only ill advised but a mess of errors begging for a place to happen.

Leave a Reply

Your email address will not be published. Required fields are marked *