While working in Excel, sometimes we need to** apply formula for alternate rows**. For a small dataset, this task can be done quite easily. But, when we face a large dataset, it becomes an exponentially harder job to do. To save us from this hassle, Excel has a few nifty tricks to do it for us. In this article, we will learn **5 **easy methods to **apply formula for alternate rows in Excel**.

**Table of Contents**hide

## Download Practice Workbook

## 5 Ways to Apply Formula for Alternate Rows in Excel

Let’s say, we have **Sales Data of XYZ Company** as our dataset. In the dataset, we have **Quarterly Sales **for the **Years 2021 **and **2022**. We will find the **Half-Yearly** **Sales **by applying formula for alternate rows.

### 1. Using Fill Handle Option

In the first method, we will use the **Fill Handle** option of Excel to **apply formula for alternate rows**. **Fill Handle **allows us to copy a formula in multiple cells. Let’s follow the steps mentioned below to do this.

__Steps:__

- Firstly, enter the following formula in cell
**E5**.

`=D5+D6`

Here, cell **D5 **represents the *Sales *of **Qtr 1 **for the *Year 2021*, and cell **D6 **refers to the *Sales *of **Qtr 2 **for the *Year 2021*.

- Following that, press
**ENTER**.

As a result, you will have the *Half-Yearly Sales* for the first 2 *Quarters *of the *Year 2021 *as shown in the following picture.

- After that, select cell
**E5**along with its adjacent cell**E6**as marked in the image below.

- Now, drag the
**Fill Handle**up to cell**E12**.

Consequently, you will have the *Half-Yearly Sales* on the alternate rows as demonstrated in the following image.

**Read More: ****How to Apply Formula to Entire Column Without Dragging in Excel**

### 2. Utilizing Copy and Paste Command

Utilizing the **Copy** and **Paste **command of Excel is one of the easiest methods to apply formula for alternate rows. Let’s use the steps given below to do this.

__Steps:__

- Firstly enter the following formula in cell
**E5**.

`=D5+D6`

- Then, hit
**ENTER**.

As a result, you will get the following output on your worksheet.

- Now, select cell
**E5**and its adjacent cell**E6**together. - Subsequently, press the keyboard shortcut
**CTRL + C**to copy the cells.

- After that, select the range of the cells where you want to apply the formula for alternate rows.
- Then, press
**CTRL + V**to paste the copied cells.

Consequently, you will have the *Half-Yearly Sales* in the alternate rows as shown in the following picture.

**Read More: ****How to Use Point and Click Method in Excel (3 Examples)**

### 3. Using MOD and ROW Functions

In this method, we are going to use the **MOD** and the **ROW** functions of Excel to **apply formula for alternate rows**. The **MOD **function gives us the** remainder** of a number after dividing it by another number. The **ROW** function returns the **number of the row of a cell**. Let’s follow the procedure mentioned below to **apply formula for alternate rows in Excel**.

__Steps:__

- Firstly, use the formula given below in cell
**E5**.

`=IF(MOD(ROW(),2)=1,D5+D6,"")`

**Formula Breakdown**

**ROW()**→ It returns the**row number**of a cell.**Output**→**5**

**MOD(ROW(),2)**→ It becomes**MOD(5,2)**- It returns the
**remainder**of**5**after dividing by**2**. **Output → 1**

- It returns the
**IF(MOD(ROW(),2)=1,D5+D6,””)**→ This becomes**IF(1=1,D5+D6,””)**- The
**IF function**checks whether a condition is met, and returns one value if**TRUE**, and another one if**FALSE**. **Output → $5,200**

- The

- After that, press
**ENTER**.

Subsequently, you will have the *Half-Yearly Sales* for the first **2 ***Quarters *of the *Year 2021*.

- Now, by using the
**AutoFill**feature of Excel, we can get the rest of the outputs as shown in the image below.

**Read More: ****How to Insert Formula for Entire Column in Excel (6 Quick Ways)**

### 4. Utilizing ISEVEN Function

Utilizing the **ISEVEN** **function** is another smart way to **apply formula for alternate rows in Excel**. The **ISEVEN **function tells us whether a number is **even **by returning **TRUE **or **FALSE**. Now, let’s follow the steps mentioned below.

__Steps:__

- Firstly, enter the following formula in cell
**E5**.

`=IF(ISEVEN(ROW())=FALSE,D5+D6,"")`

**Formula Breakdown**

**ROW()**→ It returns the**row number**of a cell.**Output → 5**

**ISEVEN(ROW())**→ This becomes**ISEVEN(5)**- It returns whether the number
**5**is**even**or not. **Output → FALSE**

- It returns whether the number
**IF(ISEVEN(ROW())=FALSE,D5+D6,””)**→ It becomes**IF(FALSE=FALSE,D5+D6,””)**- The
**IF function**checks whether a condition is met, and returns one value if**TRUE**, and another one if**FALSE**. **Output → $5,200**

- The

- Then, hit
**ENTER**.

As a result, you will have the following output in cell **E5 **as marked in the following image.

- Following that, use the
**AutoFill**feature of Excel to obtain the remaining outputs.

**Read More: ****Excel VBA: Insert Formula with Relative Reference (All Possible Ways)**

### 5. Using ISODD Function

In this method, we will use the **ISODD function** of Excel to **apply formula for alternate rows**. The **ISODD **function allows us to know whether a number is **odd **by returning **TRUE ** or **FALSE**. Now, let’s use the steps discussed in the following section.

__Steps:__

- Firstly, use the formula given below in cell
**E5**.

`=IF(ISODD(ROW())=TRUE,D5+D6,"")`

**Formula Breakdown**

**ROW()**→ It returns the**row number**of a cell.**Output → 5**

**ISODD(ROW())**→ This becomes**ISODD(5)**- It returns whether the number
**5**is**odd**or not. **Output → TRUE**

- It returns whether the number
**IF(ISODD(ROW())=TRUE,D5+D6,””)**→ It becomes**IF(TRUE=TRUE,D5+D6,””)**- The
**IF function**checks whether a condition is met, and returns one value if**TRUE**, and another one if**FALSE**. **Output → $5,200**

- The

- After that, press
**ENTER**.

As a result, you will get the following output in cell **E5 **as shown in the image given below.

- Now, by using the
**AutoFill**option of Excel, you can get the rest of the outputs.

**Read More: ****How to Apply Same Formula to Multiple Cells in Excel (7 Ways)**

## How to Color Alternate Row Color Based on Group in Excel

In Excel, we often need to **color alternate row based on group** so that our data becomes easier to read and more attractive to present. Let’s say, we have **Sales Data of ABC Tech Store** as our dataset. In the dataset, we have **Product **name, **Purchaser**, and the **Price **of the **Products**. We need to **color the alternate rows** here based on the groups of **Products**.

Let’s use the steps mentioned below to do this.

__Steps:__

- Firstly, create a column beside the
**Price**column and give the title of the column as**0**.

- Following that, enter the following formula in cell
**E5**.

`=IF(B5=B4,E4,E4+1)`

Here, cell **B5 **refers to the cell of the *Product *column, cell **B4 **is the **title **of the *Product *column, and cell **E4 **represents the title of the newly created column.

- After that, press
**ENTER**.

As a result, you will have the following output on your worksheet as marked in the following image.

- Now, use the
**AutoFill**option of Excel, to get the remaining outputs.

- Next, select the entire dataset and go to the
**Home**tab from**Ribbon**. - Subsequently, choose the
**Conditional Formatting**option from the**Styles**group. - Then, select the
**New Rule**option from the drop-down.

Consequently, the **New Formatting Rule** dialogue box will open on your worksheet.

- Now choose
**Use a formula to determine which cells to format**option from the**New Formatting Rule**dialogue box. - Following that, enter the formula in the field named
**Format values where this formula is true**.

`=AND(LEN($B5)>0,MOD($E5,2)=0)`

Here, cell **B5** indicates the cell of the Product column, and cell **E5** refers to the cell of the newly created column.

**Formula Breakdown**

**LEN($B5)**→ It returns the length of cell**B5**.**Output → 10**.

**MOD($E5,2)**→ This returns the remainder of cell**E5**after dividing by**2**.**Output**→ 1

**AND(LEN($B5)>0,MOD($E5,2)=0)**→ It becomes**AND(10>0,1=0)**- The
**AND function**checks whether all the arguments are**TRUE**, and returns**TRUE**if all arguments are**TRUE**. Otherwise, it returns**FALSE**. - It can be written as
**AND(TRUE,FALSE)** **Output**→**FALSE**.

- The

- Then, click on the
**Format**option.

- Next, in the
**Format Cells**dialogue box, go to the**Fill**tab. - After that, choose your preferred color and click on
**OK**.

- As a result, you will be redirected to the
**New Formatting Rule**dialogue box and click on**OK**there.

Consequently, you will have the alternate rows colored based on group like in the following picture.

You can also use **any method of the linked article here** to alternate row color in Excel based on group.

## How to Alternate Row Color Without Table in Excel

While working in Excel, sometimes we need to** alternate row colors**. We can do it easily by using the **Table** format of Excel. We can also do this without using table. In this section of the article, we will learn how we can **alternate row color in Excel without table**.

__Steps:__

- Firstly, select the alternate rows while pressing the
**CTRL**key from your keyboard. Here, we selected rows**6, 8, 10, 12, 14**, and**16**.

- Following that, go to the
**Home**tab from**Ribbon**. - Then, choose the
**Fill Color**option from the**Font**group. - Now, select your preferred color from the drop-down.

Consequently, you will have the alternate rows colored without using the table as demonstrated in the following picture.

You can also use the **methods described here** to alternate row color in Excel without table.

## Practice Section

In the **Excel Workbook**, we have provided a **Practice Section **on the right side of the worksheet. Please practice it by yourself.

## Conclusion

That’s all about today’s session. I strongly believe that this article was able to guide you to **apply formula in Excel for alternate rows**. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website, **ExcelDemy**. Happy learning!