Many Excel users struggle with formatting their spreadsheets to give them a polished and professional appearance. Among the challenges these users face is how to center text over multiple columns. Learn how you can use Excel’s Center Across Selection feature to solve this problem quickly and easily in this tip. Also, learn how Center Across Selection differs from Excel’s Merge & Center feature, which often results in “downstream” formatting issues.
Understanding the Issue
Suppose you have created a report like the one presented in Figure 1. In this report, you would like to center the “header” information in cells A1 through A3 so that it “stretches” to column F. You would also like to center each of the column headings in cells B5 through F5 within their respective columns.
Figure 1 - Sample Report for Centering Text
Regarding centering the column headings on row 5, that is an easy task. Select those cells and click the Center icon in the Alignment group on the Home tab of the Ribbon. It’s centering the heading that causes problems for many Excel users.
Why Merge & Center Can Be Problematic
To center text across multiple columns, it is tempting to use Excel’s Merge & Center feature. If you choose to work with this tool, select the text you want to be centered and the range over which you want to center it. Then from the Alignment group in the Home tab of the Ribbon, click Merge & Center. Upon doing so, the text you selected centers over the range you selected, as shown in Figure 2.
Figure 2 - Using Merge & Center to Center Text Over Multiple Columns
At first glance, this method appears to be the perfect solution. However, there are some downsides associated with Merge & Center. Among the more significant of these are the following.
For the reasons outlined above, and perhaps others, in most cases you should avoid using Merge & Center when formatting your spreadsheets. Instead, use Center Across Selection.
- Once you use Merge & Center, you cannot insert columns into the merged range.
- If you use Merge & Center, you cannot sort data within the merged range.
- Merge & Center limits your ability to paste data into the merged range.
For the reasons outlined above, and perhaps others, in most cases you should avoid using Merge & Center when formatting your spreadsheets. Instead, use Center Across Selection.
Center Across Selection Is the Better Solution
Fortunately, Excel offers an alternative to merging cells for formatting effect, and that solution is Center Across Selection. When you use Center Across Selection, your formatting results appear the same as if you had used Merge & Center. However, you do not experience any of the adverse side effects of Merge & Center.
To format your data using Center Across Selection, begin by selecting the text and the range over which you want it centered, as shown in Figure 3. Importantly, note that you can select text from multiple rows when formatting with Center Across Selection. That is a significant difference between this feature and Merge & Center.
To format your data using Center Across Selection, begin by selecting the text and the range over which you want it centered, as shown in Figure 3. Importantly, note that you can select text from multiple rows when formatting with Center Across Selection. That is a significant difference between this feature and Merge & Center.
Figure 3 - Selecting Text for Center Across Selection
After you select your text and the range over which you want it centered, click the dialog launcher in the Alignment group on the Home tab of the Ribbon. Then choose Center Across Selection in the Horizontal list and click OK to complete the process. Figure 4 illustrates the steps for applying the Center Across Selection format.
Figure 4 - Applying Center Across Selection
Once you click OK to complete the process shown in Figure 4, you see that the data you selected centers over the range that you also selected. Further, you can sort the data, insert columns into the range if necessary, and paste data into the range without restrictions. In short, when you use Center Across Selection, you do not experience any of the adverse side effects associated with Merge & Center.
Summary
Merge & Center remains a popular feature in Excel, probably because of its’ easy access on the Home tab of the Ribbon. However, when you use Merge & Center, you open the door to other formatting and data manipulation issues, such as inserting columns into the range. Therefore, as an alternative, consider using Center Across Selection instead. Although Center Across Selection is a little more challenging to find in Excel’s user interface, it provides superior results compared to Merge & Center, particularly considering there are no negative consequences associated with Merge & Center. So, the next time you need to center text over multiple columns, give Center Across Selection a try. I’m confident in your response to the results!
RSS Feed