How To Merge Cells Vertically In Excel
The tutorial demonstrates different techniques to quickly merge two cells in Excel and combine multiple cells row by row or column past column without losing data in Excel 365, Excel 2019, 2016, 2013, 2010 and lower.
In your Excel worksheets, y'all may often need to merge 2 or more than cells into i big prison cell. For example, y'all may want to combine several cells for a better data presentation or structure. In other cases, there may be besides much content to be displayed in one cell, and yous determine to merge information technology with adjacent blank cells.
Any the reason, combining cells in Excel is not as straightforward as it may seem. If at to the lowest degree two cells y'all are trying to join contain data, the standard Excel Merge Cells feature volition only keep the upper-left cell value and discard values in other cells.
Simply is in that location a way to merge cells in Excel without losing data? Of course there is. And further on in this tutorial, you volition detect a few solutions that piece of work in all versions of Excel 2016, Excel 2013, Excel 2010 and lower.
Combine cells using Excel's Merge and Center characteristic
The fastest and easiest fashion to combine two or more cells in Excel is to use the built-in Merge and Centre option. The whole process takes only 2 quick steps:
- Select the contiguous cells you desire to combine.
- On the Home tab > Alignment group, click the Merge & Center
In this example, nosotros accept a list of fruits in cell A1 and we want to merge it with a couple of empty cells to the correct (B2 and C2) to create a large jail cell that fits the unabridged list.
Once you click Merge and Centre, the selected cells will be combined into one prison cell and the text is centered like in the following screenshot:
Join Excel cells into i
Combine multiple cells into one cell
Read more than
Quickly merge cells without whatever formulas!
And go along all your data rubber in Excel
Read more
Other merging options in Excel
To admission a couple more merge options provided by Excel, click the little drib-downwardly arrow next to the Merge & Center button and cull the option you want from the drib-down menu:
Merge Across - combine the selected cells in each row individually:
Merge Cells - join the selected cells into a unmarried cell without centering the text:
Tip. To change the text alignment later on merging, simply select the merged cell and click the desired alignment in the Alignment grouping on the Home tab.
Excel'south merging features - limitations and specificities
When using Excel'due south built-in features to combine cells, there are a few things to keep in mind:
- Make sure that all the data you want to include in a merged cell is entered in the left-most cell of the selected range considering only the content of the upper-left prison cell will survive after merging, data in all other cells will exist deleted. If you are looking to combine two or more than cells with information in them, cheque out How to merge cells without losing information.
- If the Merge and Center push is greyed out, most probable the selected cells are in Edit mode. Press the Enter fundamental to cancel the Edit manner, and and so try to merge cells.
- None of standard Excel merging options works for the cells inside an Excel table. Y'all take to convert a table to a usual range first (right click the table and select Table > Catechumen to Range from the context card), and then combine the cells.
- Information technology'due south not possible to sort a range containing both merged and unmerged cells.
How to merge cells in Excel without losing information
Equally already mentioned, the standard Excel merge features go on the content of the summit-left cell merely. And although Microsoft has made quite a lot of improvements in the recent versions of Excel, the Merge Cells functionality seems to have slipped out of their attention and this critical limitation persists fifty-fifty in Excel 2013 and Excel 2016. Well, where there is no obvious way, at that place is a workaround :)
Method i. Combine cells within one column (Justify feature)
This is a quick and easy method of merging cells keeping all their content. Still, it requires that all the cells to be merged reside in one surface area in i cavalcade.
- Select all the cells you desire to combine.
- Make the column wide plenty to fit the contents of all cells.
- On the Home tab, in the Editing group, click Fill > Justify. This will move the contents of the selected cells to the elevation-most cell.
- Click Merge and Center or Merge Cells, depending on whether y'all want the merged text to be centered or not.
If the combined values spread across 2 or more rows, make the column a bit wider and echo the procedure.
This merging technique is like shooting fish in a barrel to utilize, still it does take a number of limitations:
- Using Justify you can only bring together cells in a single column.
- It works for text only, numerical values or formulas cannot be merged in this way.
- It does non work if there are any blank cells in between the cells to be merged.
Method 2. Merge multiple cells with data in any range (Merge Cells add-in)
To be able to merge two or more cells in Excel without losing data and without extra "tricks", we created a special tool - Merge Cells for Excel.
Using this add-in, y'all tin rapidly combine multiple cells containing whatever data types including text, numbers, dates and special symbols. Also, yous can divide the values with whatever delimiter of your choosing such as a comma, space, slash or line break.
To join cells exactly the way yous want them, configure the following options:
- Choose Cells into 1 under "What to merge".
- Select the delimiter under "Separate values with".
- Specify the cell where yous want to place the result: summit-left, peak-correct, bottom-left or bottom-correct.
- Make sure the Merge all areas in the choice pick is selected. If this box is non checked, the add-in will piece of work like the Excel CONCATENATE function, i.e. combine the values without merging the cells.
Autonomously from joining all cells in the selected range, this tool tin as well merge rows and combine columns, you just have to select the corresponding option in the "What to merge" drop-down list.
To give the Merge Cells add-in a try, you are welcome to download the evaluation version that works with Excel 2016, 2013, 2010, 2007 and 2003.
Method 3. Use the CONCATENATE office to combine two or multiple cells
Users who experience more than comfortable with Excel formulas, may like this fashion to combine cells in Excel. Y'all can employ the CONCATENATE function or Excel & operator to bring together the cells' values showtime, and then merge the cells if needed. The detailed steps follow beneath.
Supposing you desire to combine ii cells in your Excel sheet, A2 and B2, and both cells take data in them. Not to lose the value in the second cell during merging, concatenate the two cells by using either of the post-obit formulas:
=CONCATENATE(A2,", ",B2)
=A2&", "&B2
The formula, withal, inserts the concatenated values in another jail cell. If you practice need to merge two cells with the original data, A2 and B2 in this example, so a few extra steps are required:
- Copy the cell with the CONCATENATE formula (D2).
- Paste the copied value in the superlative-left cell of the range you desire to merge (A2). To exercise this, correct click the cell and select Paste Special > Values from the context menu.
- Select the cells that you want to join (A2 and B2) and click Merge and Center.
In a similar fashion, yous tin merge multiple cells in Excel, the CONCATENATE formula will be just a little chip longer in this case. An advantage of this approach is that you tin separate values with different delimiters inside a single formula, for example:
=CONCATENATE(A2, ": ", B2, ", ", C2)
Y'all tin can notice more formula examples in the following tutorial - CONCATENATE in Excel: combine text strings, cells and columns.
Shortcut for merging cells in Excel
If yous merge cells in your Excel worksheets on a regular basis, yous may find useful the following Merge Cells shortcut.
- Select the cells you want to merge.
- Press the Alt key that provides access the commands on the Excel ribbon and hold information technology until an overlay appears.
- Hitting H to select the Dwelling house tab.
- Press One thousand to switch to Merge & Center.
- Printing one of the following keys:
- C to merge and middle the selected cells
- A to merge cells in each individual row
- K to merge cells without centering
At first sight, the merge shortcut seems a bit long-winded, simply with a little practice you may find this fashion to combine cells faster than clicking the Merge and Center push button with the mouse.
How to quickly find merged cells
To find merged cells in your Excel sheet, perform the following steps:
- Press Ctrl + F to open the Notice and Supersede dialog, or click Find & Select > Notice.
- On the Find tab, click Options > Format.
- On the Alignment tab, select the Merge cells box under Text control, and click OK.
- Finally, click either Find Adjacent to select the next merged cell, or Find All to observe all merged cells on the sheet. If you choose the latter, Microsoft Excel will display a list of all constitute merged cells and permit you navigate between them past selecting i of the merged cells in this list:
How to unmerge cells in Excel
If you changed your mind immediately afterward merging cells, you can quickly unmerge them by pressing the shortcut Ctrl + Z or clicking the Undo push on the Quick Access Toolbar.
To split up the previously merged prison cell, select that cell and click Merge & Eye, or click the little pointer next to Merge & Middle, and select Unmerge Cells:
After unmerging the cells, the entire contents will appear in the top-left cell.
Alternatives to merging cells in Excel
It goes without maxim that merged cells can assist present the data in your Excel worksheets in a better and more than meaningful way… simply they spawn numerous side-effects that you lot may not be even enlightened of. Here are simply a few examples:
- Yous tin can't sort a column with merged cells.
- Neither AutoFill nor Fill Flash feature works if a range of cells to be filled contains merged cells.
- Y'all cannot turn a range containing at least one merged jail cell into a total-fledged Excel tabular array, let solitary a pivot tabular array.
So, my advice would be to think twice before merging cells in Excel and exercise this simply when actually needed for presentation or similar purposes, e.1000. to center the table title beyond the table.
If you lot want to combine cells somewhere in the middle of your Excel sheet, you may consider using the Center Across Selection feature as an alternative:
- Select the cells that you'd like to bring together, B4 and C4 in this case.
- Press Ctrl + 1 to open the Format Cells
- Switch to the Alignment tab and select the Center Across Pick option from the Horizontal drop-downwardly list, and then click OK.
In terms of expect, the effect is indistinguishable from the merged cell:
To testify that we did not really merge two cells, nosotros tin select each i individually:
This is how you lot tin combine 2 cells in Excel or merge multiple cells without losing data. Hopefully, this data has proved useful for your day-to-mean solar day tasks. I thank y'all for reading and hope to see on our blog adjacent calendar week.
You may also be interested in
Source: https://www.ablebits.com/office-addins-blog/2015/07/22/merge-combine-cells-excel/
Posted by: parkreakes2000.blogspot.com
0 Response to "How To Merge Cells Vertically In Excel"
Post a Comment