While it is easy to use Excel's Merge & Center feature to center titles across a range of cells, there is a better way used by strong financial modelers.
The preferred approach is to use the Center Across Selection option in the Format Cells dialogue box. This feature allows you to:
- Add and remove columns easily
- Use the up and down arrow keys without jumping around
- Use the page up and page down to find cell references in the same column
Step 1
Highlight the cells in which you would like the text to be centered across, while ensuring the text is in the first cell selected.
In the example above we have highlighted cells B1 (which includes the title) to O1. The goal is to have the text centered in this selection.
Step 2
Now press Ctrl + 1 to access the Format Cells dialogue box (Command + 1 on a Mac). Strong financial modelers use keyboard shortcuts to save time when building models.
Step 3
Go to the Alignment tab. The keyboard shortcut to switch tabs is Ctrl + PageUp/PageDown on a PC (Control + Fn + Up/Down on a Mac).
Step 4
Go to the "Horizontal" drop down menu and choose Center Across Selection. This is the final step so click OK when done.
Result
The text is now centered across the selection. Now you can add and remove columns and navigate the model quickly without running into any errors.
Watch this quick video by FMI’s Executive Director, Ian Schnoor, CFA, CFM, to learn why to avoid Merge & Center, and instead use Center Across Selection.
Transcript (Edited for Clarity)
Let’s start with centering. Centering titles is a common practice in financial models. For example, the Scenarios page has a centered title, and the Model sheet does as well. As you move through the workbook, each section begins with a centered heading that combines the company name with the sheet name, such as Revenue Schedule, Cost of Production Schedule, Income Statement, and Cash Flow Statement. The point is that every major section begins with a centered title for consistency and clarity.
Now let’s take a look at the Executive Summary. A key tip to keep in mind is that in a well-structured model the company name should only be entered once. In this example, Henderson Manufacturing is entered on the cover sheet. Every other instance links back to that cell. On the Summary sheet, for instance, the name is simply linked to the Cover sheet using a basic formula. This approach ensures consistency if the name changes and makes the model easier to reuse in the future.
Here the title is centered across a range of cells, but it is not merged. Many users achieve this look by clicking “Merge and Center” in the Home menu. While this does center the text, it also merges all selected cells into one, which creates serious problems. Merged cells make it difficult to insert or delete columns and complicate the creation of formulas. For this reason, merging cells should be avoided when building financial models.
Instead of using Merge and Center, the best approach is to use Center Across Selection. To do this, first unmerge the cells so they return to their original state. Begin in column B, hold Shift, and use the right arrow to select the desired range, such as from column B to column Q. With the range selected, press Control + 1 to open the Format Cells dialog box. On the Alignment tab, under Horizontal alignment, choose Center Across Selection. This centers the title across the specified range without merging any cells. The result is more elegant and far easier to work with in the future. Whenever you encounter merged cells, unmerge them and apply Center Across Selection instead.
That’s the first tip. Merged cells may seem harmless, but they create significant problems in financial models. Always avoid merging and use Center Across Selection instead.
