Center Across Selection

Let`s check the issues derived by the standard “Merge” effect and how to use the “Center Across Selection” technique to overcome them.

Standard Merge Procedure

Inserting the standard merge effect in your spreadsheet is straight forward so let`s practice with a simple example. The table below measures the online sales performance of a company based on the number of “Leads”, “Prospects” and “Clients” it achieved during the years 2021 and 2022.

The format is not yet looking good, because the headers are not centralized. Let`s then insert the standard merge effect as per steps below:

1. The title “Online Sales Performance is located in cell B2. So, select the range B2:H2, go to the Home Menu and in the field “Editing” click on the option “Merge & Center”.

2. The entire blue range has been centralized. We can then repeat this process to the other headers. Select the range C3:D3 and click on “Merge & Center” again. You have just centralized the header “Leads”.

3. The same process needs to be done for the header “Prospects” (range E3:F3) and “Clients” (G3:H3).

Issues caused by the “Merge” effect

Your table will look much more presentable now. However, a few annoying issues will arise after inserting a merge effect in your spreadsheets which can be avoided with “Center Across Selection”. These issues are:

a. If you try to select only the entire column G by using your mouse or keyboard (starting in cell G16), you will end up selecting the entire column H as well. This is because you merged cells G3 and H3 and Excel automatically expands your selection.

b. If you then try to expand this same selection to the cells above the header “Clients” (i.e. cells G3 and H3), Excel will then automatically expand the selection area to the entire table. This is because you initially merged the range B3:H3 which includes the 2 cells you were tried to select.

c. Another common issue happens when you are trying to swap columns of your dataset. If pay attention to the table of our example, you will notice that the years underneath the header “Prospects” are displayed in the incorrect order. You should have the year 2021 first and 2022 later. If you select the entire column E and drag to the column on the right by holding the SHIFT key to make the swap, Excel will display a message saying, “We can`t do that to a merged cell.”

To avoid all these issues caused by the tool Merge & Center, you can then use the alternative Excel tool “Center Across Selection”. So let`s check how it works.

Center Across Selection – How to apply it

So, let`s select the entire first table by pressing CTRL A. You can then copy it and paste it in cell J2, just so you can have both results displayed side by side. You can then remove the merge effect of the title and the 3 headers by selecting those ranges and clicking on the same icon “Merge & Center” we showed above which will then remove the merge effect for you.

Now to centralize your title again in the blue area on the top of your table, follow the steps below:

1. Select the range J2:P2.

2. Go to the home menu and in the field “Alignment” click on the small icon labelled as “Alignment Settings” as per image below.

3. The menu “Format Cells” will pop up with all the options related to “Alignment” available to you. Note this menu is also accessible with the shortcut CTRL 1.

4. In the first section on top left side of the menu labelled as “Text Alignment”, expand the dropdown of the field “Horizontal”.

5. In the list that pops up, select the option “Center Across Selection” and click on OK.

Here, you just managed to apply the same merge effect, but without all those annoying issues we described before. You can then repeat this process for the headers “Leads” (K3:L3), “Prospects” (M3:N3) and “Clients” (O3:P3).

Improvements from the use of “Center Across Selection”

You can now select any cells that you want, and your selection will not expand automatically. This because each cell address of your ranges is still classified as singe cells (i.e. not merged cells anymore) even though all contents are centralized. If you now try again to swap columns M and N to have the years of the header “Prospects” displayed in the right order, you will now be able to do it easily. Follow the steps below:

1. Select the entire column N and while holding the SHIFT key drag it between columns L and M and let go the mouse.

2. The years related to the field “Prospects” are now in the correct order. However, at this stage you can only see the title “Prospects” in cell N3 and the title “Leads” in cell L3. Let`s correct those titles.

3. Select cell N3, cut it (i.e. CTRL X) and paste it (i.e. CTRL V) in cell M3. The title “Leads” will automatically adapt after this step, because it will now be at the center of the range K3:L3.

4. Now select the range M3:N3 and again apply the option “Center Across Selection”. Your table is now complete with no merge issues anymore.