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

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

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

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

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

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

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.

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

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

** **

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

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

**Similar Readings:**

**How to Turn Off AutoFill in Excel (3 Quick Ways)****AutoFill Sequential Letters in Excel (5 Quick Ways)****How to Use Flash Fill in Excel to Split Data (4 Quick Ways)****AutoFill Formula to Last Row with Excel VBA (5 Examples)****How to Autofill Days of Week Based on Date in Excel (5 Easy Ways)**

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

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

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

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

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

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

Useful article

Thank You.