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

Get FREE Advanced Excel Exercises with Solutions!

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

Fill Column in Excel with Same Value


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.

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

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


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.


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.


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.

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

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.

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.

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

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.


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.


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


<< Go Back to Excel Autofill | 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.
Nuraida Kashmin
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo