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

## 6 Ways 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**.

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

**Steps:**

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

- 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**number until**cell B12**.

- The
**rows**will be**Auto-Filled**.

**Read More: How to Use Autofill Formula in Excel**

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

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

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

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

**Similar Readings:**

**AutoFill Cell Based on Another Cell in Excel****How to AutoFill from List in Excel****How to Create a Custom AutoFill List in Excel****How to Fill Down Blanks in Excel****How to Apply AutoFill Shortcut in Excel****How to AutoFill Sequential Letters in Excel****How to Auto Number Cells in Excel****How to Autofill Numbers in Excel Without Dragging****How to AutoFill Ascending Numbers in Excel****How to AutoFill Numbers in Excel with Filter**

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

Follow the step-by-step guide to **AutoFill rows** on 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**.

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

**Read More: How to Auto Number or Renumber after Filter in Excel**

### 3. Utilizing the Row Function to Fill the 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…**

** **

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

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

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

**Similar Readings:**

**How to Auto Generate Number Sequence in Excel****How to Add Sequence Number by Group in Excel****How to Repeat Number Pattern in Excel****Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows****How to Enter Sequential Dates Across Multiple Sheets in Excel****How to Autofill Dates in Excel Without Dragging****How to Autofill Days of Week Based on Date in Excel****How to AutoFill Months in Excel****How to Create Automatic Rolling Months in Excel****How to Increment Month by 1 in Excel**

### 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`

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

The **AutoFill** operation is successful.

**Similar Readings:**

**How to Autofill a Column in Excel****How to Fill Column in Excel with Same Value****How to Fill Down to Last Row with Data in Excel****How to AutoFill Formula When Inserting Rows in Excel****How to Auto Populate from Another Worksheet in Excel****How to Perform Predictive AutoFill in Excel****Applications of Excel Fill Series****[Fix] Excel Fill Series Not Working****Fix: Excel Autofill Not Working**

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

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

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.

**Read More: [Fixed!] AutoFill Formula Is Not Working in Excel Table**

#### 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…**

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

The blank **cells **within the **cell** **range** will be selected.

- Type the text “
**Female**”. - Press
**CTRL + ENTER**.

This will **Fill** up the blank **rows**.

**Read more: How to Autofill Dates in Excel **

### 6. Using the 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 the **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**.

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

**Read more: How to Repeat Formula Pattern in Excel**

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

**Download Practice Workbook**

## 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 **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!

Useful article

Thank You.