How to Fill Column in Excel with Same Value (9 Tricks)

Sometimes we need to fill a column with the same value in Excel. It makes our dataset easy to operate and saves a lot of time. In this article, we are going to learn how to do that with some easy and quick examples with explanations.

How to Fill Column in Excel with Same Value: 9 Tricks

Here, we are going to use a sample dataset to understand how to fill a column with the same value. The dataset contains a list of Customer names and their pay Amount with the Payment method as ‘Cash’ in one cell of the column. We are going to fill the rest of the cells of the column with the same value called ‘Cash’.

1. Excel Fill Handle to Fill Column with Same Value

The fill Handle feature fills a column or row with values automatically by dragging a mouse. We can use this feature to autofill a column with the same value. Here we are using the above dataset to demonstrate the process. Let’s see how to do that.

STEPS:

• First, select Cell D5.
• Here, we will see a green small box at the bottom corner of the cell. If we hover a mouse on it, we can see that it turns into a Black Plus (+) sign like the screenshot below.

• Now left-click the mouse and drag down the plus sign.
• After that, release the mouse clicking.
• Finally, we can see that the column is filled with the same value.

Read More: How to Fill Down to Last Row with Data in Excel

2. Fill the Same Data with the Fill Command in Excel

We can find the Fill command from the Ribbon part of the Excel worksheet. It simply copies & pastes the format of one cell to another. Let’s see how to apply it to the same dataset.

STEPS:

• In the beginning, select the range of the column where we want to apply the Fill command. Here it’s D5:D9.
• Next, go to the Home tab from the Ribbon.

• Now, select the Fill drop-down from the Editing section.
• Then click on the Down option.

• In the end, we can see that the selected column range is filled with the same value ‘Cash’.

3. Keyboard Shortcut for Filling Column with Same Value

There is also a keyboard shortcut to fill a column with the specific value. Suppose, we have the same dataset as above. Let’s go through the procedure.

STEPS:

• Firstly, select the column range D5:D9.

• Secondly, press the ‘Ctrl + D’ keys from the keyboard.
• Boom! We can finally see that the column is filled.

4. AutoFill Columns with Exact Value in Excel

Let’s assume that, we have a dataset of the weekly payment method of the two regular customers Bob & Lily. They both pay in ‘Cash’. Now, we are going to auto-fill both columns with the same value ‘Cash’ at once. Let’s see how to do that.

STEPS:

• First, select the columns range C5:D9.

• Then in Cell C5, write down ‘Cash’ manually.

• After that, press the ‘Ctrl + Enter key’.
• In the end, the columns are filled with the same data as below.

5. Use Context Menu to Fill Column with Same Value

The Context menu is one of the easiest options to fill a column. It’s an Excel built-in command. Let’s see how to use it. Suppose, we use the same dataset as the first trick here.

STEPS:

• Select Cell D5 at first.
• Next, right-click on the mouse.
• Here, we can see a context menu pop up. Select Copy from it.

• Now select the fill range of the column. Here it’s D6:D9.
• Again, right-click on the mouse.
• Then select Paste from the Paste Options of the context menu.

• We can see the column is filled with the same value at last.

Read More: Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows

6. Apply Power Query to Fill Down Column in Excel

Power Query is one of the most important data automation tools of Excel. We can use it to fill a column easily. To do that, we need to follow the below steps. Here we are going to use the same dataset as we discussed above.

STEPS:

• First, select any cell from the dataset. We select Cell D6.
• Now, go to the Data tab from the Ribbon.
• Next, select the ‘From within Sheet’ option from the ‘Get & Transform Data’ section.

• A ‘Create Table’ window pops up. Ensure the table range is in the blank box and also put a tick mark on the ‘My table has headers’ box.
• Then click on OK.

• The Power Query Editor window pops up with the required table.

• After that, right-click on the column header which we want to fill with the same data.
• Select Fill > Down.

• Here, we can see that the required column is filled with the same value.
• Finally, select the ‘Close & Load’ option from this window.

• In the end, we can see that a new worksheet appears in the workbook. It contains the modified values which we change in the Power Query Editor window.

Read More: Filling a Certain Number of Rows in Excel Automatically

7. Excel VBA to Fill Column with Same Value

Excel VBA (Visual Basic for Applications) helps us to develop a user-defined function. It is very easy to learn as well as easy to apply. It saves a lot of time. In this method, we are going to use it to fill the column with the same value in the same dataset as we discussed before.

STEPS:

• In the beginning, select the column range D5:D9.
• Secondly, right-click on the worksheet from the Sheet Bar.
• Thirdly, select the View Code option.

• A VBA Module window pops up. We can also get it by using the keyboard shortcut ‘Alt + F11’.
• Next, type the below code here.

Sub Fill_ColumnWithSameValue()
Dim rng As Range
For Each rng In Selection.Cells
If rng.Value = "" Then
rng.FillDown
End If
Next rng
End Sub

• After that, click on the Run option like the below screenshot. We can also press the F5 key to run the code.

• At last, when we return to the main worksheet, we can see that the column is filled with the same value.

8. Column Filling with Excel ‘Go To Special’ Option

Filling down a column manually with the same value is quite difficult. In Excel, the ‘Go To Special’ option helps us to do it. Let’s apply it to the above dataset and see the result.

STEPS:

• First, select the column range D5:D9.
• Then go to the Home tab.

• Next, click on the Find & Select drop-down from the Editing section.
• Select the ‘Go To Special’ option.

• A ‘Go To Special’ window pops up here.
• Now select the Blanks option and click on OK.

• It will indicate the blank cells of the column.

• After that, go to the Formula Bar.
• Write down the below formula:

• Press ‘Ctrl + Enter key’.
• Finally. We can see that the whole required column is filled with the same data.

9. Excel ‘Find & Replace’ Feature to Fill Column with Same Value

The Find & Replace feature is an Excel built-in option. We are going to apply this to the above dataset to fill the column.

STEPS:

• First, select the column range D5:D9.

• Next, from the Home tab, select the Editing section.
• Now select the Find & Replace drop-down.
• Click on the Replace option.

• A Find and Replace window pops up.
• Then keep the Find what box blank and write Cash on the Replace with box.
• Click on the Replace All option.

• After that, a confirmation message box is shown. Click OK.

• Finally, we can see that the required column is filled with the same value.

Things to Note

Sometimes, we cannot find the Power Query feature in the Ribbon. For that, we need to search it from the File > Options path or download it from Microsoft’s official site.

Practice Workbook

Download the following workbook and exercise.

Conclusion

By using these methods, we can easily fill the column in Excel with the same value. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.

Related Articles

• How to AutoFill Formula When Inserting Rows in Excel
• How to Autofill Dates in Excel 
• How to Auto Populate from Another Worksheet in Excel

<< Go Back to Excel Autofill | Learn Excel