Mastering Excel 365 Center Across Selection for Perfect Formatting

Create centered text without the complications of merging cells

Financial models are decision-making tools, so it’s important to have well-constructed and accurate models. However, it’s also important to apply proper formatting best practices. A poorly formatted model reflects negatively on the model designer, which will influence the delivery and use of the ultimate analysis.

Therefore, while we need to make sure the model is as accurate as possible it must also be presentable. One of the major formatting issues in Excel has to do with how to center titles across multiple columns.

What is Center Across Selection in Excel 365?

Center Across Selection is a formatting feature in Excel that allows users to center text across multiple cells. Center Across Selection differs from the similar Merge & Center option in that Center Across Selection maintains the integrity of individual cells while still achieving the ability to center text horizontally.

In contrast, Merge & Center actually combines two or more adjacent cells into a single larger cell. The text in the merged cells is then centered.

Both formatting features are commonly used when formatting titles or headers in Excel. However, Center Across Selection is considered the better option by many Excel power users.

Why Do We Use Center Across Selection?

The Center Across Selection feature has several advantages over other formatting methods, especially Merge & Center:

• Better Cell Integrity: Unlike merging cells, which can create data manipulation issues, centering text across selection maintains the functionality of the individual cells.
• More Flexible Formatting: Excel users can easily modify the centered text without impacting other adjacent cells.
• Formula Functionality: Centering across selection maintains all of the proper, original cell references. In contrast, when referencing cells that are merged, the reference will be to the upper-left cell in the merged selection (unless a cell reference is manually entered, which is not recommended).

How to Apply Center Across Selection in Excel 365

Applying Center Across Selection in Excel is easy. The following steps walk you through applying this feature across cells.

1. Select the cells that you want to apply centering.
2. Use the Excel shortcut key “Ctrl 1” to open the Format Cells dialog box (“Cmd 1” for Mac Excel). Alternatively, you can access Format Cells by right-clicking with the mouse or going to Home on the Excel ribbon, then selecting Format, then Format Cells (Alt H O E).
3. Go to the tab labeled Alignment.
4. Under the section labeled Horizontal, select Center Across Selection.
5. Press the OK key in the dialog box.

Note that the text or data to be centered needs to be in the first cell in the selected range.

Common Use Cases for Center Across Selection

Below are some of the most common use cases for Center Across Selection.

Headers and Titles

Center Across Selection is perfect for formatting headers and titles across multiple columns. This feature improves model understanding by creating a professional appearance and calling attention to key points or topics. Common headers and titles include:

• Report headers across multiple columns
• Financial statement titles (for example, Income Statement or Balance Sheet)
• Can be set up to effectively function as section dividers in long spreadsheets

Dashboard Formatting

Center Across Selection allows for professional-looking dashboards, helping users quickly understand key findings and outputs.

• Consistent formatting across different sections and tables
• Professional-looking headers without the complications of merged cells
• Maintains proper alignment of KPI titles and metrics
• Enables clean formatting of chart titles and descriptions

Center Across Selection vs. Merge & Center

Cell Differences Between Center Across Selection and Merge & Center

In the above screenshot, Center Across Selection has been applied to “Actual” as well as “Forecast” (“Forecast” is in cell F5). Note that the text is centered across the appropriate columns, but the cells remain distinct (for example, cell C5 contains the text “Actual” but remains a distinct, separate cell).

In this next screenshot, the text is centered using Merge & Center. Notice how the cell is considered C5, and cells D5 and E5 are merged into C5 and are no longer distinct cells.

Other Key Differences Between Center Across Selection and Merge & Center

Understanding the main differences between Center Across Selection and Merge & Center is crucial for effective Excel use and best-practice formatting:

Key features of Center Across Selection:

• Preserves individual cells, allowing for easier editing and data manipulation
• Center Across Selection allows for seamless copy/paste operations while copying with Merge & Center can be an issue
• Allows for Sorting and Filtering of data, which may be a problem using Merge & Center

Issues with Merge & Center:

• Combines affected cells into one merged cell
• Given the above issue on filtering, merging can cause data manipulation and analysis issues
• Merging may create problems with formulas and can complicate cell references
• Selecting individual columns in the merged range is problematic

Selecting Columns

Center Across Selection maintains the individual integrity of Excel columns. This matters if we want to select specific columns to which the centering is applied. Using the keyboard shortcut Ctrl Spacebar, we can select an entire column. If Center Across Selection is used, then this shortcut can easily select a column, as shown in the below screenshot.

However, merging combines cells so it’s more difficult to select individual columns. Applying the shortcut Ctrl Spacebar in a single column results in the entire merged range to be selected, as shown below.

Since financial analysts or Excel users rarely want or need this, it’s recommended to use Center Across Selection instead of merging.

Limitations of Center Across Selection

While Center Across Selection is superior to merging cells, users should be aware of some limitations.

Center Across Selection only works horizontally across columns and does not work down rows. To achieve a similar look, you basically have to merge rows. You can see in the screenshots below that while “Enterprise Value” uses Center Across Selection, the “WACC” label uses merge.

Additionally, unlike Merge & Centre, there is no button or visual indicator for Center Across Selection on the Excel ribbon. To access Center Across Selection, users need to go through the “Format Cells” dialog box.

Another limitation is that Center Across Selection is not as well-known as Merge & Center. Merge & Center is more commonly used by less-experienced Excel users. Power users who use Excel daily are more likely to use Center Across Selection due to some of the issues with merging cells.

Conclusion

Center Across Selection is an invaluable tool for Excel users seeking professional-looking spreadsheets without the complications of merging cells. By understanding its capabilities and limitations, users can create well-formatted models and analyses while maintaining full Excel functionality. Whether you’re a casual user or a power user, mastering Center Across Selection can significantly improve your spreadsheet design.

Through proper use of the Format Cells dialog box and the Alignment tab, users can achieve a professional design while avoiding the issues with merging cells. As you use this feature and become more familiar with it, you’ll find it’s an essential tool for creating nice-looking Excel models that maintain both their appearance and full functionality.

Additional Resources

Thank you for reading CFI’s guide on Excel’s Center Across Selection. To keep advancing your career and skills, the following CFI resources will be useful:

• FP&A Modeling Best Practices
• Find and Remove Duplicates in Excel
• CFI’s Free Financial Modeling Guidelines eBook
• CFI’s Excel Fundamentals — Quick Start Guide Course
• See all Excel resources