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.


Practice Workbook

Download the following workbook and exercise.


9 Tricks to Fill Column in Excel with Same Value

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’.

Fill Column in Excel with Same Value

Read More: How to Autofill a Column in Excel (7 Easy Ways)


1. Excel Fill Handle to Fill Column with Same Value

Fill Handle feature fills a column or row with values automatically by dragging a mouse. We can use this feature to fill 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 Black Plus (+) sign like the screenshot below.

Excel Fill Handle to Fill Column with Same Value

  • 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: Fix: Excel Autofill Not Working (7 Issues)


2. Fill Same Data with 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 in 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.

Fill Same Data with Fill Command in Excel

  • 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’.

Read More: How to Use Autofill Formula in Excel (6 Ways)


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.

Keyboard Shortcut for Filling Column with Same Value

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

Read More: How to Apply AutoFill Shortcut in Excel (7 Methods)


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.

Auto Fill Columns with Exact Value in Excel

STEPS:

  • First, select the columns range C5:D9.

Auto Fill Columns with Exact Value in Excel

  • 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.

Read More: How to AutoFill Numbers in Excel (12 Ways)


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 pops up. Select Copy from it.

Use Context Menu to Fill Column with Same Value

  • 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: How to Number Columns in Excel Automatically (5 Easy Ways)


Similar Readings:


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.

Apply Power Query to Fill Down Column in Excel

  • 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.

Apply Power Query to Fill Down Column in Excel

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

Apply Power Query to Fill Down Column in Excel

  • 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.


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.

Excel VBA to Fill Column with Same Value

  • 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 for running the code.

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

Read More: How to Use VBA AutoFill in Excel (11 Examples)


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

Filling down a column manually with the same value is quite difficult. Excel ‘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.

Column Filling with Excel ‘Go To Special’ Option

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

Column Filling with Excel ‘Go To Special’ Option

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

Column Filling with Excel ‘Go To Special’ Option

  • It will indicate the blank cells of the column.

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

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

Related Content: How to Turn Off AutoFill in Excel (3 Quick Ways)


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.

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

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

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

  • 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.

Read More: [Fix] Excel Fill Series Not Working (8 Causes with Solutions)


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.


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

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo