Microsoft Excel has a range of exciting features that help users organize their data in columns and rows. It can take you hours to learn all the functions, but only a few minutes to discover how to merge cells in Excel spreadsheet. It is a unique feature that helps you organize your data in a suitable format and make it look attractive.
There are numerous benefits of merging cells in Excel, including grouping data from across many cells into a single larger one. It also helps format the data to have a centred heading across the spreadsheet. To understand the concept, you need to learn the various ways on how to combine two cells in Excel and identify the best method to use.
How to merge cells in Excel
Three simple methods will help you understand how to merge cells in Excel and make your work easier. Doing this does not mean we are splitting individual box partitions since it is practically impossible. Combining cells in Excel only make them appear a little bit invisible in the merged set, yet it is not true that they split during the process.
Using the Merge & Center feature
The technique involves using the Merge & Center feature in the HOME tab to merge cells in Excel without combining the texts. It keeps data from only one of the small boxes (on the leftmost) while deleting the rest. Let us use the following table showing the month of October, year and the days.
Looking at the table, you may decide to do away with the text '2019' and have only 'October' covering the seven columns.
- First, select the range A1 to G1 in the worksheet.
- Go to the HOME tab and select Merge & Center.
- A small window will pop-up warning you that the process will only keep one value.
- Click OK and observe the changes.
As you can see, the text '2019' is deleted and instead we have 'October' covering the entire columns. You may notice that only the text from the leftmost cell (A1) remains. If you are not satisfied with the new changes, it is possible to revert to the previous format. All you need is highlight the merged data and click Merge & Center again. The merged heading will split back into two and the text 'October' moves to the left, its initial position.
This method may not be effective in some scenarios; therefore, we need to explore alternative and suitable ways on how to combine cells in Excel.
Merging cells using a formula
Unlike the previous process, using the CONCATENATE formula function will combine both the cells and their respective texts without losing any data. Microsoft Excel has several operators that serve different purposes, including combining 2 cells in Excel. In this case, we are going to use the following data on students' performance.
You may decide to format each name into a single partition instead of two. Use column C as your working space while following this procedure.
- Click cell C2 then type the formula =A2&" "&B2 and hit enter.
- You will see that each name combines into one cell.
- Repeat the process for the rest of the names.
You can also interchange the names such that the last name comes before the first by modifying the function to =B2&" "&A2. To add a comma between the names, separate the elements with a comma as =B2&", "&A2.
The automatic technique
Dealing with extensive data may be exhausting, and you may need a quick way to merge two cells in Excel. Using the automated method is simple and will save you time. It should be relieving to uncover how Excel merge cells automatically. How does this work?
- Use data in the previous example and delete the C column.
- Click C2 and type 'Winnie Terry' and Excel will pick the concatenation.
- Proceed to C3 and write the next name 'Johnny Spike.'
- You will notice that the moment you start typing, Excel will show the full name that you want to type.
- It uses the concatenation of the first name in step 1 to guess what you want in the following box.
- Excel will also try to fill the remaining part of the column. Once you confirm they are correct, press enter and the changes will implement immediately.
What happens when you change the format of your concatenation? Does Excel combine cells by picking the new formula or stick to the first one?
- Once again, begin by deleting the data in the C column.
- Click C2 and this time reverse the name and type as 'Terry Winnie.'
- Next, click C3 and type the name 'Spike Johnny' and observe what happens.
- Excel will fill the remaining sections using the new format, just like in the previous occasion.
- This confirms that Excel will always figure out your original idea or what you did previously and use it to apply to the other parts of the column.
How to use a keyboard shortcut to combine cells in Excel
A good number of people like using keyboard shortcuts in their operations, which vary depending on the task they perform. Windows users are familiar with the 'Control C' shortcut for copy and 'Control V' for pasting and many more. How much do you know about the shortcut key for merging cells in Excel? To understand how it works:
- First, highlight the data you want to combine, in this case, A2 and B2.
- Hold the ALT key on the keyboard until letters and numbers appear on the header.
- Press H on the keyboard to select the HOME tab.
- You will notice that the Merger & Center feature is assigned letter M. Go back to your keyboard and Press M.
- Different letters are assigned to the drop-down elements — Press C on your keyboard to select the first option.
- A small window will appear with some warning. As previously discussed, click OK to implement the changes.
Formatting data using the 'Center Across Selection' option
To ensure your data in the merged cells align at the centre, you need to use the Center Across Selection option to format the spreadsheet.
- Highlight the data you want to merge.
- Press Control + 1 on your keyboard.
- A window box named Format Cells will appear on your screen.
- Click the Alignment tab to select Center Across Selection under Horizontal drop-down.
- All data will be aligned at the centre immediately without necessarily dealing with each cell individually.
As you can see, it takes only a few minutes to learn how to merge cells in Excel. You have several simple techniques that can guide you through the process, including using the shortcut key. So, which one do you prefer?
READ ALSO: How does a spell check in Excel work?