How to Apply Formula in Excel for Alternate Rows (5 Easy ways)

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.


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.

how to apply formula in excel 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.

Using Fill Handle Option to apply formula in excel for alternate rows

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.

Final output og method 1 to apply formula in excel for alternate rows

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.

Utilizing Copy and Paste Command to apply formula in excel for alternate rows

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.

Final output of method 2 to apply formula in excel for alternate rows

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

Using MOD and ROW Functions to apply formula in excel for alternate rows

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
  • 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
  • Then, hit ENTER.

Utilizing ISEVEN Function to apply formula in excel for alternate rows

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.

Final output of method 4 to apply formula in excel for alternate rows

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
  • 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
  • After that, press ENTER.

Using ISODD Function to apply formula in excel for alternate rows

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.

How to Color Alternate Row Color Based on Group in Excel

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.

Using conditional formatting to to color alternate row in excel for alternate rows

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.

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.

How to Alternate Row Color in Excel Without Table

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

Practice section to apply formula in excel for alternate rows


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!


Related Articles

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo