How to Combine Cells In Excel (With Steps and Tips)
By Indeed Editorial Team
Published 16 May 2022
The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.
Many professionals, such as business analysts and operations managers, frequently use Excel, which helps them calculate and combine data faster so that they can work more efficiently. One common function in Excel is to combine cells. Knowing how to combine cells can help you work more efficiently, format Excel spreadsheets clearly and help you become even more proficient in Excel. In this article, we discuss when it's appropriate to combine cells in Excel, describe four methods to do so and share tips.
How to combine cells in Excel
Learning how to combine cells in Excel can improve the format of a document and its readability. For example, you can use this function to create a table with a centre-aligned title. Below are the steps for merging cells in Excel:
1. Select the target cells
Select the range of cells you want to combine. You can select them by left-clicking and dragging the mouse across the cells. You can also click the first cell in a row or column and hold the "Shift" button while using the arrows to select to the end.
2. Use the built-in function
Select the function "Merge Cell". If you can't find this function, go to the "Home" tab and look for the "Alignment group". If you're using a Windows operating system, you can also press the "ALT" key and use the buttons "H", "M" and "N" to complete the action.
Example: A sales analyst creates a table that shows the sales from the first three months of a year. The analyst wants to give a title to the table.
ABC1Sales During the First Quarter
2**January February March3$300,000$275,000$323,000**The analyst drags and selects the cells "A1" to "C1" and then clicks on "Merge" from the "Home" tab. Excel merges these three cells and centres the title over the three columns.
Other ways to combine cells in Excel
There are several other ways to combine cells in excel, including the following:
Use the ampersand symbol
If you want to combine data in two or more cells into a separate cell, you can use the ampersand symbol. For example, you may want to combine first names and last names that are located in two different cells to get full names. Follow these steps:
Select a blank cell where you want to put the combined data.
Type the "=" sign in the blank cell.
Click the first cell containing the data you want to combine.
Type the "&" symbol followed by the cell that you want it to combine with.
If you want to combine more than two cells, continue selecting the cells you want to combine before pressing "Enter".
Example: Stella is an administrative assistant helping her employer organise a list of names of the people who subscribed to the company's newsletter. The company already has an Excel file with all the last names in column A and first names in column B:
Stella wants to combine the data in cells "A1" and "B1" and display the result in cell "C1". She types the "=" sign into cell "C1" and clicks cell "B1" first. Then, she types the "&" symbol. Since she wants to leave a space between the first and the last name, she also adds a pair of quotation marks before clicking on cell "A1". If there are other rows that require that same action, Stella can click cell "C1" and drag the function down to apply the same equation to each row. The formula in cell "C1" looks like this:
ABC1ChanTai Man= A1& "" &B1 Related: How to Be a Good Marketing Manager (With Top 10 Qualities)
Use the CONCAT function
You can also combine data in different cells by using the CONCAT function. This function can join up to 30 values together and display the combined result as text. Below are a few steps to illustrate how to use the CONCAT function:
Type "=CONCAT(" in the cell where you want the combined data to display.
Select the first cell and use a comma to separate it from the second cell you're combining. If you want to select multiple cells quickly, press the "CTRL" key and drag the cursor over all the cells you want to combine.
Repeat the same step until you select all the cells you want to combine. Close the formula with a parenthesis and press "Enter".
Example: James is a marketing assistant who is tasked with organising a list of addresses so that the logistics department can read them more comfortable. The spreadsheet looks like this:
ABCDEFG1NameFlat and floor numberBlock**Name of Estate DistrictRegionFull Address2Chan Tai ManRoom 2, 13/FASunrise GardenSheung Shui**New Territories
James wants to combine the data from cells "A2" to "F2" and display the full address in cell "G2". To start, James types "=CONCAT(" in cell "G1". After that, he selects all the data from cells "A2" to "F2". The formula in cell "G2" looks like this:
When is it appropriate to combine cells in Excel?
There are many situations that are appropriate to combine cells in Excel. Some examples include:
merging related names, such as first, middle and last names
streamlining a large Excel spreadsheet to make it easier to read
adding new text to a column of cells for other processes
Using functions to combine data in different cells can improve formatting and ensure that you're combining the data in the right way without missing important data. If you make any adjustments to the original data cell, the formula automatically updates the combined data, saving you time and improving your work efficiency.
Tips for combining cells in Excel
Below is a list of tips you can use to help you combine cells in Excel:
Use "Merge & Centre" function
In Excel, you can merge cells and position words at the centre of a combined cell simultaneously. To do this, use the "Merge & Centre" function found in the "Alignment" group of the "Home" tab. This function is especially useful when you're creating multiple tables.
Be detail-minded when combining data
When you're using the "Merge & Centre" function, Excel automatically deletes part of the data. It keeps the upper-left value and discards all data in other cells. For example, if there are values in cells A1, B1 and C1, Excel may keep the value of A1 and delete the values in cells C1 and B1 when you combine these three cells.
Combine text but don't combine cells
Since the "Merge & Centre" function may delete other cell content except the text from the upper-left cell, you might consider using another method that prevents this from happening. Use the "Centre Across Columns" button when you don't want to affect the structure of the document. Here's how it works:
Select the data you want to centre, right-click on them and choose "Format Cells" in the drop-down menu. You can also press "Ctrl+1" on Windows operating systems.
When you see a pop-up window, click on the "Alignment" tab. Go to the "Text Alignment" section and choose "Centre Across Selection" in the drop-down menu.
Uncheck the "Merge Cells" option and check that the text is at the centre across all cells.
Insert a line break
When you're using the ampersand symbol to combine cells, you can also insert line breaks between data to increase readability. Use the formula, "CHAR(10)", to insert a line break.
ABC1Chan Tai Man**Team 1
Daniel is a marketing assistant and wants to combine cells "A1" and "B1", but he also wants to insert a line break between these two cells. To do this, he uses the following formula in cell "C1":
= A2 & CHAR(10) & B2
The result looks like this:
ABC1Chan Tai ManTeam 1Chan Tai Man
Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.
Explore more articles
- Ways to Define Creativity: With Importance, Benefits and Tips
- What Is Skilled Labour? (Definition, Types and Jobs)
- How to Write a Sincere Thank-You (With Tips and Examples)
- The Definition of Diversity (Plus Six Benefits of a Diverse Workplace)
- What Is Statistical Significance? (Plus Example Calculation)
- How to Add Value to Yourself through Career Retraining
- Functional Testing: Definition, Types, Steps and Importance
- How to Write SMART Goals For Your Career (With Examples)
- How to Write a Thank-You Note (With Tips and Examples)
- Social-Emotional Skills: Definition and Examples
- Steps in the Procurement Process (Plus Types of Procurement)
- How to Use the VLOOKUP Excel Function: A Definitive Guide