How to Alternate Row Colors in Excel Without Table (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

For the purpose of attracting your data, you can alternate Row colors in Excel without even making a Table. In this article, I will explain how to alternate Row colors in Excel without Table.

Here, I will describe 5 methods to alternate Row colors in Excel without Table. Also, for your better understanding, I’m going to use sample data which has 4 columns. These are Product, Sales, Profit, and Status.

How to Alternate Row Colors in Excel Without Table


1. Using Fill Color Option to Alternate Row Colors in Excel Without Table

You can use the Fill Color feature to alternate Row colors in Excel without Table. This is absolutely a manual process. So, when you have so much data then it will be quite time-consuming. The steps are given below.

Steps:

  • Firstly, you have to select the Rows which you want to color. Here, I have selected Rows 6, 8, 10, 12, and 14.

  • After that, you need to go to the Home tab.
  • Now, from the Fill Color feature >> you have to choose any of the colors. Here, I have chosen Green, Accent 6, Lighter 60%. In this case, try to choose any light color. Because the dark color may hide the inputted data. Then, you may need to change the Font Color.

How to Alternate Row Colors in Excel Without Table

Finally, you will see the result with alternate Row colors.


2. Applying Cell Styles Feature to Alternate Row Colors in Excel

You can use the Cell Styles feature to alternate Row colors in Excel without Table. This is absolutely a manual process. So, when you have so much data then it may be quite time-consuming. The steps are given below.

Steps:

  • Firstly, you have to select the Rows which you want to color. Here, I have selected Rows 6, 8, 10, 12, and 14.
  • Secondly, from the Home tab >> you need to go to the Cell Styles feature.
  • Thirdly, choose your preferred colors or styles. Here, I have chosen the Calculation.

How to Alternate Row Colors in Excel Without Table

Finally, you will see the following result with alternate Row colors.


3. Applying Conditional Formatting with Formula to Alternate Row Colors in Excel Without Table

You can apply Conditional Formatting with the formula. Here, I will use two different formulas with the ROW function. In addition, I’m going to use the MOD and ISEVEN functions.


3.1. Use of MOD and ROW Functions

Let’s start with the MOD and ROW functions to alternate Row colors in Excel without Table. The steps are given below.

Steps:

  • Firstly, you should select the data up to which you want to apply the Conditional Formatting to alternate the row colors. Here, I have selected the data range B5:E14.

How to Alternate Row Colors in Excel Without Table

  • Now, from the Home tab >> you must go to the Conditional Formatting command.
  • Then, you need to choose the New Rule option to apply the formula.

At this time, a dialog box named New Formatting Rule will appear.

  • Now, from that dialog box >> you have to select Use a formula to determine which cells to format.
  • Then, you need to write down the following formula in the Format values where this formula is true: box.
=MOD(ROW(),2)
  • After that, go to the Format menu.

How to Alternate Row Colors in Excel Without Table

Formula Breakdown

  • Here, the ROW function will count the number of Rows.
  • The MOD function will return the remainder after division.
  • So, MOD(ROW(),2)–> Becomes 1 or 0 because the divisor is 2.
  • Finally, if the Output is 0 then there will be no fill color.

At this time, a dialog box named Format Cells will appear.

  • Now, from the Fill option >> you have to choose any of the colors. Here, I have chosen Green, Accent 6, Lighter 40%. In this case, try to choose any light color. Because the dark color may hide the inputted data. Then, you may need to change the Font Color.
  • Then, you must press OK to apply the formation.

  • After that, you have to press OK on the New Formatting Rule dialog box. Here, you can see the sample instantly in the Preview box.

How to Alternate Row Colors in Excel Without Table

Finally, you will get the result with alternate Row colors.


3.2. Use of ISEVEN and ROW Functions to Alternate Row Colors in Excel 

Now, I will show you the use of ISEVEN and ROW functions to alternate Row colors in Excel without Table. The steps are similar to the previous method.

  •  Firstly, you have to follow method-3.1 to open the New Formatting Rule window.
  • Secondly, from that dialog box >> you have to select Use a formula to determine which cells to format.
  • Thirdly, you need to write down the following formula in the Format values where this formula is true: box.
=ISEVEN(ROW())
  • Finally, go to the Format menu.

Formula Breakdown

  • Here, the ISEVEN function will return True if the value is an even number.
  • The ROW function will count the number of Rows.
  • So, if the Row number is odd then the ISEVEN function will return FALSE. As a result there will be no fill color.

At this time, a dialog box named Format Cells will appear.

  • Now, from the Fill option >> you have to choose any of the colors. Here, I have chosen Gold, Accent 4, Lighter 60%. Also, you can see the formation below in the Sample box. In this case, try to choose any light color. Because the dark color may hide the inputted data. Then, you may need to change the Font Color.
  • Then, you must press OK to apply the formation.

How to Alternate Row Colors in Excel Without Table

  • After that, you have to press OK on the New Formatting Rule dialog box. Here, you can see the sample instantly in the Preview box.

Lastly, you will see the result with alternate Row colors.

How to Alternate Row Colors in Excel Without Table


4. Using Excel Formula with Sort & Filter Command to Alternate Row Colors Without Table

You can use a formula with the Sort & Filter command to alternate Row colors in Excel without Table. Furthermore, I will use the MOD, IF, and ROW functions in the formula. The steps are given below.

Steps:

  • Firstly, you have to select a cell, where you want to keep the output. I have selected the F5 cell.
  • Secondly, use the corresponding formula in the F5 cell.
=MOD(IF(ROW()=2,0,IF(E5=E4,F4, F4+1)), 2)

Formula Breakdown

  • Here, IF(E5=E4,F4, F4+1)–> This is a logical test where if the value of E5 cell is equal to E4 cell then it will return the value of F4 cell otherwise it will give 1 increment with F4 cell value.
    • Output: 1
  • Then, the ROW() function will count the number of Rows.
    • Output: 5
  • IF(5=2,0,1)–> This logical test says that if 5 is equal to 2 then it will return 0 otherwise it will return 1.
    • Output: 1
  • The MOD function will return the remainder after division.
  • Finally, MOD(1,2)–> becomes.
    • Output: 1

  • After that, you have to press ENTER to get the result.

How to Alternate Row Colors in Excel Without Table

  • Subsequently, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells F6:F14.

At this time, you will see the following result.

How to Alternate Row Colors in Excel Without Table

  • Now, select the data range. Here, I have selected B4:F14.
  • Then, from the Home ribbon >> go to the Editing tab.
  • Then, from the Sort & Filter feature >> you have to choose the Filter option. Here, you can apply the Keyboard technique CTRL+SHIFT+L.

At this time, you will see the following situation.

How to Alternate Row Colors in Excel Without Table

  • Now, you should click on the Drop-Down Arrow on the F column.
  • Then, select 1 and uncheck 0.
  • Finally, press OK.

Subsequently, you will see the following filtered output.

How to Alternate Row Colors in Excel Without Table

  • After that, you need to select the filtered data.
  • Then, you have to go to the Home tab.
  • Now, from the Fill Color feature >> you have to choose any of the colors. Here, I have chosen Green, Accent 6, Lighter 60%. In this case, try to choose any light color. Because the dark color may hide the inputted data. Then, you may need to change the Font Color.

  • Now, to remove the Filter feature, from the Home ribbon >> go to the Editing tab.
  • Then, from the Sort & Filter feature >> you have to choose again the Filter option.
  • Otherwise,  you can press CTRL+SHIFT+L to remove the Filter feature.

Lastly, you will see the result with the same Row colors for the same Status.

How to Alternate Row Colors in Excel Without Table


5. Applying VBA Code to Alternate Row Colors in Excel Without Table

You can employ a VBA code to alternate Row colors in Excel without Table. The steps are given below.

Steps:

  • Firstly, you have to choose the Developer tab >> then select Visual Basic.

  • Now, from the Insert tab >> select Module.

How to Alternate Row Colors in Excel Without Table

  • Write down the following Code in the Module.
Sub ChangeRowColors()
Dim range As range
Dim chr As Long
Dim NoColor As Long
Dim Colored As Long
'I will Define Color as Input
  NoColor = vbWhite
  Colored = RGB(0, 255, 255)
'Select a range as variable
  Set range = Selection
'You should select more than 1 Row
  If range.Rows.Count = 1 Then Exit Sub
'Loop for Color Changing
  For chr = 1 To range.Rows.Count
    If chr Mod 2 = 0 Then
      range.Rows(chr).Interior.Color = Colored 'Even Row
    Else
      range.Rows(chr).Interior.Color = NoColor 'Odd Row
    End If
  Next chr
End Sub

Code Breakdown

  • Here, I have created a Sub Procedure named ChangeRowColors.
  • Next, declare some variables range as Range to call the range; chr as Long; NoColor as Long; Colored as Long.
  • Here, RGB (0, 255, 255) is a light color called Aqua.
  • Then, the Selection property will select the range from the sheet.
  • After that, I used a For Each Loop to put Color in each alternate selected Row using a VBA IF Statement with a logical test.

  • Now, Save the code then go back to Excel File.

How to Alternate Row Colors in Excel Without Table

  • After that, select the range B5:E14.
  • Then, from the Developer tab >> select Macros.

  • At this time, select Macro (ChangeRowColors) and click on Run.

How to Alternate Row Colors in Excel Without Table

Finally, you will see the result with alternate Row colors.


💬 Things to Remember

  • When you have lots of data then you should use method 3 (Conditional Formatting) or method 5 (VBA Code). This will save your time to alternate Row colors.
  • In the case of a tiny dataset, you can easily use method 1 (Fill Color) or method 2 (Cell Styles).
  • Furthermore, when you want to color similar data or something sorted then you should use method 4 (Sort & Filter).

Download Practice Workbook

You can download the practice workbook from here:


Conclusion

I hope you found this article helpful. Here, I have explained 5 methods to Alternate Row Colors in Excel Without Table. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Alternating Row ColorsHighlight Row | Highlight in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo