what is the function of the merge cells option
8 Ways to Merge Cells in Microsoft Excel
Unified cells are one of the most popular options utilized by beginner spreadsheet users.
Only they have a lot of drawbacks that make them a not so great option.
In this Emily Post, I'll show you everything you need to know virtually merged cells including 8 ways to merge cells.
I'll also say you why you shouldn't use them and a better alternative that bequeath produce the same exteroception result.
What is a Merged Cell
A incorporate cell in Excel combines two or more cells into one astronomical prison cell. You can only merge contiguous cells that form a rectangular shape.
The above example shows a single merged prison cell resulting from merging 6 cells in the range B2:C4.
Why You Might Merge a Cell
Convergent cells is a common technique used when a title Beaver State mark is needed for a mathematical group of cells, rows OR columns.
When you coalesce cells, only the value or rule in the peak liberal cell of the range is preserved and displayed in the resulting united cellphone. Any other values or formulas are unwanted.
The in a higher place example shows cardinal merged cells in B2:C2 and D2:F2 which indicates the class of information in the columns below. For deterrent example the First and Last name columns are organized with a Epithet merged cell.
Why is the Merge Overlook Disabled?
Along occasion, you might find the Merge & Center command in Excel is greyed out and not available to use.
There are two reasons wherefore the Merge & Center command can get ahead unobtainable.
- You are trying to merge cells inside an Excel table.
- You are hard to merge cells in a protected sheet.
Cells indoors an Excel table can not embody merged and there is no root to enable this.
If nearly of the other commands in the ribbon are dulled out too, then IT's likely the sheet is protected. In order to access the Conflate option, you will involve to unprotect the worksheet.
This can be done by going to the Review tab and clicking the Unprotect Sheet command.
If the sheet has been protected with a password, and then you leave ask to enter it in order to unprotect the canvass.
Now you should be able to combine cells inside the sheet.
Incorporated Cells Only Retain the Top side Left Values
Warning before you get merging cells!
If the cells hold in data or formulas, then you testament lose anything not in the upper left cell.
A warning dialogue box will appear telling you Merging cells only keeps the upper-left value and discards other values.
The above example shows the result of blended cells B2:C4 which contains text data. Only the data from cell B2 remains in the resulting merged cells.
If no information is pose in the selected cells, then no warning bequeath appear when disagreeable to merge cells.
8 Ways to Merge a Cell
Confluence cells is an casual task to perform and there are a variety of places this bid can make up plant.
Flux Cells with the Merge & Center Instruction in the Home Tab
The easiest way to merge cells is using the command found in the Home tab.
- Select the cells you want to merge in concert.
- Go to the Home lozenge.
- Click happening the Merge & Sum command found in the Conjunction division.
Merge Cells with the Alt Hotkey Shortcut
There is an easy way to access the Menage pill Merge and Center command using the Alt key.
Press Alt H M C in sequence on your keyboard to use the Merge & Center command.
Merge Cells with the Format Cells Panel
Merging cells is also available from the Format Cells panel. This is the menu which contains all formatting options including merging cells.
You can open the Formatting Cells duologue boxwood a couple of variant ways.
- Go to the Home tab and click on the small launch icon in the let down right recession of the Alignment incision.
- Usance the Ctrl + 1 keyboard crosscut.
- Right click on the selected cells and choose Format Cells.
Crack to the Alignment tab in the Format Cells menu then check the Flux cells alternative and pressur the OK button.
Unite Cells with the Quick Access Toolbar
If you enjoyment the Merge &adenylic acid; Center command a good deal, and then it power make sentience to add it to the Quick Memory access Toolbar so it's ever readily uncommitted to exercise.
Go to the Home tab and satisfactory click on the Merge & Center dominate then take Add to Quick Admittance Toolbar from the options.
This will add the command to your QAT which is always available to use.
A nice bonus that comes with the ready accession command is IT gets its own hotkey shortcut based on its position in the QAT. When you press the Alt key, Surpass leave show you what key to press future in order to access the command.
In the above example, the Merge and Center program line is the third program line in the African te so the hotkey shortcut is Alt 3.
Merge Cells with Copy and Paste
If you cause a merged cell in your sheet already then you bathroom copy and paste information technology to create more merged cells. This will develop merged cells with the accurate same dimensions.
Transcript a merged cell using Ctrl + C then paste it into a new location victimisation Ctrl + V. Just make a point the paste sphere doesn't overlap an existent merged cell.
Merge Cells with VBA
Substitute MergeCells() Range("B2:C4").Merge End Sub The rudimentary command for merging cells in VBA is rather simple. The above computer code volition merge cells B2:C4.
Merge Cells with Function Scripts
function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); selectedSheet.getRange("B2:C4").merge(false); selectedSheet.getRange("B2:C4").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center); } The above office script will merge and center the range B2:C4.
Merge Cells inside a PivotTable
We ass't spend a penny this change for the hand-picked cells because it testament move a PivotTable. Use the field list to variety the describe. If you are trying to insert or delete cells, move the PivotTable and try once again.
If you try to use the Fuse and Center control inside a Pivot Put of, you will be greeted with the above error message.
You toilet't exercise the Unite & Revolve around command inside a PivotTable, but at that place is a way to merge cells in the PivotTable options menu.
Systematic to help of this option, you leave first need to pretend sure your Pivot Table is in the systematic display mode.
- Prime a electric cell inside your PivotTable.
- Go to the Design tab.
- Click happening Report Layout.
- Take Show in Tabular Form.
Your pivot remit will now be in the tabular form where each study you add together to the Rows area will be in its ain column with information in a displayed in a single row.
Now you can enable the option to show merge cells at bottom the pivot table.
- Select a cell inside your PivotTable.
- Go to the PivotTable Take apart tab.
- Penetrate connected Options.
This will open the PivotTable Options menu.
Enable the Mix up and center cells with labels setting in the PivotTable Options menu.
- Click connected the Layout & Initialize tab.
- Impediment the Merge and center cells with labels option.
- Press the OK push.
Now as you spread out and collapse fields in your pivot put of, fields will merge when they have a common label.
10 Ways to Unmerge a Cell
Unmerging a cell is as wel quick and promiscuous. Most methods postulate the same steps as accustomed conflate the cell, but there are few extra methods worth knowing.
Unmerge Cells with the Home Tab Command
This is the same process as merging cells from the Home tab command.
- Select the merged cells to unmerge.
- Go to the Home tab.
- Click on the Merge & Halfway command.
Unmerge Cells with the Format Cells Menu
You butt unmerge cells from the Formatting Cells dialog boxwood as well.
Press Ctrl + 1 to unsealed the Format Cells menu then attend the Alignment pill then uncheck the Merge cells option and press the OK push.
Unmerge Cells with the AL Fervent Key Cutoff
You can apply the same EL baking hot key combination to unmerge a merged jail cell. Select the merged cell you wishing to unmerge then urge Alt H M C in episode to unmerge the cells.
Unmerge Cells with Copy and Spread
You can copy the format from a symmetric solitary cell to unmerge merged cells.
Find a sole cell and utilize Ctrl + C to copy it, then select the incorporated cells and press Ctrl + V to paste the cell.
You can usance the Glue Special options to avoid overwriting any information when pasting. Press Ctrl + Alt + V and so select the Formats option.
Unmerge Cells with the Quick Access Toolbar
This is a good option if you have already added the Merge & Nitty-gritty program line to the quick access toolbar previously.
All you need to do is select the incorporate cells and click on the command or use the EL hot key shortcut.
Unmerge Cells with the Clear Formats Command
- Select the merged cell to unmerge.
- Go to the Home tab.
- Click happening the right part of the Clear button found in the Editing section.
- Select Clear Formats from the options.
Unmerge Cells with VBA
Sub UnMergeCells() Range("B2:C4").UnMerge End Sub Above is the the basic command for unmerging cells in VBA. This leave unmerge cells B2:C4.
Unmerge Cells with Office Scripts
Most methods of merging a cell rear end be performed once again to unmerge cells like a on/off switch to merge or unmerge.
Still the code in authority scripts doesn't work the likes of this and instead, you wish need to remove formatting to unmerge.
function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); selectedSheet.getRange("B2:C4").clear(ExcelScript.ClearApplyTo.formats); } The above code volition clear the data format in cells B2:C4 and unmerge some merged cells it contains.
Unmerge Cells with the Availableness Pane
Merged cells create problems for screen readers which is an accessibility issue. Because of this, they are flagged in the Accessibility window pane.
If you know the location of the merged cells, you can select them and XTC to the Review tab then cluck on the depress part of the Check Accessibility button and choose Unmerge Cells.
If you don't have intercourse where each the merged cells are, you can unsealed up the availability pane and IT wish show you where at that place are and you will live able to unmerge them one at a time.
Get ahead to the Review tab and click happening the top part of the Check Accessibility command to launch the accessibility window pane.
This will show a Merged Cells section which you can inflate to see each merged cells in the workbook. Click on the address and Excel will submit you to that location.
Click on the downward chevron icon to the right of the address and the Unmerge option can be hand-picked.
Unmerge Cells inside a PivotTable
To unmerge cells inside a pivot table, you just need to disable the setting in the swivel table options menu.
Now you can enable the option to prove merge cells inside the pin table.
- Prize a cell inside your PivotTable.
- Go to the PivotTable Analyze tab.
- Click connected Options.
- Click on the Layout & Format tab.
- Uncheck the Meld and center cells with labels option.
- Press the Oklahoma release.
Merge Multiple Ranges in One Step
In Surpass, you can select multiple not continuous ranges in a sheet by holding the Ctrl key. An nice consequence of this is you can convert these nine-fold ranges into integrated cells in one step.
Hold the Ctrl key while selecting multiple ranges with the black eye. When you'Ra finished selecting the ranges you can release the Ctrl key so get going to the Home tab and click on the Merge & Center command.
Each pasture will become its own merged cell.
Combine Information Earlier Merging Cells
Ahead you immix any cells which contain data, it's advised to combine the data since only the data in the top left prison cell is preserved when blending cells.
There are a motle of ways to do this, simply using the TEXTJOIN function is among the easiest and all but flexible as it will allow you to separate each cadre's data victimisation a delimiter if needed.
= TEXTJOIN ( ", ", TRUE, B2:C4 ) The above formula will join all the text in range B2:C4 going from left to right and then top to bottom. Text edition in each jail cell is disjointed with a comma and distance.
Once you have the formula result, you can copy and library paste information technology as a value so that the information is maintained when you merge the cells.
How to Get Data from Merged Cells in Surpass
A common misconception is that each cell of in the merged range contains the information entered inside.
In fact, only the top off left jail cell will contain any data.
In the supra example, the formula references the top left value in the merged browse. The result returned is what is seen in the merged range.
In this example, the formula references a mobile phone that is not the top left most cubicle in the incorporated vagabon. The result is 0 because this cell is vacate and contains no value.
In order to extract data from a merged range, you will always need to reference the uppermost left about cubicle of the range. Fortunately, Excel mechanically does this for you when you prize the merged cell in a rul.
How to Find Merged Cells in a Workbook
If you create a bunch of merged cells, you might need to come up them all later. Thankfully it's not a atrocious process.
This can be done victimization the Find & Prize program line.
Go to the Home tab and click happening the Find & Prize push button found in the Redaction surgical incision, then choose the Find choice. This will open the Find and Replace menu
You can also use the Ctrl + F keyboard shortcut.
You don't need to come in some text to find, you can use this menu to find out cells formatted as merged cells.
- Clack on the Format button to open the Find Format menu.
- Go to the Alignment chit.
- Check the Merge cells option.
- Press the OK button in the Find Format menu.
- Printing press the Find All operating theatre Find Next push in the Find and Replace menu.
Unmerge All Merged Cells
Fortunately, unmerging all unified cells in a sheet is very easy.
- Select the entire sheet of paper. You can practise this ii ways.
- Click happening the select all button where the tower headings encounter the row headings.
- Insistency Ctrl + A on your keyboard.
- Go to the Home tab.
- Press the Meld & Center command.
This will unmerge all the merged cells and leave all the other cells unaffected.
Wherefore You Shouldn't Merge Cells
In that respect are a lot of reasons why you shouldn't merge cells.
- You can't sieve information with merged cells.
- You can't filter data with merged cells.
- You can't copy and paste values into merged cells.
- It will create accessibility problems for cover readers by ever-changing the order which text should be read.
This isn't an thoroughgoing list, there are likely many more you will come across should to select to merge cells.
Alternative to Merging a Cadre
Good news!
There is a very much better alternative than blended cells.
This mutually exclusive has none of the negative features that come with merging cells but will stillness allow you to achieve the same visual look of a merged cell.
It will allow you to display text centered across a range of cells without mingling the cells.
Unfortunately, this technique bequeath single do work horizontally with one row of cells. There is no option to get along something connatural with a vertical browse of cells.
Select the level range of cells which you would like to center your text edition across. The first cell should contain the text which is to be centered while the remaining cells should be empty.
This example shows text in cell B2 and the drift B2:C2 is elect as the set out to center across.
Attend the Menage tab and click on the small launch icon in the lower right street corner of the Alignment section. This will open up the Format Cells dialog box seat with the Alignment tab activist.
You can also closet Ctrl + 1 to open the Formatting Cells panel and navigate to the Alliance tab.
Select Meat Across Selection from the Level dropdown options in the Text edition alignment surgical incision.
The school tex will now appear as though it occupies altogether cells but it will only exist in the first cell of the centering excerption and each electric cell is inactive branch out.
Pros
- The text appears centered across the selected range like to a merged cell.
- No negative personal effects from merged cells.
- Bathroom be used inside an Excel table.
Cons
- Can non center across a vertical range of cells.
Conclusions
There are lots of ways to merge cells in Excel. You can besides easily unmerge cells too if you need to.
there are many thing to view when merging cells in Excel.
Every bit you have seen, on that point are lots of pitfalls to merged cells that you should consider before implementing them in your workbooks.
Do you have a favourite merge method acting or warning about the dangers of merged cells that's ex-directory in the post? Allow me know about IT in the comments.
About the Author
Whoremaster is a Microsoft MVP and freelance consultant and flight simulator specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You toilet find other interesting articles from Bathroom on his web log or YouTube channel.
what is the function of the merge cells option
Source: https://www.howtoexcel.org/tutorials/merge-cells/
Posting Komentar untuk "what is the function of the merge cells option"