Need to learn how to color alternate row based on cell value in Excel? When we work on a large datasheet, we need to alternate the row color to visualize our dataset better. If you are looking for such unique kinds of tricks, you’ve come to the right place. Here, we will take you through 10 easy and convenient methods of alternating row color based on the cell value in Excel.
How to Color Alternate Row Based on Cell Value in Excel: 10 Ways
To demonstrate the approaches, we consider a report on the Daily Sales- Fruits Section of a certain grocery store. This dataset contains the name of Products in Column B, and the name of the Customers and the Quantity of products are in columns C and D, respectively.
Now, we will show the ways to alternate row colors based on the value in Excel by using the above dataset. In this case, we’ll consider the cell values of Column B as the base values. Based on these values, the entire row will get colored.
1. Color Alternate Row Manually Based on Cell Value in Excel
Here, we’ll alternate the row color by selecting those rows and then selecting our desired background color. Let’s go through the steps below.
📌 Steps
- As the alternate rows are not adjacent rows, we have selected them by pressing CTRL.
- After the selection procedure, go to the Home tab.
- Then, select the Fill Color drop-down on the Font group.
- Later, choose Green, Accent 6, Lighter 60% from the available colors.
- In this way, you can give alternate row colors based on the cell value in Excel.
2. Using Format as Table Option in Excel
To fill the alternative rows with any color you can use the Format as Table option easily. The steps of this process are given below:
📌 Steps
- First of all, select the dataset. In this case, we selected cells in the B4:D14 range.
- Secondly, move to the Home tab.
- Then, select the Format as Table drop-down in the Styles group.
- Later, choose Light Green, Table Style Light 7 from the options.
- Instantly, the Create Table dialog box will appear.
- At this point, ensure to check the My table has headers option.
- Then, click OK.
- Now, select the whole table. In this case, we selected cells in the B4:D14 range.
- After that, go to the Table Design tab.
- Next, select Convert to Range in the Tools group.
- At this moment, click on Yes in the warning box.
- Finally, we will get back our previous data range but with alternate row colors based on cell values.
3. Creating Table to Color Alternate Row Based on Cell Value in Excel
We will use the Table option to alternate the row colors based on the cell values of the following dataset. Follow the steps below.
📌 Steps
- At first, select cells in the B4:D14 range.
- Then, go to the Insert tab.
- After that, select the Table option on the Tables group.
- Suddenly, the Create Table dialog will pop up.
- Then, click OK.
- Thus, we can convert our data range into the following table with alternate row colors.
- Again, to convert the table into the range you can follow the steps of Method 2.
Read More: How to Alternate Row Colors in Excel Without Table
4. Using Format Painter Option
In this section, we will use the Format Painter option to alternate the row colors based on the value of the following dataset. Let’s go through the procedure below.
📌 Steps
- After selecting the second row of the range, go to the Home tab.
- Then, select the Fill Color drop-down on the Font group.
- Later, choose Green, Accent 6, Lighter 60% from the available colors.
So we have now created the format which contains one row with no color and another row with a color. Now, using the Format Painter option we will copy this format to the remaining rows.
- At the very beginning, select the first two rows i.e rows 5 and 6.
- Secondly, select the Format Painter option on the Clipboard group.
- Then the Format Painter sign will appear, drag it down and to the right side.
As a result, we can see that our data range has banded rows now.
5. Applying IF and MOD Functions
In this approach, we will use the IF and MOD functions to get the binary grouping of our dataset. The steps of this approach are given below:
📌 Steps
- Initially, select cell E4 and write down Zero (0) into the cell.
0
- Then, press ENTER.
- Then, select cell E5 and write down the following formula into the cell.
=MOD(IF(B5=B4,E4,E4+1),2)
IF(B5=B4, E4, E4+1): The IF function checks the value of cell B5 with B4. If both values match each other, the function returns the value of cell E4. Otherwise, it will add 1 with the value of cell E4 and return that.
MOD(IF(B5=B4, E4, E4+1),2): The MOD function will divide the result of the IF function by 2 and show the value of the remainder.
- Simply, press the ENTER key.
- After that, double-click on the Fill Handle icon to copy the formula up to cell E14.
- As a result, the remaining cells of Column E get their results.
- Now, select the range of cells B5:E14.
- After that, go to the Home tab
- Then, click on the drop-down arrow of the Conditional Formatting on the Styles group.
- Later, select the New Rule option from the drop-down list.
- As a result, a small dialog box called New Formatting Rule will appear.
- Then, select Use a formula to determine which cells to format under the section of Select a Rule Type.
- Afterward, write down the following formula into the empty box below Format values where the formula is true text.
=$E5=1
- Then, click on the Format option.
- Another dialog box called Format Cells will appear.
- In the Fill tab, choose a color according to your desire to make the rows distinguishable. We have chosen Green, Accent 6, Lighter 60% color.
- In the end, click OK.
- Again, click OK to close the New Formatting Rule dialog box.
- Then, right-click on Column E.
- After that, select the Hide option on the context menu.
- As a result, the extra column is hidden now. And, the result looks like the image below.
6. Combining IF, MOD, and ROW Functions
In this process, the IF, MOD, and ROW functions will help us to get the binary grouping of our dataset. Let’s explore the method step by step.
📌 Steps
- Primarily, select cell E4 and write down Zero (0) into the cell.
0
- Then, press ENTER.
- Then, select cell E5 and write down the following formula into the cell.
=MOD(IF(ROW()=2,0,IF(B5=B4,E4,E4+1)),2)
ROW(): The ROW function returns the row number. In this case, the value is 5.
IF(B5=B4, E4, E4+1): The IF function checks the value of cell B5 with B4. If both values match each other, the function returns the value of cell E4. Otherwise, it will add 1 with the value of cell E4 and return that.
IF(ROW()=2,0,IF(B5=B4,E4, E4+1)): In this formula, the IF function checks whether the row number is equal to 2. If the logic is True, the function returns 0. Or, if the logic is False the function returns the result of the second IF function.
MOD(IF(ROW()=2,0,IF(B5=B4,E4, E4+1)),2): The MOD function will divide the result of the IF function by 2 and show the value of the remainder.
- Simply, press the ENTER key.
- Now, select the range of cells B5:E14.
- After that, go to the Home tab
- Then, click on the drop-down arrow of the Conditional Formatting on the Styles group.
- Later, select the New Rules option from the drop-down list.
- As a result, a small dialog box called New Formatting Rule will appear.
- Then, select Use a formula to determine which cells to format under the section of Select a Rule Type.
- Afterward, write down the following formula into the empty box below Format values where the formula is true text.
=$E5=1
- Then, click on the Format option.
- Another dialog box called Format Cells will appear.
- In the Fill tab, choose a color according to your desire to make the rows distinguishable. We choose Green, Accent 6, Lighter 60% color.
- In the end, click OK.
- Again, click OK to close the New Formatting Rule dialog box.
- In this way, you can give alternate row colors based on the value in Excel.
- Besides, you can follow the steps of Method 5 to hide the extra column.
7. Utilizing AND, IF, LEN, and MOD Functions
In this method, first, we are going to use the IF function in our dataset to get the numerical grouping. Besides that, we have to use the AND, LEN, and MOD functions in the Conditional Formatting rules to alternate the row color. The steps of this process are given below:
📌 Steps
- Firstly, select cell E4 and write down Zero (0) into the cell.
0
- Then, press ENTER.
- Then, select cell E5 and write down the following formula into the cell.
=IF(B5=B4,E4,E4+1)
IF(B5=B4, E4, E4+1): The IF function checks the value of cell B5 with B4. If both values match each other, the function returns the value of cell E4. Otherwise, it will add 1 with the value of cell E4 and return that.
- Simply, press the ENTER key.
- Now, select the range of cells B5:E14.
- After that, go to the Home tab
- Then, click on the drop-down arrow of the Conditional Formatting on the Styles group.
- Later, select the New Rules option from the drop-down list.
- As a result, a small dialog box called New Formatting Rule will appear.
- Then, select Use a formula to determine which cells to format under the section of Select a Rule Type.
- Afterward, write down the following formula into the empty box below Format values where the formula is true text.
=AND(LEN($B5)>0,MOD($E5,2)=0)
LEN($B5): The LEN function counts the length of the cell value. In this case, the value is 5.
MOD($E5,2): This function divides the value of cell E5 by 2 and shows the value of the remainder. Here, the result is 1.
AND(LEN($B5)>0, MOD($E5,2)=0): In this formula, the AND function checks whether the value of the LEN function is greater than 5 and the result of the MOD function is equal to 0. If both logics are True, the row will show our selected color. Otherwise, it shows as usual.
- Then, click on the Format option.
- Another dialog box called Format Cells will appear.
- In the Fill tab, choose a color according to your desire to make the rows distinguishable. We choose Green, Accent 6, Lighter 60% color.
- In the end, click OK.
- Again, click OK to close the New Formatting Rule dialog box.
- In this way, you can give alternate row colors based on the cell value in Excel.
8. Applying ISODD Function to Color Alternate Row Based on Cell Value in Excel
In this procedure, we are going to use the IF and SUM functions first, to get the numerical grouping of our dataset. Then, we will apply the ISODD function in Conditional Formatting to alternate the row color. The procedure of this process is shown as follows:
📌 Steps
- Firstly, select cell E4 and write down Zero (0) into the cell.
0
- Then, press ENTER.
- Then, select cell E5 and write down the following formula into the cell.
=IF(B4=B5,E4,SUM(E4,1))
SUM(E4,1): The SUM function will add 1 with the value of cell E4. For this cell, the function returns 1.
IF(B4=B5,E4,SUM(E4,1)): The IF function checks the value of cell B5 with B4. If both values match each other, the function returns the value of cell E4. On the other hand, if the logic is False, it returns the result of the SUM function.
- Simply, press the ENTER key.
- Now, select the range of cells B5:E14.
- After that, go to the Home tab
- Then, click on the drop-down arrow of the Conditional Formatting on the Styles group.
- Later, select the New Rules option from the drop-down list.
- As a result, a small dialog box called New Formatting Rule will appear.
- Then, select Use a formula to determine which cells to format under the section of Select a Rule Type.
- Afterward, write down the following formula into the empty box below Format values where the formula is true text.
=ISODD($E5)
Then, ISODD will determine if the corresponding cell value is odd or not, and if it is odd then it will return TRUE. If the logic is TRUE the row will show our selected color. Otherwise, it shows as usual.
- Then, click on the Format option.
- Another dialog box called Format Cells will appear.
- In the Fill tab, choose a color according to your desire to make the rows distinguishable. We choose Green, Accent 6, Lighter 60% color.
- In the end, click OK.
- Again, click OK to close the New Formatting Rule dialog box.
- In this way, you can give alternate row colors based on the cell value in Excel.
9. Linking ISEVEN Function with Filter Option
In this section, we are going to use the ISEVEN function and the Filter option to alternate the row colors based on values. Let’s explore the method step by step.
📌 Steps
- For convenience, we have added the Helper column.
- Secondly, select cell E5 and write down the following formula.
=ISEVEN(ROW())
The ROW function returns the row number. Then, ISEVEN will determine if the corresponding row number is even or not, and if it is even then it will return TRUE otherwise FALSE.
- Simply, press the ENTER key.
- At this moment, select the dataset. In this case, we selected cells in the B4:E14 range.
- Then, go to the Home tab.
- After that, select the Sort & Filter drop-down on the Editing group.
- Later, choose the Filter option.
- As a result, we will get the filtered table.
- At present, click on the dropdown symbol of the Helper column.
- Then, uncheck the TRUE option to hide it and press OK.
- As a result, the rows with TRUE will be hidden.
- Next, select the unhidden rows in the dataset.
- Now, go to the Home tab.
- Then, select the Fill Color drop-down on the Font group.
- Later, choose Green, Accent 6, Lighter 60% from the available colors.
- Finally, we are getting the rows highlighted.
- Again, click on the dropdown symbol of the Helper column.
- At this moment, select the option Clear Filter From “Helper”.
- Later, click on the OK button.
- Eventually, we are getting our desired alternate row colors based on the cell values.
- Again, select the whole dataset including the header row.
- Afterward, go to the Home tab.
- Next, select the Sort & Filter drop-down on the Editing group.
- Lastly, unclick the Filter option.
So, now we are having our normal data range having alternate row colors.
10. Employing VBA Code to Color Alternate Row Based on Cell Value in Excel
Employing the VBA code is always an amazing alternative. Follow the steps below to be able to solve the problem in this way.
📌 Steps
- Firstly, press the ALT+F11 key.
- Suddenly, the Microsoft Visual Basic for Applications window will open.
- Then, go to the Insert tab.
- After that, select Module from the options.
- It opens the code module where you need to paste the code below.
Sub Alternate_Row_Color()
Dim y As Integer
For y = 1 To Selection.Rows.Count
If y Mod 2 = 0 Then
Selection.Rows(y).Interior.Color = RGB(198, 224, 180)
End If
Next
End Sub
Here, we have declared y as Integer and the FOR loop will work for each row of our dataset, and the IF statement will ensure that if the row number is divisible by 2 or even then it will only be colored.
- Now, return to the sheet and select the dataset.
- Then, go to the Developer Tab.
- Next, select the Macros Option on the Code group.
- Suddenly, the Macro wizard will open up.
- Afterward, select the macro Alternate_Row_Color(that we have created in the previous step).
- Finally, press the Run button.
Finally, you can fill the alternate row with your desired color. One thing is to notice that here Excel is considering the first row of our selection as Row 1 and so it is odd (basically it was Row 5) and the following row as Row 2 and so it is colored because it is even ( actually it was Row 6).
Practice Section
For doing practice by yourself we have provided a practice section like below in the last sheet named Practice Section on the workbook. Please do it by yourself.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
Conclusion
This article provides easy and brief solutions to color alternate rows based on the cell values in Excel. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.
