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!)
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.
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.
2)Â Click Insert to insert a column to the left of the column you have highlighted.
3)Â Click into the cell in your new column that is on the first row of data you want to combine.
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).
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.
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.
Now you’ll see the results of your formula working in all rows that you copied it to.
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.
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.
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.
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.
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.
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.)
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:
- The BEST Pinterest Strategy Course out there for learning to drive traffic to your blog.
- Affiliate Marketing for Bloggers – an awesome course you should take to learn how to make money through affiliate marketing on your blog.
- 2019 Blog Planner – An awesome blog planner you need in your life if you’re a blogger.
- Blogcabulary Plus: The e-book YOU NEED if you are a new blogger.
- The Blog Fixer: for any issues or problems you can’t seem to fix on your own.
- ConvertKit: for sending marketing messages to previous or potential clients.
- Siteground: Another great option for web hosting with awesome customer service and great speed.
Photo by rawpixel on Unsplash
Hi, I’m Jessica! I am wife to Chris, and mom to Kaiper, Alana and Koa. I am a freelance website & blog developer and aspiring author. I share about everything from parenting, how to make money from home, tutorials for my fellow bloggers, work-at-home mom life and being a homeschool family. Learn more about me here.
