Merge Cells in Excel

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

Author: emma mccarthy
emma mccarthy
emma mccarthy
Emma graduated in May 2022 with her Bachelors of Science in Finance and in May 2023 with her Masters in Business Administration. Emma has a strong skillset in Excel, Bloomberg, and a multitude of other Financial Technology tools. During her time at Quinnipiac University, Emma was very involved in the School of Business where she was a graduate assistant for an entry level finance class.
Reviewed By: Parul Gupta
Parul Gupta
Parul Gupta
Working as a Chief Editor, customer support, and content moderator at Wall Street Oasis.
Last Updated:May 8, 2024

Why Merge Cells in Excel?

Merge Cells is an Excel feature that permits the users to combine two or more adjacent cells into one single, larger cell. The primary use of the Merge function is to format and organize data in Excel spreadsheets.

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 used in a broad range of classes, including finance. It 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 helps design tables and other data-driven materials. It 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 needed 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

Key Takeaways

  • Merging cells in Excel is a formatting feature that allows users to combine two or more adjacent cells into a single larger cell.
  • This feature is commonly used to create headers, labels, or titles that span across multiple columns or rows for better visual presentation of data.
  • While merging cells is useful for creating headers or labels, users can also combine text from multiple cells using Excel formulas such as CONCATENATE or the ampersand (&) operator.
  • Formulas offer more flexibility and allow users to combine text dynamically based on specific criteria or conditions.

How to Merge Cells in Excel

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.

How to Unmerge Cells in Excel

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.

An Alternative to Merge Cells in Excel – Center Across Selection

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. 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 Examples

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.

Merge Cells Example: Sales
Sales
September October November
25,000 30,000 13,010
25,615 30,006 14,576
25,630 32,000 29,933
25,645 33,000 39,032
25,660 30,100 40,503
25,675 31,126 34,039
25,690 31,000 39,493
Merge Cells Example: Sales II
Sales
September October November
25,000 30,000 13,010
25,615 30,006 14,576
25,630 32,000 29,933
25,645 33,000 39,032
25,660 30,100 40,503
25,675 31,126 34,039
25,690 31,000 39,493

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.

Example 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? 

Data

The solution is posted below.

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

Example 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:

Merge Cells Example: Attendance of Career Fair
Attendance of Career Fair
First Name, Last Name First Name Last Name
Cindy, Smith    
Deborah, First    
Zachary, Wang    
Glenda, Ewing    
Anabella, Ritter    
Reis, White    
River, Bennett    
Aizah, Person    
Pippa, Sandoval    
Tamera, Gates    
Arwa, Allison    

The solution is posted below.

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. Name
  2. Highlight the cells being distributed into the other two cells.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:Names
  8. To merge and center the title of the list of names, highlight the cells that should be connected (A1, B1, and C1).Data
  9. Move to the home tab in Excel, and find the Excel Ribbon Merge & Center.

Free Resources

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