Merge Columns Excel

Friends don’t let friends merge cells! This is something you hear often among Excel enthusiasts. People usually merge cells in an attempt to make a spreadsheet look nicer. That being said… not only is the beauty of a spreadsheet less important than its functionality, which is definitely adversely affected by merged cells… but there is actually a way to alter the appearance identically to merging cells without all of the many disadvantages that come with merged cells. Let’s check it out.

If you would like to follow along with my demonstration below, here is an Exercise file: MergedCells

This is a fictional list of students and grades, with some merged cells at the top.

The first row of data contains 3 sets of merged cells: A1 with B1; C1 with D1; and E1 with F1. If you select any of these, notice in your Alignment group that Merge and Center is selected.

Download excel sample file for this tutorial. Merge-two-columns with blanks.xlsx (Excel 2007 Workbook.xlsx) Functions in this article: ISBLANK(value) Checks whether a reference is to an empty cell and returns TRUE or FALSE. MIN(number1,number2) Returns the smallest number in a set of values. Ignores logical values and text. Excel’s “Merge Cells” function works like “Merge & Center” – without the addition of centering. Select the cells you want to combine and then click “Merge Cells.” The text in the original cell will not be automatically centered, and will remain left or right-justified. Merge And Combine Rows Without Losing Data In Excel Excel only keeps the data in the upper-left most cell, if you apply 'Merge & Center' command (Home tab Merge & Center on the Alignment panel) to merge rows of data in Excel. Users have to use another method to merge multiple rows of data into one row without deleting data. Select the cells that you want to merge. You select multiple cells in Excel by holding down the mouse button and dragging the cursor across columns or rows. You can combine two or more table cells located in the same row or column into a single cell. For example, you can merge several cells horizontally to create a table heading that spans several columns. Select the cells that you want to merge. Under Table Tools, on the Layout tab, in the Merge group, click Merge Cells.

Perhaps someone did this in an attempt to make their spreadsheet look less cluttered? Let’s see why this might have been a bad idea.

There are actually quite a few ways that merged cells can be problematic. Here are just a few.

Sort and Filter

Columns

Let’s say I would like to use the custom Filter buttons we play with in Excel Essentials. You want to filter by all students with an A.

1. Click anywhere in the top row, and on the right side of the Home tab, select Sort and Filter, and Filter.

2. Now, Go to the dropdown created next to Grade.

Normally I would have the opportunity to filter by letter grade, but because E1 and F1 were merged cells, Excel instead only offers for you to filter by grade %. Not very helpful.

PivotTables

Maybe instead, we can make a PivotTable from the data, and pivot by the letter grade? Go to the Insert tab, and select PivotTable.

What is this? We are receiving an error because we don’t have true column labels (header row) when cells are merged like they are in our top row.. this means that Excel doesn’t know what our categories are to create a PivotTable.

(Shameless plug: come to an Excel: Pivot Tables training if you would like to learn more.)

Formulas

This is probably the biggest one for me. Let’s say I want to count the number of 22 year old students in my class. No problem! Let’s do a CountIf formula.

In I1 I entered =Countif( … then I tried to select my range, column D…. look what happens:

Excel doesn’t want to allow me to include column D alone… it wants to include Column C as well. How annoying! We could probably find our way around this formula issue, but even then, I guarantee these merged cells will get in your way with a future formula.

Macros

It is worth mentioning that there are macros that can be interfered with when you use merged cells; it depends on what type of macro you are building.

All in all, merged cells are just not worth the trouble.

If you are truly attached to the look of merged cells, there is another option. It is called Center Across Selection.

  1. First, let’s undo the merged cells. Select the merged areas, then go to the Home tab, Alignment group, select the dropdown for Merge and Center, and select Unmerge cells.

2. Select A1 and B1, and Right Click on top of them. Select Format Cells.

3. In the popup screen, go to the Alignment tab, and click on the dropdown next to Horizontal. Select Center Across Selection. Click OK.

4. Repeat this step with C1 and D1 selected, then E1 and F1 selected. Appearance wise, it will look just like merged cells.

Combining 3 Cells In Excel

This still will be somewhat limiting; for instance, you may still have difficulty with a PivotTable unless you convert this to a Table first, but you will not experience nearly as many drawbacks as merged cells.

Whether you choose to center across or merge cells, I think it is an important question to ask yourself, why are you wanting to do this? Is it truly necessary? When at all possible, I would recommend avoiding either of these practices. I understand the desire to beautify a workbook, but clearly labeled columns with long lines of uninterrupted data are the truly beautiful spreadsheets. Their beauty is in their functionality; and when functionality is lost, nobody will really care much about how the top row looks. Just a thought, from someone who has “unmerged” many cells in many peoples’ spreadsheets over the years.

What do you think? Has this convinced you to unmerge and never merge again? Either way, I will be here to help you.

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

Sheree Smith
(First Power User of the decade!)

Here’s a question from Nanda:

“I need to copy list of names available in spreadsheet into a simple text document to then import it into a legacy information system we use at work. But when I copy the Excel columns to the clipboard and then paste in Notepad, it all goes wrong. Can you help to merge the first and last name into the same column so i can then export it to the Text file”

Sure. Read on for the details.

Columns

Merge columns using the concatenation function

Let us use a simple example to showcase the process. You’ll obviously need to apply the steps below to your data.

  • Open the Excel spreadsheet that contain the columns you might need to merge as shown below:
  • In this example the first name and the last name has to be merged in the Full Name column.
  • In the C2 cell type =CONCATENATE (A2, ” ” ,B2).

Here A2 is Vinod and B2 is Mvd. The ” “ represents a space between A2 and B2.

  • Now we can see the merged name.
  • After this just drag the green bottom pointer to the end of the names.

Concatenate using Excel Auto fill

Using the auto fill functions we can easily merge names into one column. No need of using the formulas. Just by entering the column number we can merge the names in one cell. Read further for the procedure.

  • Open your spreadsheet and place the cursor click on the blank space where the names has to be placed.
  • Now, click on Home in the ribbon and select the drop down box of Auto Sum in the Editing menu.
Columns
  • Select more functions, in the select category click All.
  • Search for concatenate function and click OK.
  • In the text 1, select the column by clicking on the icon in the text box.
  • Similarly for other text columns and select OK to merge the columns.

Merge Columns Excel 2016

That’s it. This is how we can merge names in same column.

Merge Columns Excel 2010

Thanks for reading.. 🙂