Center Across Selection is a useful formatting tool in Excel that allows you to center your data across multiple cells without merging them.
This feature comes in handy when you want to present your spreadsheet in a more organized and visually appealing way. It is particularly helpful when working with headers or titles that span across several columns in your worksheet.
It’s important to note that Center Across Selection is different from the Merge & Center option, which combines cells into one larger cell before centering the content. While both functionalities serve a similar purpose, Center Across Selection maintains the individual cells’ integrity, which can be advantageous when referring to specific cells in formulas or when sorting and filtering data in your spreadsheet.
How to Use Center Across Selection in Excel?
Let me show how to use Center Across Selection using a practical example.
Below, I have a dataset where I have the names in column A, the quarterly sales values in columns B to E, and the total sales value in column F.
Jose started working at the company at the beginning of the Q3. So, I need to enter “n/a” in the middle of cells B3 and C3.
I can apply center Across Selection in this scenario.
To apply “Center Across Selection”, follow the below steps.
- Select the cells where you want to center the text. In this case, I select cells B3 to C3.
- Press “Ctrl + 1” to open the “Format Cells” dialog box and go to the “Alignment” tab.
- Expand the “Horizontal” drop-down and select “Center Across Selection”.
- Click OK.
Now, you can see that “n/a” is in the middle of cells B3 and C3.
Center Across Selection vs Merged Cells
The key difference between Center Across Selection and merging cells in Excel is that Center Across Selection centers the contents of a cell across multiple cells without actually merging them together, while merging cells combines multiple cells into a single cell.
When using Center Across Selection, the selected cells maintain their individual cell references, so formulas and functions that refer to those cells will still work correctly.
Meanwhile, merging cells combines the referenced cells into one, so any formulas using the individual cells may break or return unexpected results.
Another reason to use Center Across Selection instead of Merge Cells options is that when you have merged cells, it may not play well when you want to sort that dataset.
Let me show you what I mean with an example.
Below, I have a dataset where I have the names in column A, the quarterly sales values in columns B to E, and the total sales value in column F. You can see that Cells B3 and C3 are merged in this table.
So, what I’d like to do now is organize the names based on the total sales column (Column F), with the highest sales at the top. To do this, I’ll expand the dropdown in column F and choose “Sort Largest to Smallest.”
When I try to sort, I’ll get an error (To do this, all the merged cells need to be the same size).
You’ll get this message because there are some merged cells in the table.
To get around this, I can use “Center Across Selection” to format cells B3 and C3 instead of “Merge & Center.”
So, the first step is to remove the “Merge & Center” from cells B3 & C3. I can do this by selecting the merged cells and clicking the “Merge & Center” icon in the “Home” tab.
Next, I apply “Center Across Selection” to cells B3 and C3.
Now, I’m all set to sort names based on the total sales column (Column F).
Limitations of Center Across Selection in Excel
There are a few limitations to using Center Across Selection in Excel:
- It only works horizontally across columns, not vertically down rows
- Copying and pasting data can be problematic. Pasting data over a Center Across Selection range may cause errors.
- It does not allow the merging of cell contents like text. Center Across Selection just centers the contents individually across columns, and it does not actually merge text.
- Formatting is not unified. Each cell maintains its own formatting when using Center Across Selection, compared to Merge Cells, which applies formatting uniformly.
- It can be slower for large ranges than Merge Cells. Since Center Across Selection does not actually merge anything, it has to individually format each cell rather than treating the range as one unit like Merge Cells.
Also read: How to Find Merged Cells in Excel?
Frequently Asked Questions about Center Across Selection
Below are some common questions people have about the center across selection feature in Excel.
Can you use Center Across Selection in Excel Online?
Yes, you can use Center Across Selection in Excel Online. To do this, first select the cells in which you want to center the text across. Then, right-click the selection and choose “Format Cells.”
In the Format Cells dialog, go to the Alignment tab, and under “Horizontal,” choose “Center Across Selection.” Finally, click “OK” to apply the changes.
What is the keyboard shortcut for Center Across Selection?
The keyboard shortcut for Center Across Selection is Ctrl + 1. After selecting the range of cells where you want to center the text, press Ctrl + 1 to open the Format Cells dialog box.
Navigate to the Alignment tab, and under “Horizontal,” choose “Center Across Selection.” Click “OK” to apply the changes.
How to vertically center text without merging cells in Excel?
I don’t think there is a way to vertically center text without merging the cells. Center across selection only works for horizontal text (rows) and not vertical cells (columns). The only way I can think of doing this is to merge cells.
Why might Center Across Selection not work properly?
One possible reason it may not work is when the cells may already be merged. In such a case, you need to first unmerge the cells and then try using Center Across Selection.
Is Center Across Selection available in Google Sheets?
Yes, Center Across Selection is available in Google Sheets. To use it, select the range of cells you want to center the text across. Then, click “Format” in the menu bar, followed by “Align” and “Center Across Selection.” The text will now be centered across the selected cells, similar to how it would be in Excel.
Other articles you may also like:
