Markaad ku shaqaynayso xogta Excel , waa wax caadi ah in la isku daro unugyo, tiirar, iyo saf si loo mideeyo tirooyinka ama qoraalka.

Waxaa jira sababo kala duwan oo laga yaabo inaad u baahato inaad xogta ku dhex darsato Excel^(Excel) . Mararka qaarkood waa in la hagaajiyo qaabaynta. Marar kale waa in macluumaadka unugyo badan laga keeno unug keliya.

Sabab kasta ha noqotee, waxaad isticmaali kartaa hababka soo socda si aad isugu geyso macluumaadka Excel si kasta oo aad u baahan tahay.

Sida loo Mideeyo Unugyada Excel

Mid ka mid ah sababaha ugu badan ee lagu midoobi karo unugyo badan waa marka aad samaynayso saf cinwaan gudaha xaashidaada Excel .

1. Waxaad ka dhex abuuri kartaa qoraalka cinwaanka gudaha mid ka mid ah unugyada aad rabto inaad ku biirto. U qaabee^(Format) qoraalka si uu u muuqdo si kasta oo aad rabto, oo ay ku jiraan cabbirka fartu, qaabka, iyo toosinta ama dhererka^{(vertical alignment or height)} .

1. Marka xigta, waxaad u baahan doontaa inaad hubiso in qoraalku si fiican ugu toosan yahay ballaca miiska xogtaada. Si tan loo sameeyo, dooro dhammaan unugyada aad rabto inaad ku biirto oo aad dhexda u dhigto.

1. Dhammaan unugyada aad rabto inaad ku biirto oo la doortay, menu -ka guriga ka dooro ^(Home)Isku-dhafka & Xarunta^{(Merge & Center)} . Tani waxay isku geyn doontaa dhammaan unugyada la xushay hal unug, waxayna udub dhexaad u noqon doontaa qoraalka ama xogta unuggaas. 

Waxaad sidoo kale u samayn kartaa unugyada si toos ah sidoo kale. Tusaale ahaan, haddii aad rabto in magaca bishu uu si toos ah ugu qotomo dhanka midig ee dhammaan safafka bishaas, waxaad taas ku samayn kartaa adigoo isticmaalaya Unugyada Isku^{(Merge Cells)} -dhafka .

Si tan loo sameeyo:

1. Ku qor magaca bisha unugga kowaad.

1. Marka xigta, dooro dhammaan unugyada khuseeya Markaa kiiskan, Janaayo^(January) waxaa ku jiri doona unugyada koowaad iyo labaad.

1. Hadda, iyadoo unugyadaas la doortay, menu-ka guriga^(Home) dooro fallaadha hoos u dhaca ee ku xiga Isku-dhafka & Xarunta^{(Merge & Center)} . Ka dooro Isku-dhafka Unugyada^{(Merge Cells)} liiska hoos u dhaca.

Tani waxay isku dari doontaa unugyada Excel mid ka mid ah waxayna dhigaysaa qoraalka xagga hoose ee unugga si caadi ah. Sababta aadan u rabin inaad u isticmaasho Isku^(Merge) -dhafka & Xarunta^(Center) kiiskan waa sababtoo ah taasi waxay udub dhexaad u noqon doontaa qoraalka si toosan ee tiirka. 

Xaaladdan oo kale, waxay u badan tahay inaad door bidayso inaad qoraalka si toos ah u dhex dhigto si uu ugu yaallo dhammaan unugyada ay khusayso. Si tan loo sameeyo kaliya dooro unugga cusub ee la midoobay, oo dooro summada toosinta toosan ee ku taal menu-ka guriga ee kooxda ^(Home)ribbon^(Alignment) ee toosinta.

Tani waxay si toos ah ula jaanqaadaysaa qoraalka dhammaan unugyada khuseeya si wax walba ay si fiican ugu safraan.

Sida loo Mideeyo Columns gudaha Excel

Excel , kuma koobna isku darka unugyo gaar ah. Waxa kale oo aad isku dari kartaa dhammaan tiirarka xogta

Tusaale ahaan, xaashidan tusaalaha ah, waxaa laga yaabaa inaad rabto inaad ku biirto Magaca Koowaad^{(First Name)} iyo Magaca Dambe ee lammaanaha iibka oo aad geliso tiir kale magacooda oo buuxa.

Xaashidan faafinta ah, haddii aad dooratid dhammaan xogta ku jirta labada tiir oo aad isku daydo in aad isku darsatid unugyada adiga oo isticmaalaya Merge & Center , waxa aad arki doontaa farriin khalad ah oo aad ku waayi doonto xogta dhammaan unugyada marka laga reebo unugga koowaad ee gudaha. baaxadda. 

Tani gabi ahaanba faa'iido ma leh.

Waxaa jira dhowr siyaabood oo degdeg ah oo aad isku dari karto dhammaan xogta laga bilaabo laba tiir adoon lumin waxba. 

Isku dar tiirarka adoo isticmaalaya Notepad

Mid ka mid ah dariiqo fudud oo lagu isku daro xogta labada tiir ee mid ka mid ah ayaa ah in la koobiyeeyo dhammaan xogta labada tiir oo la geliyo notepad. Raadinta Notepad-ka iyo qaabka beddelka ayaa ah hab wax ku ool ah oo si degdeg ah loogu qaabeeyo labada qaybood ee xogta mid.

1. Ka koobbi^(Copy) dhammaan unugyada labada tiir ee aad rabto in aad isku darto oo ku dheji Notepad^(Notepad) . Tag liiska hoose oo taabo Tab on your keyboard. Muuji meesha bannaan ee tabka oo koobiyi addoo isticmaalaya Ctrl ^(Highlight)-C. ^(Ctrl-C.)Waxaad u baahan doontaa tan hadhow. Kadibna calaamadee dhammaan qoraalka.

1. Dooro liiska Edit , oo dooro Bedel^(Replace) .

1. Gudaha Find what field, riix Ctrl-V si aad u dhajiso jilaha Tab . Ku beddelka^{(Replace with)} garoonka, taabo barta bannaan. Tani waxay ku beddeli doontaa dhammaan tabsiyada dukumeentiga hal meel.

1. Hadda dooro Bedel Dhammaan^{(Replace All)} , dhammaan qoraalka ku jira dukumeentiga Notepad -ka waa inuu u muuqdaa mid caadi ah, iyadoo magaca koowaad iyo magaca dambe ay kala soocan yihiin hal boos.

1. Calaamadee^(Highlight) dhammaan qoraalka ku jira dukumeentiga oo taabo Ctrl-C si aad u nuqul ka sameyso. Ku laabo xaashidaada oo ku dheji adigoo isticmaalaya Ctrl-V xagga sare ee tiirka kowaad ee aad asal ahaan ka soo guurisay.

1. Ugu dambeyntii, kaliya dib u magacaw tiirka koowaad oo tirtir kan labaad. Hadda xaashida faafintaadu waxay leedahay labada tiir oo la isku daray mid.

Kani maaha habka ugu casrisan ee lagu midoobi karo tiirarka Excel , laakiin way shaqeysaa wayna fududahay.

^{(Merge Columns)}Ku biir tiirarka gudaha Excel Adigoo isticmaalaya ^{(Excel Using Concatenate)}Concatenate

Haddii aad rabto inaad hesho wax yar oo casri ah oo aad badbaadiso dhowr tillaabo habka, waxaad isticmaali kartaa shaqada CONCATENATE ee Excel . Shaqadani waxay u shaqeysaa sida shaqada Concatenate ee Google Sheets^{(Concatenate function in Google Sheets)} .

Shaqadani waxay isku daraysaa qoraalka unugyo badan oo mid ah. Waxaad xitaa u isticmaali kartaa inaad ku biirto tiirar badan oo aad rabto, ma aha laba kaliya.

1. Si taas loo sameeyo, midig guji tiirka dhanka midig ee labada aad rabto inaad ku biirto oo dooro Geli^(Insert) . Tani waxay gelin doontaa tiir cusub oo maran. 

1. Unugga ugu horreeya ee bannaan ee sare ee tiirkan bannaan, ku qor =CONCATENATE(C3,” “,D3) . Ku beddel C3^{(Replace C3)} iyo D3 wax kasta oo unugyo ka kooban xogtaada. Dhexda "" waxay ku daraysaa meel bannaan oo u dhaxaysa qoraalka labada unug. Markaad riixdo geli, waxaad arki doontaa xogta labada unug oo la isku daray meel bannaan oo u dhaxaysa.

1. Ugu dambayntii, si aad shaqadan hoos ugu dhigto tiirka oo dhan oo aad isugu gayso xogta labada tiir, ku qabo Shift kiiboodhkaga oo ka dul rog mouse-kaaga dhanka geeska midig ee hoose ee unugga koowaad ilaa uu summada mouse-ku isu beddelo laba xariiq oo toosan. Kadib laba-bidix guji jiirka.

Kani waa sifada buuxinta tooska ah ee Excel . Waxay buuxin doontaa qaacidada xagga hoose ee tiirkaas ilaa inta xogtu ka jirto tiirka bidixda. Hadda waxaad arki kartaa tiirka oo dhan ayaa xog ka helay labada tiir ee hore oo la isku daray.

Si kastaba ha ahaatee, si aad u tirtirto labada tiir ee hore, waxaad u baahan doontaa inaad nuqul ka sameysato dhammaan tiirarka cusub oo aad dib ugu dhejiso qiyam ahaan kaliya.

Markaad tan sameyso, waxaad tirtiri kartaa labadii tiir ee hore oo xaashida faafintaadu hadda dhammaatay, iyadoo labada tiir ay midoobeen.

Sida loo Mideeyo Safafka gudaha Excel

Ma jiro khiyaamo deg-deg ah oo fudud ama sifo lagu midoobo safafka gudaha Excel . Waxaad u baahan doontaa inaad isticmaasho isla shaqada CONCATENATE sidii aad u samaysay isku darka tiirarka.

Si kastaba ha ahaatee farsamadu way ka yara duwan tahay. Maaha wax caadi ah in dadku isku daraan xogta safafka kala duwan oo ay galaan saf cusub, laakiin baahida ayaa iman karta marmar.

Isku dar safafka adigoo isticmaalaya Concatenate

Tusaale ahaan, xaashida tusaalaha ah ee aanu isticmaalnay, ka waran haddii aad rabto in aad isku gayso dhammaan magacyada xubnaha kooxda hal bil oo aad gasho saf cusub oo bog kale ah? Si tan loo sameeyo, waxaad u baahan doontaa inaad isku xidho magacyada oo aad kala saartid adigoo isticmaalaya jile sida comma.

1. Dhig cursor meesha aad rabto inaad xogta ka dhigto safaf badan. Tusaale ahaan waxaad ku dhejin kartaa cursor xaashi cusub. Kadibna ku qor =CONCATENATE(Sheet1!C3,”,”,Sheet1!C4) . Beddel C3^{(Replace C3)} iyo C4 adiga oo dooranaya unugyada dhabta ah ee xaashida kale ee aad rabtay inaad isku xidho.

1. Markaad riixdo Gelida^(Enter) , waxaad arki doontaa xogta isku xidhan oo ka soo baxaysa safka cusub, oo ay u kala soocan yihiin hakad. Si loo midoobo safaf badan, kaliya nuqul ka bixi oo ku dheji unugga kowaad safka labaad ee cusub, oo tafatir qaacidada si aad ugu darto safafka xaashida asalka ah ee aad rabto inaad ku biirto.

Waad sii wadi kartaa nidaamkan ilaa aad ka midayso dhammaan safafka xaashida asalka ah ee aad rabto inaad midayso. Xusuusnow^(Remember) , waxaad barafaysan kartaa safka sare ee Excel^{(freeze the top row in Excel)} haddii aad u baahato si aad weli u arki karto madaxa marka aad xogta ku shaqaynayso.

Ku biir safafka ^(Rows)Excel Adigoo isticmaalaya Isku^{(Excel Using Merge)} -dhafka & Xarunta^(Center)

Dabcan Isku^(Merge) -dhafka & Xaruntu^(Center) waxay u shaqeysaa safaf badan oo haya xog isku mid ah. Tusaale ahaan xaashidan, labadaba Feebarwari^(February) waxay ka yimaadeen gobolka "Bartamaha". Halkii aad ku celin lahayd tan laba jeer, waxaad isku dari kartaa labadan saf oo mid ah.

1. Calaamadee^(Highlight) labada saf ee aad rabto in aad midoobaan.

1. Liiska guriga^(Home) , ka dooro Midaynta^(Merge) & Xarunta^(Center) ribbon.

Sida aad arki karto, tani waxay isku daraysaa labada saf ee ka kooban xog isku mid ah hal saf oo ka kooban mid ka mid ah nuqulladaas.

Tani waa hab caqli badan oo lagu nadiifiyo xaashiyahaaga^{(clean up your spreadsheets)} oo lagu yareeyo nuqullada inta lagu jiro xogta.

Kuwani waa qaar ka mid ah tabaha ugu dhaqsaha badan ee lagu isku dari karo unugyada, tiirarka, iyo safafka gudaha Excel . Ma taqaan cid kale? La wadaag qaybta faallooyinka ee hoose!

How To Merge Cells, Columns & Rows In Excel

When working with data in Excel, it’s common to merge cells, columns, and rows tо combine numbers or tеxt.

There are different reasons you may need to merge information in Excel. Sometimes it’s to improve formatting. Other times it’s to bring information from multiple cells into a single cell.

Whatever the reason, you can use the following methods to combine information in Excel in any way you need to.

How to Merge Cells in Excel

One of the most common reasons to merge multiple cells is when you’re creating a title row in your Excel spreadsheet.

1. You can create the title text inside any of the cells that you’d like to merge. Format the text to appear however you’d like, including font size, style, and vertical alignment or height.

1. Next, you’ll want to make sure the text is aligned perfectly over the width of your data table. To do this, select all of the cells that you want to merge and center.

1. With all cells you want to merge selected, on the Home menu select Merge & Center. This will combine all selected cells into one single cell, and center the text or data in that cell. 

You can do the same for cells vertically as well. For example, if you want to have the name of the month centered vertically on the right side of all rows for that month, you can do this using Merge Cells.

To do this:

1. Type the name of the month in the first cell.

1. Next, select all of the cells that are relevant. So in this case, January would include the first and second cells.

1. Now, with those cells selected, in the Home menu select the dropdown arrow next to Merge & Center. Select Merge Cells from the dropdown menu.

This will merge those cells in Excel into one and place the text at the bottom of the cell by default. The reason you don’t want to use Merge & Center in this case is because that will center the text horizontally in the column. 

In this case, you’d probably prefer to center the text vertically so it’s in the center of all of the cells it applies to. To do this just select the newly merged cell, and select the vertical center alignment icon in the Home menu in the Alignment ribbon group.

This aligns the text vertically with all of the relevant cells so that everything lines up perfectly.

How to Merge Columns in Excel

In Excel, you aren’t limited to merging individual cells. You can also merge entire columns of data.

For example, in this example spreadsheet, you may want to combine the First Name and the Last Name of the sales associate into another column for their full name.

In this spreadsheet, if you select all of the data in the two columns and try to merge the cells using Merge & Center, you’ll see an error message that you’ll lose the data in all of the cells except the first cell in the range. 

This isn’t useful at all.

There are a couple of quick ways you can combine all data from two columns without losing anything. 

Merge Columns Using Notepad

One easy way to combine data from the two columns into one is copying all of the data from the two columns into notepad. Notepad’s search and replace feature is an effective way to quickly format the two pieces of data into one.

1. Copy all of the cells from the two columns you want to merge and paste them into Notepad. Go to the bottom of the list and press Tab on your keyboard. Highlight that tab space and copy it using Ctrl-C. You’re going to need this later. Then highlight all of the text.

1. Select the Edit menu, and select Replace.

1. In the Find what field, press Ctrl-V to paste the Tab character. In the Replace with field, press the spacebar. This will replace all tabs in the document with a single space.

1. Now select Replace All, and all of the text in the Notepad document should appear normal, with the first name and last name separated by just a single space.

1. Highlight all text in the document and press Ctrl-C to copy. Go back to your sheet and paste using Ctrl-V into the top of the first column you originally copied from.

1. Finally, just rename the first column and delete the second one. Now your spreadsheet has the two columns merged into one.

This isn’t the most sophisticated way to merge columns in Excel, but it works and it’s easy.

Merge Columns In Excel Using Concatenate

If you do want to get a little more sophisticated and save a few steps in the process, you could use the CONCATENATE function in Excel. This function works just like the Concatenate function in Google Sheets.

This function combines text from multiple cells into one. You could even use it to merge as many columns as you want, not just two.

1. To do this, right click the column to the right of the two you want to merge and select Insert. This will insert a new, blank column. 

1. In the first blank cell at the top of this new blank column, type =CONCATENATE(C3,” “,D3). Replace C3 and D3 with whatever cells contain your data. The “ “ in the middle adds a space between the text from the two cells. When you press enter, you’ll see the data from the two cells put together with a space between them.

1. Finally, to copy this function down the entire column and merge data from both of the columns, hold down Shift on your keyboard and hover your mouse over the lower right corner of the first cell until the mouse icon changes to two horizontal lines. Then double-left click the mouse.

This is an automated fill feature in Excel. It’ll fill the formula to the bottom of that column as far as there’s data in the column to the left. Now you can see the entire column has data from the first two columns merged together.

However, in order to delete the first two columns, you’ll need to copy the entire new column and repaste it as values only.

Once you do this, you can delete the two old columns and your spreadsheet is now finished, with the two columns merged into one.

How to Merge Rows in Excel

There is no quick and easy trick or feature to merge rows in Excel. You’ll need to use the same CONCATENATE function as you did for merging columns.

However the technique is a little different. It isn’t very common for people to combine data from different rows into a new row, but the need may arise occasionally.

Merge Rows Using Concatenate

For example, in the sample spreadsheet we’ve been using, what if you wanted to combine all of the team member names from a single month into a new row on a different page? To do this, you’d need to concatenate the names and separate them using a character like a comma.

1. Place the cursor where you want to place the data from multiple rows. For example you might place the cursor in a new sheet. Then type =CONCATENATE(Sheet1!C3,”,”,Sheet1!C4). Replace C3 and C4 by selecting the actual cells in the other sheet that you wanted to concatenate.

1. When you press Enter, you’ll see the concatenated data appear in the new row, separated by commas. To merge more rows, just copy and paste the first cell into the second new row, and edit the formula to include the rows from the original sheet that you want to merge.

You can continue this process until you’ve merged all of the rows from the original sheet that you want to. Remember, you can freeze the top row in Excel if you need to so that you can still see the header while you’re working on the data.

Merge Rows In Excel Using Merge & Center

Of course the Merge & Center does work for multiple rows that hold the same data. For example in this spreadsheet, both February entries are from the “Central” region. Instead of repeating this twice, you could merge these two rows into one.

1. Highlight both rows that you want to merge.

1. In the Home menu, select Merge & Center from the ribbon.

As you can see, this combines the two rows containing the same data into a single row containing one of those duplicates.

This is a smart way to clean up your spreadsheets and reduce duplicates throughout the data set.

These are a few of the quickest tips to merge cells, columns, and rows in Excel. Do you know any others? Share them in the comments section below!

Related posts

• Safka Kooxda iyo Tiirarka ku jira xaashida shaqada ee Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida Dhakhso Loo Geliyo Safaf Badan gudaha Excel

• Sida Loogu Mideeyo Xogta Faylal Badan oo Excel ah

• Sida Loo Dhaqaajiyo Columns gudaha Excel

• Sida Taariikh ahaan loogu kala saaro Excel

• Hagaha VBA Sare ee MS Excel

• Sida loo xisaabiyo Z-Score gudaha Excel

• Sida loo Quful Unugyada Microsoft Excel

• Sida loo raad raaco Ku-tiirsanaanta gudaha Excel

• Adeegsiga Qalab Raadinta Hadafka ee Excel Maxaa-Haddii Falanqaynta

• Sida Loo Abuuro Isku-dhafka Boostada ee Microsoft Word

• Sida Loo Geliyo CSV ama TSV Xaashida Shaqada ee Excel

• 4 siyaabood oo Excel loogu badalo Google Sheets

• Sida Loo Isticmaalo Shaqada PMT ee Excel

• Ilaali Tixraaca Unugyada marka aad nuqul ka samaynayso foomamka gudaha Excel

• Sida loo isticmaalo Flash Fill ee Excel

• Sida Loo Abuuro VBA Macro Ama Script gudaha Excel

• Sida loo isticmaalo Excel's What-If Analysis

• Sida Loo Raadiyo Excel