In some situations, Excel will help you merge cells automatically. This can save a lot of time if you need to merge the same types of data repeatedly.
Let’s take a look.
We’ll use the Employees sheet (the second sheet in the workbook) again. Delete any values from the Full Name column before you start.
Click into cell C2, and type “Noella Bridgen.” This is the concatenation result that you’re looking for.
Next, click into cell C3, and start typing “Rosana Ellin.” As soon as you start typing, Excel will fill in the cell with its best guess at what you want, and will show you a preview of what the rest of the entire column would look like if you used that type of concatenation.
In our case, everything looks good, so we’ll just hit enter to accept it.
Now, the entire column has been filled with merged cells!
For example, if you type “Bridgen, Noella” into cell C2, and then start typing “Ellin, Rosana,” Excel will fill in the rest of the cells with the same type of concatenation, giving you everyone’s name in “Last name, first name”-format.
Pretty cool, huh?
It gets even better.
Click over into cell E2, and type “Bridgen, Noella: Product Management.” Then click down into the next cell in the column, and start typing “Ellin, Rosana: Marketing.”
Excel already knows what you’re doing, and will help you autofill the entire column with this format.
The possibilities for this auto-merge type of concatenation are endless.
And once you get the hang of it, it can save you a lot of formula typing!
If you have a huge spreadsheet and you want to merge a lot of different cells, this is a very efficient way to do it.
Once you’ve seen the things you can do with the concatenation operator and Excel’s automatic concatenation feature, it becomes clear that merging cells is not only easy, but very powerful. It just takes a bit of practice to get used to.
Try doing some merging in your own spreadsheets. See if you can figure out how to get Excel’s automatic concatenation to save you some time!