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.

(picture)

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

As well. So, let’s dive in and start by talking about centering. Centering is very important. Let’s take a look here at the model for a second. When I go into the model, you can see that it’s very, very common in a model. People like to center titles. I have a title here; it’s centered in the Scenarios page. I have a title and it’s centered on the Model sheet. As I go down, everywhere I go down, I have a title. It says the company name. There’s a company name, and then the sheet name right underneath it: Revenue Schedule. As I roll down there is a Cost of Production Schedule, and as I roll down there’s an Income Statement. You get the idea: the Cash Flow Statement, etc. But the point is, I chose to center titles.

Now, let’s go take a look at the Executive Summary. First of all, one tip that I didn’t specifically mention but it’s important to note is that in a good model you only ever want to enter the company name once. So the company’s name has been entered up front here on the cover sheet—Henderson Manufacturing. Every other time we see the company’s name, it’s linked back. On the Summary sheet, it’s linking back to the Cover sheet. I literally just entered an equal sign, clicked back on the prior sheet, linked that cell, and pressed Enter. Obviously, we don’t want to type the company name more than once because it might change, or you might reuse the model. I typed the name in once, and every other time you see it, it is linked back.

Now, the point that I want to make is this: I chose to center this title. I wanted to center it across this range. What a lot of people will do is go to the Home menu and click on the little button that says Merge and Center. Watch what happens when I click Merge and Center: it centers the title, but it merges all of those cells into one giant cell, just like this. Now, it looks OK—it looks just like these other pages—but on this sheet it’s not merged. You can see I still have a bunch of cells here. It’s very problematic and it’s very dangerous to merge cells like that. Let me tell you why. When you merge a cell, it becomes very difficult to insert more columns, you can’t delete columns, and it’s hard to build formulas. It’s really not ideal to have a merged cell like that.

What we want to do instead is use Center Across Selection. Let’s do that. First, I’m going to unmerge this and go back to the way it was. Fine. I start in the first cell in column B, hold Shift, press the right arrow, and select the range from column B to column Q—the range over which I want to center the title. With that range selected, I press Control + 1 to open the Format Cells dialog box. Many people right-click and choose Format Cells, but that’s time consuming. Control + 1 opens it instantly. On the Alignment tab, under Horizontal alignment, I choose Center Across Selection. When I click OK, look what happens: it centers the title across the specified range, but it did not merge any cells. All of the cells remain intact—much more elegant and much easier to work with down the road. If you ever have a spreadsheet with merged cells, I encourage you to unmerge them and use Center Across Selection instead.

That’s the first tip. It sounds like a small topic, but merged cells really cause lots of problems—they invalidate and ruin many models. Please do not merge; use Center Across Selection.