Merge Cells in Excel

Excel is handy for various purposes, making it a powerful tool. These functions can create a data table or Excel spreadsheet more appealing to investors, students, etc., and are a great tool to know how to use the data.

Excel is not only used in Finance classes but in a broad range of classes and is a fundamental tool for students to learn and interact with, so when they enter the workplace, they are highly familiar with it. 

The merge and center function is one of these functions that help design tables and other data-driven materials. The merge and center function allows you to combine different cells to create one. 

Throughout this article, the merge and center function will be explained. Videos, practice problems, and similar functions will be included for help if there is a need when using this function in Excel. 

Follow along and learn more about using the merge and center functions and related functions in Excel. 

This function is used in Excel to create a label across a dataset that spans over columns or rows. 

For example, cells A1, B1, and C1 were merged to create one larger cell by highlighting the three cells and pressing merge and center. The steps to combine two or more cells:

• Select two or more cells you want to connect by highlighting the cells.
• On the Home tab, under alignment, press merge and center.
• Under this tab, there are also many other merge and center functions, such as:
  • Merge Across: combining cells without any alignment needed
  • Merge Cells: combining cells without any alignment necessary, Similar to the merge across function
  • Unmerge Cells: reverting any of the merge and center functions

Syntax for Merge & Center Function

As there is no syntax for the merge & center function in Excel, under the home tab, there is an easy button that users can press when merging cells, which is extremely helpful. Depending on what the goal of the merge or unmerge is, the buttons will be listed like this:

• Merge & Center
• Merge Across
• Merge Cells
• Unmerge Cells

The first one is the basic merge and center function.

The second function is the 'merge across' part. Similarly to the merge and center function, this function will merge the cells and move the content of the cells throughout the cells rather than center them.

The third function is 'merge cells,' where no alignment occurs. This function merges the highlighted cells.

The fourth function under this tab is 'unmerge cells.' This is where users reverse all of the previous three functions described. To do so, highlight the merged cell (or cells) and press this button, which will revert to the number of cells that were there before the merge took place.

These four functions are extremely useful in creating data tables or any flow in an excel sheet presented to a manager, a potential investor, students, etc.

Unmerge Cells

There are two different ways to unmerge cells in Excel. The ways are: 

• Using the ribbon in Excel
• Keyboard Shortcut

The first way to unmerge cells in excel is to highlight the merged cell and choose to unmerge the cell from the dropdown menu under the merge & center menu. After the cells are unmerged, they will return to the original format of the two or more merged cells.

The photos below show a step-by-step process through the Excel ribbon.

This way may be suitable for new users to learn how to navigate Excel and learn more about the different ribbons and where everything is located compared to the second way to unmerge cells, which will be discussed next.

The second way to unmerge cells in Excel is through a simple keyboard shortcut. Highlighting the cell and then pressing Control + Z on most computers and desktops will unmerge the cells.

This will keep all of the data rather than unmerge the cells that were previously one by reverting them to what they looked like.

This is generally quicker than using the ribbon in Excel, but new users of Excel may want to learn the foundational aspects before going to the shortcuts.

Distributing Text from One Cell into Other Cells

Distributing text from one cell into others also falls under Excel's merging and unmerging functions. This could be useful when creating lists or separating different types of non-numerical data.

An example of this could be splitting a list of full names into two lists of first and last names.

Organizations can use this for filing purposes. Excel does this by distributing text in one cell into multiple cells across an Excel sheet. This does not work with numerical data, only text data, so if numerical data is entered, some error message will be provided in Excel.

These are the steps for distributing text from one cell into other cells:

1. Select the cell(s) that contain you would like to split.
   Note: No more than one column can be selected
2. Go to Data, then Text to Columns, which displays a Convert Text to Columns Wizard.
3. Press Delimited - Characters such as commas or tabs separate each field, then Next
4. Press Space and Comma, then Next
5. Press the Column Data Format Text and select the cells containing the split data
6. Press Finish

Merge Cells in Excel: Practice Problems

The data set below is given to you in Excel. What is wrong with the title of the Data Set, and What function should be used to fix the issue? After that is identified, fix the problem using the process identified previously.

The solution is posted below:

To fix the problem in the data set given in the practice problem, the merge and center function should be used to make sales centers in one cell above the months.

The photo below shows the correction of the data set given in the original practice problem above and the steps to get there.

1. Paste Data Set into Excel.
2. Highlight "Sales" in A1 and cells B1 and C1.
3. Go to the Home tab and Press the Merge and Center Ribbon.

PRACTICE PROBLEM #2

Using the same data set from Practice Problem #1 (listed below for ease), how do you revert the changes to the original format? Why would someone want to do this? 

The solution is posted below.

PRACTICE PROBLEM #2 (SOLUTION):

Unmerging cells in Excel can be helpful if the data set changes or different formulas are used for something.

The unmerge cell function should be used to switch back to the original data set. This function can be found under the home tab at the top of Excel. The steps and solution are

1. Highlight the merged cell that you want to unmerge.
2. On the Excel Ribbon, go to Merge and Center and press the downward arrow. 
3. Press Unmerge Cells

PRACTICE PROBLEM #3

There is a list of names given below. Using the information learned throughout this article, separate the list of names into first and last names. After doing so, merge and center the list's title above the name set.

Discuss what needs to be done and then demonstrate the Excel work. The list of names is below:

The solution is posted below.

PRACTICE PROBLEM #3 (SOLUTION):

This problem illustrates distributing one text from one cell into two other cells. This problem also ties in with the function merge and center concept, which is generally what this article discusses.

Focusing on the problem's first concept, distributing data can only be done with non-numerical data. First and last names are a great example, as this is usually done when spreading text across cells.

The steps to complete this problem are shown below, and following the completion of that concept will lead to the merge and center function for reinforced practice from the first practice problem.

1. Paste the table into Excel. 
2. Highlight the cells being distributed into the other two cells.
3. Under the Data tab, press Text to Columns with highlighted cells.
4. Click "Delimited - Characters such as commas or tabs separate each field" and press Next.
5. Select the Delimiters as "Commas" and "Spaces" and press Next.
6. Click the cell destination where the first name should be moved and press Finish.
7. The finished data table with the distributed cells should look like this:
8. To merge and center the title of the list of names, highlight the cells that should be connected (A1, B1, and C1).
9. Move to the home tab in Excel, and find the Excel Ribbon Merge & Center.

Researched and authored by Emma McCarthy | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• REPLACE Function
• Scatter Plot
• Secondary Axis
• Slicer in Excel
• Types of Graphs