Filling a Certain Number of Rows in Excel Automatically (6 Methods)

We need to Fill a certain number of rows now and then in Excel. The task can be tiresome if we want to do it manually. Luckily Microsoft Excel provides certain ways to perform that. In this article, we are going to show you 6 methods of Filling a certain number of rows in Excel automatically.

In order to explain the methods, we are going to use a sample dataset. We have taken this dataset which contains the year-wise sales value of a company. It has 3 Columns: Serial, Year, and Sales.

Filling a Certain Number of Rows in Excel Automatically


Download Practice Workbook


6 Ways of Filling a Certain Number of Rows in Excel Automatically

1. Making Pattern and Using Fill Handle for Filling a Certain Number of Excel Rows

In this method, we are going to make a pattern of numbers. Fill Handle will allow us to Fill the excel rows automatically.

Filling a Certain Number of Rows in Excel Automatically

Steps:

  • Put 1 in Cell B5, and 2 in B6. It will create a pattern (increasing by 1).

Filling a Certain Number of Rows in Excel Automatically Using Pattern

  • Select those two rows.
  • Put the mouse pointer at the edge, until there is a “plus(+)” sign. The sign is called Fill Handle.
  • By using the Fill Handle you can AutoFill until cell B12.

  • The rows will be Auto-Filled.

Filling a Certain Number of Rows in Excel Automatically Using Pattern

Related Content: Flash Fill Not Recognizing Pattern in Excel (4 Causes with Fixes)


2. Applying Fill Series Command and Filling Certain Rows

We can AutoFill rows using the Excel Fill Series feature. Fill Series command has many options. Only 2 options will be discussed. We will be using the same dataset from method 1.


2.1. Applying Fill Series on Numbers

We will Fill the Serial column using the Excel Fill Series command. To do so follow these

Steps:

  • Firstly, put a value in cell B5. We have put 1.

  • Secondly, Select the number of rows to be Auto-Filled.

Applying Fill Series Command and Filling Certain Rows

  • Then, Go to the Fill from Home.
  • Then, select Series…

  • Select Columns from Series in.
  • Type should be set “Linear” by default. If not, change it.
  • Step Value: 1 (This will increase the values by 1).
  • Finally, press OK.

Filling a Certain Number of Rows in Excel Automatically by Applying Fill Series

We can see, the Serial column has been Auto-Filled.


2.2. Applying Fill Series for Weekdays

We have a new dataset here. We want to AutoFill the Weekday column with the help of the Excel Fill Series feature.

Applying Fill Series Command and Filling Certain Rows for Weekdays

Follow the step-by-step guide to AutoFill rows with weekdays.

Steps:

  • Put the first value on cell C5.

  • Select the cell range C5:C12.
  • Open the Home tab >> from Fill >> select Series…

Select these options from the Series dialog box:

  • Series in Columns.
  • Type Date.
  • Day unit Weekday.
  • Step value 1.
  • Finally, press OK.

Applying Fill Series Command and Filling Certain Rows for Weekdays

Notice, We don’t have the date 12, and 13 March of 2022 as these dates are weekends. This only shows the weekdays. The AutoFill method worked as intended.

Read More: Applications of Excel Fill Series (12 Easy Examples)


3. Utilizing Row Function to Fill Exact Number of Rows

The ROW function can be used to AutoFill a certain number of rows in Excel. The ROW function returns the row number of a cell. We have the same dataset from method 1.

Steps:

  • Select the range B5:B12.
  • Find & Select from Home.
  • Then Go To Special…

Utilizing Row Function to Fill Exact Number of Rows

  • Select Visible cells only.
  • Press OK.

  • Type the following formula in the Formula bar (it should be written on cell B5 by default, no need to click on anything):
=ROW(B5)-4

Here, ROW(B5) will return the value 5. We want our value to start from 1, hence we will deduct 4 from it. 

Utilizing Row Function to Fill Exact Number of Rows

  • Press CTRL + ENTER. Then the series of numbers will be inserted into the selected cells.

We can click on cell B5 to show the formula.

Utilizing Row Function to Fill Exact Number of Rows

This is the final output of Filling a number of rows using the ROW function.

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


Similar Readings:


4. Creating a Formula to Fill Certain Rows in Excel Automatically

Similar to method 3, we can use a formula to AutoFill a certain number of rows. We will use our original dataset here. We will increase our number by 1 using a formula here.

Steps:

  • Put 1 in cell B5.
  • Type the formula below in cell B6, or into the Formula bar:
=B5+1

Creating a Formula to Fill Certain Rows in Excel

  • Press Enter to see the value 2 in Cell B6.

  • Use the Fill Handle to AutoFill from cell B6 to copy the formula up to cell B12.

Creating a Formula to Fill Certain Rows in Excel

The AutoFill operation is successful.

Read More: How to AutoFill Formula When Inserting Rows in Excel (4 Methods)


5. Using CTRL + ENTER to Fill Certain Number of Rows Automatically with Same Values

Often we need to type the same value in a certain number of rows in Excel. We can do that using the CTRL + ENTER Method. We need to select a range then type the desired value. After that, we need to press CTRL + ENTER to fill the selected rows. We will put the gender of the people in the empty cells by using this technique.

Using CTRL + ENTER to Fill Rows with Same Values


5.1. Common Method of Using CTRL + ENTER

We have a dataset that contains 4 males and 4 females. The males are ordered in the dataset. We can type “Male” four times manually. But we will fill the rows automatically.

Steps:

  • Select rows D5:D8 to select only the male names.

  • Type “Male”.
  • Press CTRL + ENTER.

filling a certain number of rows in excel automatically by using CTRL+ENTER

The text “Male” is repeated 3 more times.

We can do that for the 4 females too. If done correctly, this will be the result.

filling a certain number of rows in excel automatically by using CTRL+ENTER


5.2. Filling in the Blanks of Certain Rows by Applying CTRL + ENTER

In this section, we have a dataset that has blank values. We can notice that the text “Female” should be there. We can fill those cells by selecting the blank cells and then the CTRL + ENTER method.

Steps:

  • Select the full table by selecting the B4:D12 cell.
  • Go to the Find & Select from Home.
  • Then, select Go to Special…

Filling a certain number of Blank rows in excel automatically by applying CTRL + ENTER

  • Select Blanks on the Go To Special dialog box.
  • Press OK.

The blank cells within the cell range will be selected.

filling a certain number of rows in excel automatically by using CTRL+ENTER

  • Type the text “Female”.
  • Press CTRL + ENTER.

CTRL+ENTER for filling a certain number of rows in excel automatically

This will Fill up the blank rows.

Fill in the Blanks applying CTRL + ENTER in Excel Automatically

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


6. Using IF Function for Filling a Certain Number of Rows in Excel Automatically

We can use conditionals to Fill the rows in Excel automatically. The IF function is the most common conditional in Microsoft Excel. In our dataset, we have Employee ID, Name of the employee, and Employee Status. We want to Fill the Employee Status based on a criterion automatically. Suppose, the condition is, the employee with an ID number of more than 2000 is considered as a new employee. To do that follow our guide.

Steps:

  • Type the following formula in cell D5 or in the formula bar:
=IF(B5>2000,"New Employee"," ")

This condition means, if the Employee ID is more than 2000, then the employee is a new employee. And if the Employee ID is less than 2000, then leave the cell blank.

Using IF Function for filling a certain number of rows in excel automatically

  • Press ENTER to see the desired value in cell D5.

  • Using the Fill handle we can copy the formula up to cell D12.

We can see that the AutoFill of a certain number of rows has worked perfectly.

Using IF Function for filling a certain number of rows in excel automatically

Read More: How to Number Rows Automatically in Excel (8 Methods)


Practice Section

We have provided datasets similar to this, for every method in the Excel Sheet. You can practice all the methods mentioned here, and understand them easily.

Practice Sheet for Fill a Certain Number of Rows in Excel Automatically


Conclusion

We have used six methods for filling a certain number of rows in Excel automatically. These are AutoFill using a pattern, Fill feature, ROW function, a generic formula, CTRL + ENTER, and with IF function. If you face any problem while using any of the six methods, you can comment below for assistance. Thanks for reading. Keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

2 Comments
  1. Useful article

Leave a reply

ExcelDemy
Logo