Zahid Hasan
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.
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+D6Here, 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
- 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 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.
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
- 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.
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.
- 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!
