Do you want to spruce up your report? Spreadsheets don’t have to be boring, disorganized, and hard to understand. Instead, you can learn how to merge cells in Excel to make your spreadsheet layout attractive.
Microsoft excel is the sought-after spreadsheet system that allows you to organize your data in rows and columns. This system boasts of a merge-cells add-in that will enable you to combine data from multiple cells into a single cell in a row or column.
You can organize and manipulate the data by merging the rows and columns to suit your report. What’s more, Excel allows you to unmerge the cells.
Learning how to merge cells in excel is easy. If you want a well-organized and attractive spreadsheet, then follow the steps outlined below.
Reasons for Merging Cells
Merging cells not only gives you a well-organized document, but it also customizes your spreadsheet giving it a clean look. You should learn how to join cells in excel to center your title across all columns in the given document or report. Also, merging organizes and combines several sections under a single heading. What’s more, if you accidentally merge rows and columns, then you get to split or unmerge them. It is wise to learn the ropes of merging first because merged cells can be split. Therefore, merging cells organizes your document making it easy to read and understand.
There are four primary merge options available in Excel.
- Merge & Center: This alternative merged cells and aligns text at the center while retaining the top and left most data.
- Merge Across: This option combines cells in columns without changing their alignment.
- Merge Cells: It is the simplest method of merging the selected cells
- Unmerge Cells: It is the opposite of merging because it unmerges or splits the cells.
Let's get started on how to merge cells in Excel
Method 1: Merge & Center Option in Excel
The Merging command is located on the Excel Home Tab.
- First, highlight the cells to be merged by clicking on the first cell to be highlighted, hold down the Shift Key, and then click on the last cell range.
- Afterward, maneuver to the Merge & Center button located under the Home Tab.
- Click on the Merge & Center to merge your preferred cells. However, a warning window appears, cautioning you that proceeding with the merging process will not only discard other values, but it will also retain the left-values only.
- Click OK to proceed if you are comfortable with such changes.
How to Merge Columns in Excel
It is easy to merge columns in Excel. Select the multiple columns you wish to join and repeat the above process.
- The first step is to highlight the two columns you want to merge. For instance, you may want to combine the “First Name” and “Last Name” like in the example below.
- Take note that selecting the Merge & Center button will provide you with one large cell that has only one value.
- Click on the Merge & Center drop-down menu and choose Merge Across. Instead of a single warning, Excel will give you several warnings depending on the number of rows selected.
- Click OK for each row to get a newly merged column, as shown below.
Method 2: Merging Multiple Cells using the Format Method
Another easy method to merge cells is using the format menu.
- Highlight the multiple cells to be merged.
- Then, On the Home Tab, click on “Alignment”.
- Alternately, Right Click on the selected text.
- From the menu, click on Merge Cells. In both options, a Format Cells window will be displayed.
- Locate the Alignment Tab and check on the “Merge Cells” option.
- Then click OK to combine your multiple cells into a single cell.
Disadvantages of Using the Merge Cells on Excels
Although 53% of excel users utilize the Merge Cells Feature, merging cells creates multiple data problems. First of all, it is difficult to copy and paste data. Secondly, it is impossible to highlight a single column that contains numbers as data. Thirdly, the option of Autofill is disabled, which makes it challenging to save on time on Excel. Lastly, since merged cells are not similar to the original cells, you cannot use essential Excel features such as COUNTIFS and SUMIFS. Therefore, the ideal alternative to counter these problems is using the “Center Across Selection” merging option.
How to Merge Cells without Losing Data
Method 1: Center Across Selection
Center Across Selection does not modify and combine cells. Instead, it only aligns the relevant text at the center. Therefore, when merging cells, you don’t lose any functionality such as copy, paste, or Autofill.
- The first step is to select the text you want to join across.
- Next, click on the Alignment Tab in Alignment Group Dialogue and locate the Format Cells found in the dialog box.
- Alternatively, press CTRL+1 to launch the Alignment Group Dialogue box.
- After that, a dialog box will appear, click on the Horizontal Menu, locate, and select the “Center Across Selection” option.
- After clicking OK, the text will appear merged just like in the “Merge Cells”.
However, the only difference is that the cells are intact, including their functionality. Please note that this option only works for horizontal groups. Therefore, you’ll need to merge cells vertically. What’s more, ensure that you join cells with single entries because data from multiple entries may be unsuccessful to emerge.
Method 2: Concatenation Formula
The Concatenation formula is the best option for merging cells if you don’t want to lose your data. What’s more, this formula is the best for joining multiple cells such as “First Name” and “Last Name” into a single cell. However, a new cell will be created for the result.
- First of all, select cell “C2” and apply the CONCATENATE formula (A2,” ” B2) to get the desired results.
- A2 refers to the first cell to be merged, whereas B2 is the last cell to be merged.
- The space between the first name and the last name is represented by the two quotations (“) marks.
Method 3: How to Merge cells using Ampersand (&) Operator
The Ampersand (&) Operator is similar to the Concatenation formula. However, whereas ampersand uses “&” operator function, the latter use the CONCATENATE function.
- First of all, select Cell 2 titled “Full Name”.
- After that, apply the formula =A2&“ “&B3 to give the desired result as illustrated below.
How to Unmerge Cells In Excel
If you need to split the previously merged cells, then you can unmerge them.
- Select the merged cells
- Click on “Unmerge Cells” on the Merge & Center Ribbon under the Home toolbar.
However, unmerging cells always place the data on the top-left cell leaving all the other cells blank. What’s more, data, once lost during merging, cannot be recovered after unmerging the cells. Nevertheless, unmerging solves the vast spaces in your spreadsheets caused by merging rows and columns.
Limitations of Merging Cells using Excel
However, Excel’s primary weakness is that only the upper-left value of cells is retained while all other data is discarded. Although data from one cell is retained, the contents of two or more cells cannot be merged. Consequently, only the data from the upper-left will be kept after merging.
Secondly, Excel only merges cells that form a rectangular shape. For instance, it is possible to combine data from cells C1, C2, D1, and D2. However, it is impossible to merge cells from C1, C2, and B1 only. Lastly, the sort command does not function on the already merged cells.
With that said, Excel is the best spreadsheet system to present or report data. Particularly, learning how to merge cells customizes your document giving it a clean, highly organized, and easy to understand look.
Keep in mind that when using Excel, only data in the top and the left-most cell is used. Data from other cells is automatically deleted. Although merged data can be split, it is advisable, therefore, to back up relevant data for future reference.
If you’re looking for a software company you can trust for its integrity and honest business practices, look no further than SoftwareKeep. We are a Microsoft Certified Partner and a BBB Accredited Business that cares about bringing our customers a reliable, satisfying experience on the software products they need. We will be with you before, during, and after all the sales.
That’s our 360 Degree SoftwareKeep Guarantee. So, what are you waiting for? Call us Today at +1 877 315 1713 or email email@example.com. As well, you can reach us via Live Chat.