How to Copy CONCATENATE Formula in Excel (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

While working in Excel, we often need to copy CONCATENATE formula to join multiple text strings. If you are finding difficulties to copy CONCATENATE formula then this article is the perfect guide for you. In this article, we will learn 5 convenient ways to copy CONCATENATE formula in Excel. So, let’s start this article and explore these methods.


5 Simple Methods to Copy CONCATENATE Formula in Excel

In this section of the article, we will learn 5 simple methods to copy the CONCATENATE formula in Excel. Let’s say, we have 2 sets of words in 2 different columns. Our goal is to use the CONCATENATE formula to join these and copy down the formula in the following cells.

how to copy concatenate formula in excel


1. Using Copy and Paste Command

Using the Copy and Paste command is one of the easiest ways to copy the CONCATENATE formula in Excel. Let’s follow the steps mentioned below to do this.

Step 01: Apply CONCATENATE Function 

  • Firstly, use the following formula in cell D5.
=CONCATENATE(B5&" " &C5)

Here, cell B5 represents the 1st Word and cell C5 indicates the 2nd Word. Now the CONCATENATE function will join the 2 sets of words.

  • Following that, press ENTER.

Using Copy and Paste Command to copy concatenate formula in excel

Consequently, the 2 sets of words will be joined together as shown in the following image.

Step 02: Copy CONCATENATE Function 

  • Firstly, select cell D5 and use the keyboard shortcut CTRL + C to copy the cell.

Copy CONCATENATE Function to copy concatenate formula in excel

  • After that, select the range of the cells D6:D11 and use the keyboard shortcut CTRL + V to paste the cell along with the formula.

As a result, the CONCATENATE formula will be copied down in the range of the cells D6:D11, as demonstrated in the following picture.

Final output of method 1 to copy concatenate formula in excel

Read More: How to Combine Two Formulas in Excel


2. Utilizing Fill Handle

Utilizing the Fill Handle is another efficient way to copy the CONCATENATE formula in Excel. Let’s follow the instructions outlined below to do this.

Steps:

Utilizing Fill Handle to copy concatenate formula in excel

  • After that, drag the Fill Handle down up to cell D11 as shown in the image below.

Consequently, the CONCATENATE formula will be copied down in the cells of the Combined Result column.

Final output of method 2 to copy concatenate formula in excel

Note: You can also double-click the Fill Handle icon to get the same output.

Read More: How to Concatenate Names in Excel


3. Applying Table Format

Applying the Table format is one of the smartest ways to copy the CONCATENATE formula in Excel. Now, let’s use the procedure discussed in the following section.

Steps:

  • Firstly, select the dataset and use the keyboard shortcut CTRL + T to convert the dataset into a Table.

Applying Table Format to copy concatenate formula in excel

  • Then, in the Create Table dialogue box, check the field of My table has headers.
  • Now, click OK.

As a result, the dataset will be converted to a table as shown in the following image.

  • At this point, apply the following formula in cell D5.
=CONCATENATE(B5&" " &C5)
  • Next, hit ENTER.

Consequently, the CONCATENATE formula will be copied in all of the cells of the Combined Result column as demonstrated in the following image.

Final output of method 3 to copy concatenate formula in excel

Read More: How to Concatenate Date and Time in Excel


4. Pasting as Formula

We can also use the Paste option to copy the CONCATENATE function as a formula in Excel. You can apply the steps mentioned below to do this.

Steps:

Pasting as Formula to copy concatenate formula in excel

  • Following that, select cell D5 and use the keyboard shortcut CTRL + C to copy the cell.

  • Now, select the range of cells D6:D11 and go to the Home tab from Ribbon.
  • After that, select the Paste option from the Clipboard group.
  • Then, choose the Formulas (F) option from the drop-down.

Subsequently, the CONCATENATE function will be copied into the remaining cells.

Final output of method 4 to copy concatenate formula in excel

Read More: Excel VBA: Combine Date and Time


5. Using Shortcut key

In Excel, we can copy the CONCATENATE formula by using a simple keyboard shortcut. Let’s use the instructions outlined below to do this.

Steps:

Using Shortcut key to copy concatenate formula in excel

  • After that, select all the cells of the Combined Result column.
  • Then, use the keyboard shortcut CTRL + D to copy down the formula.

That’s it! The CONCATENATE function will be copied in the cells of the Combined Result column as demonstrated in the following picture.

Final output of method 5 to copy concatenate formula in excel

Read More: How to Concatenate Date/Day, Month, and Year in Excel


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

Practice section to copy concatenate formula in excel


Download Practice Workbook


Conclusion

So, these are the most common & effective methods you can use anytime while working with your Excel datasheet to copy CONCATENATE formula in Excel. If you have any questions, suggestions, or feedback related to this article you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website ExcelDemy.


Related Articles


<< Go Back to Excel Concatenate Text | Concatenate Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Zahid Hasan
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo