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

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

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.

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

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

- 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 Create a Custom Formula in Excel

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

### 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 Create a Formula in Excel for Multiple Cells

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

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

**Read More: **How to Create a Conditional Formula in Excel

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

## Practice Section

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

**Download Practice Workbook**

## 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. Happy learning!

## Related Articles

- How to Insert Formula for Entire Column in Excel
- How to Create a Complex Formula in Excel
- How to Create a Formula Using Defined Names in Excel
- How to Create a Formula in Excel without Using a Function

**<< Go Back to How to Create Excel Formulas | Excel Formulas | Learn Excel**