How to Copy Same Value in Multiple Cells in Excel

Copying and Pasting has to be one of the most frequent tasks that we do in Excel. On many occasions while working with large spreadsheets, we need to copy the same value in multiple cells in Excel. In this article, we will know about the different useful ways to copy the same value in multiple cells.

📁 Download Excel File

Download the Excel file below.

Learn to Copy Same Value in Multiple Cells in Excel with These 5 Approaches

In this article, we will learn to copy the same value in multiple cells in Excel by 5 approaches. To illustrate things easily, we suppose that we have a dataset of different universities categorized by their state and the type of institution. Universities from Texas, South Carolina, and California are categorized and all the universities are public universities. Now, using the following dataset, we have to copy one cell content into multiple other cells. The dataset is shown as follows:

To copy values of any cell across rows or columns, the Fill Handle feature is very much used to drag the cell boundary up to the range of cells in which we want to copy the same value. We can clearly understand this by following these steps:

⬇️⬇️ STEPS ⬇️⬇️

• First, we select cell C6.
• We take our cursor to the bottom right of cell C6. The cursor will change into a thin +.
• The universities in Texas state are from cells C6 to C10. So, we grab the Fill Handle and drag it up to cell C10.

• The text “Texas” will be copied from cell C6 to C10.
• Similarly, For South Carolina, we select cell C11 and drag it using the Fill Handle up to cell C13. For, California, we select the cell C14 and drag it up to C16 using the Fill Handle.

• To select the type of institution, we are going to copy Public from cell D6 to the other cells in the column. We select cell D6 and drag the Fill Handle up to cell D16.

• We will see that cells D7:D16 will fill up with the same value in cell D6.

• The results will look like the table below:

📕 Read More: 6 Ways to Copy a Formula Across Multiple Rows in Excel

Another method of copying a cell value or content to multiple cells is by using the Fill Feature Command in the Editing Ribbon. We can use this feature by following these steps:

⬇️⬇️ STEPS ⬇️⬇️

• We select the cells C6:C10 where we want to paste the same value “Texas”.
• We hover over to the Editing Ribbon and select Fill.
• A drop-down menu will appear and from there, we select the Down option.

• The cells C6:C10 will be filled with the contents of cell C6.

• We follow the same process and fill the cells C11:C13 with the cell value “South Carolina” and the cells C14:C16 with the cell value “California”.
• After that, we move on to the next column, select the cell range D6:D16 and follow the same process again. We select the cells where we want to paste “Public.“ In this case the cell range is D7:D16.
• We go to the Editing Ribbon and select the Fill option. When the Fill option brings another drop down Menu, we select Down.
• After that, all the cells from D7:D16 will show the cell value “Public”.

• The final result will be like the image below:

📕 Read More: 5 Ways to Copy Unique Values to Another Worksheet in Excel

The Built-in commands of Copy and Paste options can also allow us to copy the same value in multiple cells. The steps for this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• First, we select cell C6, right-click, and press copy. We can also use the shortcut Ctrl+C to copy the contents of the cell.

• Then we select multiple cells C7:C10 by selecting C7 first and then pressing shift and selecting cell C10 to select all the cells within this range.
• We right-click and click on the paste option as shown in the figure. We can alternatively select the cell range and press the shortcut Ctrl+V to obtain the same result.

• Likewise, we follow the same process for copying and pasting the cell values South Carolina, California and Public in the cells C12:13, C15:C16, and D7:D16.
• The final result will be similar to the following image:

In this approach, we will see another shortcut approach from the traditional Ctrl+C and Ctrl+V shortcut keys. In this approach we will paste the same value across multiple cells in the following ways:

⬇️⬇️ STEPS ⬇️⬇️

• First, we select the range of cells that we want to fill up by a value. Here we first select cells C7:C10.
• Then we go over to the formula bar and then write “Texas”.

• After that, we press Ctrl+Enter and the selected cells will fill up with the value “Texas”.
• Next, we follow the same process sequentially to write down South Carolina, California, and Public across the range of cells C12:13, C15:C16, and D7:D16.
• Therefore, the results will be like the image below:

We will be writing a VBA code in this approach to copy the same value in multiple cells in Excel. It is very handy with long spreadsheets. The processes we need to follow in this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• First, we select the range of cells to which we want to add a VBA code for copying the same value in multiple cells. Here we choose the first cells D7:D16.
• We go to the Developer tab from the top Toolbar and select the Visual Basic option.

• A new window will appear. In that window, we select the Insert option. A drop-down Menu will appear and from that Menu, we select the Module option.

• On the right tab, we write the following VBA Code and press Run.

Sub Same_Value_in_Multiple_Cells()
Dim celRan As Range
Set celRan = Range("D7:D16")
celRan.Value = "Public"
End Sub

• We will see the cells fill up with the same value in the following image:

📄  Important Notes

🖊️  Sometimes, the AutoFill approach might not seem to work by not showing any Fill Handle if its disabled. So, we can check it by going to File >> Options >> Advanced >> Editing Options and then checking Enable fill handle and cell Drag and Drop.

🖊️  We should take care to save the files with VBA Codes as Excel Macro-Enabled Workbook with the extension .xlsm. Otherwise, our VBA Code will not be saved.

📝 Takeaways from This Article

You have taken the following summed-up inputs from the article:

📌  We have learned to copy the same value to multiple cells easily by using the Fill Handle or using the Fill Feature option from the Editing Ribbon.

📌  Alternatively, we can also use shortcut copy-paste techniques to copy the same value in multiple cells.

📌  Furthermore, we can write a short and simple VBA Code to fill a cell range with a certain value. This is a very helpful approach for large and unstructured data.

Conclusion

To conclude, this article will give you insights into ways in which you can copy the same value in multiple cells in Excel. If you find this article helpful then be sure to check the other useful articles related to Excel in our website ExcelDen. Feel free to leave any comments below if you have any questions.

Related Articles

• 3 Easy Ways to Copy and Paste from Excel to Word Without Cells
• Automatically Update One Excel Worksheet from Another Sheet
• How to Copy and Paste in Excel Without Changing the Format