Comparing Excel's Merge Cells and Center Across Selection Features

Comparing Excel's Merge Cells and Center Across Selection Features

Merge Cells and Center Across Selection are long-standing features in Excel. You can use both these features to change the appearance of data in a spreadsheet. More specifically, you can use them to present data so that it centers across multiple columns. However, your choice to use Merge Cells or Center Across Selection matters! Choosing the wrong option can create undesirable outcomes in your spreadsheets. In this tip, you will get a fair comparison of these two features. Further, you will understand why one is preferable to the other in many – but not all – situations.

Excel

Merge Cells And Center Across Selection Fundamentals

Both features – Merge Cells and Center Across Selection – appear to do the same thing. More specifically, both make your data look like it centers across a range.  For example, Excel users might desire this attribute when they want a report header centered across multiple columns. However, these two techniques operate much differently.

Merge Cells Changes the Structure of the Worksheet

The Merge Cells feature changes the worksheet by deleting cells so that your data centers across the desired range. To illustrate, consider the data pictured in Figure 1. In it, the user selected the data in cells A1 through E1. Then, the user chose Merge Cells from the Home tab of Excel’s Ribbon. Upon doing so, Excel consolidated all the data into cell A1 and removed cells B1 through E1 from the worksheet. Additionally, Excel automatically centered the data in cell A1. However, note that because Excel deleted cells B1 through E1, the width of cell A1 now “stretches” through column E. As such, Excel’s Merge Cells feature changes the structure of the worksheet.

Merging Cells in Excel
Figure 1 - Merging Cells in Excel

Because Merge Cells changes the structure of the worksheet, this technique can have some downside. For example, when Excel merges cells, you can no longer sort on the column where the data resides. To illustrate, notice the “N/A” entry in Figure 1. Technically, that entry lies in cell D7. However, because Merge Cells was used to center it across columns B and C. Therefore,  we can no longer sort the data in the report, illustrating one of several drawbacks associated with Merge Cells.

Center Across Selection Merely Changes Formats

In contrast to Merge Cells, Center Across Selection merely changes cell formats and truly centers data across a selected range. This attribute contrasts with Merge Cells deleting cells from the worksheet. Accordingly, when you use Center Across Selection, you reduce the risk of adverse consequences like those associated with Merge Cells.

To invoke Center Across Selection, begin by selecting the cells you want to center and the range over which you want them centered. Then, as shown in Figure 2, execute Center Across Selection by clicking the dialog launcher in the Alignment group on the Home tab of the Ribbon. Finally, choose Center Across Selection from the Horizontal drop-down list and click OK to complete the process.  Upon doing so, Excel will center the data you selected over the range you selected.

Center Across Selection in Excel
Figure 2 - Center Across Selection

Although Center Across Selection does not have the downsides of Merge Cells, it also does not have all the power either. The most significant limitation associated with Center Across Selection is that it works on columns only. In other words, if you need to center data across multiple rows, you must use Merge Cells for that task.

Summary

Both Merge Cells and Center Across Selection are long-standing features of Excel. Further, both these features appear to do the same thing. However, as you have learned in this tip, these tools operate in decidedly diverse ways. Because Merge Cells deletes cells from the worksheet, in most cases, you should avoid using it, opting for Center Across Selection instead. However, if you need to center data across multiple rows instead of columns, then Merge Cells is the likely tool of choice.

Learn more about Excel by participating in a K2 Enterprises training session. Click here for more information. You can also learn more about Center Across Selection from Microsoft by clicking here.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

You Can View an In-depth Video of this Tip Below

Leave a reply

Your email address will not be published. Required fields are marked *