3 Ways To Alternate Row Colors in Excel [Guide]
How to Alternate Shading Row Colors (Banded Rows)
While Microsoft Excel does not have a dedicated row banding button to alternate row colors, we can obtain this shading effect in a few creative ways. In this article, we will walk through three solutions that will provide a way forward for how you might want to accomplish this:
- Excel Table
- Conditional Formatting
- VBA Macro Code
Method 1: Utilize An Excel Table To Color Every Other Row
An Excel Table is an object you can insert to allow for your data to be dynamically referenced throughout your spreadsheet. There are limitations that come with the Table object (such as every column has to have a unique heading), but if you can live with some of the restrictions, this is a great way to alternate row color shades automatically.
Converting your spreadsheet range to a table object is as easy as
- Select your data range
- Navigate to the Insert Tab on your Ribbon Menu
- Click the Table Button
- Tell the dialog box if your selection included headers
- Click OK
Alternatively, you can select your data range and use the keyboard shortcut Ctrl + t to get to the Insert Table dialog box.
If you do not like one of the default Table Style options, you can create your own Table format to get specific row colors. Just click New Table Style at the very bottom of the Table Styles gallery in Excel Ribbon.
Method 2: Conditional Formatting To Alternate Colors
If you don’t want to utilize an Excel table, you can alternatively utilize conditional formatting rules to color every other row in your Microsoft Excel spreadsheet.
The only disadvantage to this method is you (as of this writing) cannot reference named ranges in conditional formatting rules. This means if you add a new row to the bottom of the table at a later date, you will need to update the conditional formatting data range to ensure the new row is included in the rule.
Note: If you insert a row inside your currently formatted rows, the conditional formatting rule will automatically adjust to include it.
To create this color banding, you will need to select the cell range you are targeting and add a new Conditional Formatting rule.
- Navigate to the Home Tab
- Click the Conditional Formatting menu button
- Select New Rule…
- In the New Formatting Rule dialog box, select “Use a formula to determine which cells to format”
Enter one of the MOD functional rules noted below:
- For even-numbered rows, you’ll want to use one of the following formulas:
- =ISEVEN(ROW())
- =MOD(ROW(),2)=0
- For odd-numbered rows, you’ll want to use one of the following formulas:
- =ISODD(ROW())
- =MOD(ROW(),2)>0
- For even-numbered rows, you’ll want to use one of the following formulas:
- Click the Format Button and select the specific formatting you wish to apply (I just chose a fill color of gray)
- Click OK Button
- Repeat steps 1-7 for the secondary color
Once you’ve created both your primary and secondary banding conditional formatting rules, you should see the alternating colors automatically apply.
Method 3: VBA Macro Code To Band Rows
Maybe you are looking for a solution that is more ad-hoc or on-demand. For times when you want to quickly format a table before sending it off to the executives, having a personal macro that can band your data in a pinch might be the right answer for your needs.
The below code allows you to select a range on your spreadsheet and quickly alternate two different colors across the rows. Just programmatically define the two color codes you wish to use (you can reference VB colors or an RGB color code for more flexibility/variety) at the beginning of the code.
The remaining VBA code loops through each row and alternates the fill colors applied based on odd/even rows. If you would like to learn a bit more about the technique used to determine odd/even rows, you can check out this article: VBA To Determine If Number Is Odd Or Even.
Sub AlternateRowColors()
'PURPOSE: Alternate row fill colors based on selected range
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
Dim rng As Range
Dim x As Long
Dim LightColorCode As Long
Dim DarkColorCode As Long
'Define Colors (Input)
LightColorCode = vbWhite
DarkColorCode = RGB(242, 242, 242)
'Ensure a Range is Selected
If TypeName(Selection) <> "Range" Then Exit Sub
'Store Selected range to a variable
Set rng = Selection
'Check for more than 1 row selected
If rng.Rows.Count = 1 Then Exit Sub
'Loop through each row in selection and color appropriately
For x = 1 To rng.Rows.Count
If x Mod 2 = 0 Then
rng.Rows(x).Interior.Color = DarkColorCode 'Even Row
Else
rng.Rows(x).Interior.Color = LightColorCode 'Odd Row
End If
Next x
End SubDownload The Excel Example File For Alternating Row Colors
If you want a copy of the Excel file I used throughout this article, feel free to download the spreadsheet directly by clicking the download button below.
I Hope This Article Helped!
Hopefully, I was able to explain how you can use a number of different methods in Excel to alternate row colors in a given cell range. If you have any questions about these techniques or suggestions on how to improve them, please let me know in the comments section below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.