excel combine cells with comma

Hello, my name is Jessica, and I am an Excel nerd. It’s true. I’ve been using Excel on a daily basis for various work that I do for about ehhh, 13 years now. The other day, I was making some certificates and I noticed that the spreadsheet I was provided had the first name and last name in separate columns. That’s super annoying when you’re trying to copy the first and last name onto one line, and you have to do them separately because they are in separate cells. But, there is a simple fix for situations like that, which saves a lot of time: combine the cells. In that moment I made a note to myself to write a post about how to combine cells in Excel. Merging data in Excel is actually quite simple, but if you don’t know the formula it can seem nearly impossible. I am going to teach you a simple trick to consolidate data in Excel quickly and easily.

In this post I will also teach you how to combine cells with a comma in between, or any other form of data you need to put in between the two sets of data. (Hint: if you learn better visually, scroll down, I recorded a video tutorial of these steps as well!)

how to combine cells consolidate data in excel

Why You Might Need to Consolidate Data in Excel

First, let me start by showing you some examples of when you might need to merge data in Excel. One example, as I mentioned above, is when you have a First Name and Last Name in separate columns as pictured below.

merge data in excel

For the project I was working on, it would have been easier to have one cell with first and last name combined, otherwise I would have to copy and paste out first name, then copy and paste out last name, into the same field in Illustrator, where I was making certificates.

Another example of when you might need to combine cells in Excel is an address. In some cases, it may be easier for you to have the address all on one line, or one cell of data in order to paste it out to other places without doing one cell at a time.

There are multiple instances where knowing how to consolidate cells of data comes in handy, but you get the idea now.

How to Combine Cells in Excel

Now I’ll walk you through the steps of how to merge data in Excel.

1) Right-click on the column directly to the right of the cells you want to merge. Or really,  any place you want to place your merged data.

excel consolidate

2) Click Insert to insert a column to the left of the column you have highlighted.

consolidate data in excel

3) Click into the cell in your new column that is on the first row of data you want to combine.

how to combine cells in excel

4) In that cell, type the equals sign to indicate you are starting a formula (=). Then click the first cell of data you want to combine (Jack in this example). After that, type &” ” – that’s the ampersand, apostrophe, a space, then another apostrophe, and then click on the second cell of data you want to combine (Smith in this example). So in this case your formula will look like =A2&” “&B2 (where A2 and B2 change dependent upon which cells you click to merge).

merge two cells in excel

5) Click Enter, and you’ll see your formula work it’s magic by combining the two cells of data. Notice in the space in between “Jack” and “Smith”. You did that by putting that space in between the apostrophes in your formula.

consolidate data excel

6) Now, you’ll want to replicate this for all rows of data, which you can easily do. You see that little square at the bottom right hand corner of the green highlight when you click on a cell? Click on the cell that you just combined (Jack Smith in this example), then click right on that little square and drag down as far as you need to cover all rows of data. This simply copies the formula you created into the rows below it.

combine cells excel

Now you’ll see the results of your formula working in all rows that you copied it to.

merge cells in excel

7) You’ll notice I didn’t put a header on that column yet, because this is not the final step. If you just want to be able to see the data together in one column, you can stop here, title the header of the column and be done. However, if you want to be able to click into and copy out the merged data, you’ll need to do one more thing: past the values into a new column. So once again, insert a column to the right of the column you’re working in by right-clicking, and then clicking Insert.

excel consolidate data

8) Highlight the column with the formulas in it. You can do this by clicking the header of the column (the grey box with C in it in this example). It will highlight the entire column. Then Copy. I use Command+C (on a Mac) or you can right-click and click Copy. Now Highlight your brand new column, the entire thing, and right-click. Click Paste Special.

excel merge data

9) A box will pop up, and from there you will select the Values option. The reason we want to do this is because we want to be able to click into the cell and copy out the data, basically get rid of the formula we just created.

excel tutorial

10) Once you Click Okay, you’ll see that your data is now showing in both columns. If you click into each one though, you’ll notice the difference. The first column shows formulas when you click on it. The second column only shows the data itself.

how to consolidate data excel

11) Now, you can delete the column with the formulas in it (Column C in this example) and put a title on your new combined column. You can even delete the other columns you pulled the data from if you need to.

Video Tutorial: How to Merge Data in Excel

In this video I walk through the steps mentioned above to combine cells in Excel, and also the steps listed below to combine cells with a comma or other punctuation or symbols in between.

How to Combine Cells with a Comma or other symbol in between

In some cases you may want to consolidate data in Excel with a comma or other things in between. For example, if you are combining the Address, City, State, and Zip into one column and you want to put commas. To do this you would use the same formula as we did above, except that you would add the commas in where needed.

For example, as you can see below, the formula always starts with =

Then, click the first cell of data and type &” after it. Between the apostrophes is where you can put whatever you need to. In the first example above it was just a space. In this example I have put a comma then a space for the first two and just a space for the last one. You are basically telling Excel that you want the data in this cell to equal “this cell” (whatever one you click one) & then a comma, then a space, & this cell, & then a comma, then a space, & then this cell, and you get the point. You could do this with as many cells as you need to. Just click enter when you’re done creating your formula.

combine cells in excel

Now, as you can see, you now know how to combine cells in Excel with a comma (or really whatever symbol you want or need there, a dash, period, etc.)

how to merge data in excel

Creating this tutorial was so much fun. Did I mention am I such a dork? If you have any specific questions about how to do things in Excel, I would love to hear them and possibly create new tutorials based on what you want to know! Leave your questions in the comments and I’ll respond as soon as possible.

While you’re here, check out my tutorials section for other articles you might find handy!

Have more questions about how to combine cells in Excel? Comment below.

[AffiliateLinks]

 


Here are a few tools and courses I recommend:

Photo by rawpixel on Unsplash

how to merge data in excel

Total
109
Shares

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*