To color every other row in Excel, select your data and choose Home > Format as Table for automatic banded rows, or use conditional formatting for more control. A table is usually quickest and keeps the pattern as you add data; a formula-based rule lets you choose the exact rows and fill color.
These instructions are for Microsoft Excel for Microsoft 365 on Windows. The main options are also available in Excel for Mac and Excel for the web, though some tab names and controls may look slightly different. Save a copy first if you are making changes to an important workbook.
What You’ll Need
- An Excel workbook with the rows you want to format. Decide whether the column headings should be included in the formatted range.
- A preferred fill color, if you want a specific look. Excel’s table styles offer ready-made colors; conditional formatting lets you pick a custom fill.
- For the formula method, note the address of the first data cell in the range you will format, such as A2.
Step-by-Step
- Choose the method that fits your sheet. Use Format as Table if the data forms a list with one header row and you want banding that extends when you add rows to the table. Use conditional formatting if you need to preserve a plain range, control the fill precisely, or color a selected portion of the worksheet. Both methods change the appearance, not the underlying cell values.
- For a table, select the data. Click a cell inside your list, or select the full range, including the header row. Check that there are no completely blank rows or columns splitting the list. On the Home tab, choose Format as Table, then select a style with alternating bands. Excel displays a Create Table dialog; confirm the range and check My table has headers if the first row contains labels. Choose OK.
- Check the table’s banding setting. Click inside the new table to show the Table Design tab. In Table Style Options, make sure Banded Rows is selected. If the color contrast is too strong or too faint, return to Home > Format as Table and choose another style. The exact style thumbnails can differ by Excel theme and version.
- For a regular range, select the cells to format. Select all columns and rows that should share the alternating pattern. For example, if the records are in A2:D30 and row 1 contains headings, select A2:D30 to band the data rows without applying the rule to the headings. Avoid selecting only one column if you want the color to run across each complete record.
- Create a conditional formatting rule. With the range selected, choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, select Use a formula to determine which cells to format. Enter a formula based on the first row of the selected range. If the selected range begins in row 2, enter
=MOD(ROW()-ROW($A$2),2)=0. Replace A2 with the first cell in the first data row of your selected range. For example, if your selected range starts at B5, use=MOD(ROW()-ROW($B$5),2)=0. - Choose the fill color. Select Format in the rule dialog, open the Fill tab, and choose a color. Select OK to close Format Cells, then select OK again to create the rule. The first selected row will be colored, the next left unfilled by this rule, and the pattern will repeat down the selected range. If you prefer to color the second, fourth, and other even-positioned rows instead, change the final 0 in the formula to 1.
- Check the rule’s range and preview the result. Open Home > Conditional Formatting > Manage Rules. Set Show formatting rules for to the current selection if needed. Confirm that Applies to covers the full range you intended, such as =$A$2:$D$30. The formula’s row reference should match the first row in that range. Select Apply or OK, then check the top, middle, and bottom of the data.
- Extend the pattern when the data grows. A table normally expands its banding when you type directly below it or add a row within it. For a conditional formatting rule, select the rule in Manage Rules and edit its Applies to range to include the new rows. You can also copy a formatted cell into the additional range, but check the rule range afterward so it does not become fragmented or inconsistent.
The formula uses the row’s position relative to the first row in the selection. This matters because a rule such as =MOD(ROW(),2)=0 colors worksheet rows with even row numbers. If your selected data starts on row 3, that simple formula colors the second selected row first, not the first. The relative-position version avoids that surprise.
Common Mistakes
Including the header in the formula range by accident: If you select the header and data but use a formula anchored to the first data row, the pattern can shift or the header can be colored. Select the data rows separately, or create a separate header format.
Anchoring the wrong cell: The cell inside ROW($A$2) should be in the first row of the range and should stay fixed as Excel evaluates the rule. If the first data row is 5, replace A2 with a cell from row 5. The column letter is less important to ROW than the row number, but using the range’s top-left cell makes the rule easier to understand.
Applying the rule to just one column: If the rule applies to A2:A30, only column A receives the fill. Include every data column, for example A2:D30, when each band should span the complete record.
Expecting conditional formatting to alter existing cell fills: A conditional format controls the displayed fill while its rule is true. It does not erase a manually applied fill underneath. Conflicting formatting can make the result look different from the chosen color; clear old fills or review competing rules if necessary.
Turning on table banding but expecting custom formula behavior: Table styles alternate rows automatically, but they are not the same as a conditional formatting rule. Use the method that suits the sheet rather than layering both on the same cells.
Tips
- Choose a light tint with readable text. Strong fills can make numbers, gridlines, and printed pages harder to scan. Check the result at normal zoom and in print preview if the sheet will be printed.
- Use a table when you regularly sort, filter, or append records. Tables include filter buttons and can carry formatting to new table rows. If you do not want the filter buttons, Table Design includes a Filter Button option you can turn off.
- Use conditional formatting when only certain columns should be shaded, when the data is not a proper table, or when you need the fill to follow a fixed worksheet range. You can later change its color through Manage Rules without rebuilding the rule.
- For the opposite starting pattern, change the formula’s ending test from =0 to =1. For example, with the first data row at A2,
=MOD(ROW()-ROW($A$2),2)=1leaves the first selected row unfilled and colors the next one. - When adding data to a worksheet, consider whether new rows will be inserted inside the formatted range or appended below it. A table is generally more dependable for a growing list; a conditional formatting range may need to be enlarged.
- On Excel for Mac, use the Home tab’s Format as Table or Conditional Formatting controls; the dialogs are similar, though their layout can vary. Excel for the web also offers table styles and conditional formatting, but some advanced rule-editing controls may be more limited than in the desktop app.
Troubleshooting
No rows changed color: Check that the rule was created with Use a formula to determine which cells to format, that a fill was chosen under Format, and that the Applies to range contains the selected cells. For the formula method, the formula must begin with an equals sign. Also make sure the rule was not accidentally set to a text or value-based rule.
The wrong rows are shaded: Confirm the row number in the formula’s anchor matches the top row of the Applies to range. If you used =MOD(ROW(),2)=0, the pattern follows worksheet row numbers, so changing where the range begins can reverse which selected row is colored. Use the relative-position formula to make the first selected row the starting point.
The color appears only in some columns: Open Manage Rules and inspect Applies to. Expand the range across the columns you want shaded. If the range is correct but only some cells look different, check for other conditional formatting rules or manually applied fills that may be affecting the display.
New rows do not have bands: If you used a conditional formatting rule, edit Applies to so it includes the added rows. If you used a table, make sure the new rows are part of the table; click the last table cell and press Tab to create a new table row, or type immediately below the table and confirm it expands.
You want to remove the formatting: For a conditional formatting rule, select the affected range and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. For a table, click inside it and clear the Banded Rows checkbox on Table Design. This turns off the alternating table bands without deleting the data. To remove the table structure as well, use Table Design > Convert to Range and confirm; the existing cell formatting may remain, so clear it separately if needed.
Excel displays an unexpected table range: If the Create Table dialog includes unrelated cells, cancel and select the intended range manually before choosing Format as Table. Check for nearby notes or totals that could be mistakenly absorbed into the table.
Frequently Asked Questions
Can I color every other row without turning my data into a table?
Yes. Select the range and create a conditional formatting rule using the formula method above. It applies the fill to the rows that meet the formula, while leaving the range as ordinary cells.
Will alternating colors update when I sort the data?
Table banding follows the table’s row order after sorting. A conditional formatting rule based on ROW also follows worksheet positions, so the banded pattern remains alternate by position as records move.
Can I use a different color for every third row?
Yes. In the formula rule, replace the final comparison with a remainder check for three-row cycles. For a range beginning at A2, use =MOD(ROW()-ROW($A$2),3)=0 to color the first, fourth, seventh, and subsequent rows. Choose a different remainder, 1 or 2, to start on another position in the cycle.
Does the formula method color blank rows too?
It will color every row in its Applies to range, including blank rows inside that range, because the formula checks row position rather than cell contents. Limit Applies to to the data area, or add a condition based on a column that identifies populated records.
Can I keep the header a different color?
Yes. Exclude the header from the conditional formatting range and format it separately. With a table, use the table style’s header formatting and keep Banded Rows enabled for the body.
Can I apply the same banding to multiple separate ranges?
Yes, but separate conditional formatting ranges can be harder to maintain. Create a rule for each range and use an anchor that matches the first row of each range. If the areas are part of one continuous data list, formatting them together is simpler.
Related
- Change Table Color in Excel: Styles and Custom Formatting
- Excel Subscript: How to Change Text to Subscript
- How to Change Strikethrough Color in Excel Cells Easily
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Find Duplicates in Google Sheets: Easy MethodsThe easiest way to find duplicates in Google Sheets is to select the cells, choose Format > Conditional…
- Microsoft Excel: How to Turn Off Flash FillTo turn off automatic Flash Fill in Microsoft Excel for Microsoft 365 on Windows, go to File >…
- How to Number Lines in Excel: 4 Reliable MethodsTo number lines in Excel, enter =ROW()-1 in the first numbering cell below a header in row 1,…
- Find and Replace Alt+Enter in Excel: Remove Line BreaksTo find and replace an Alt+Enter line break in Excel for Microsoft 365 on Windows, press Ctrl+H, click…
- How to Hide Divide by Zero Errors in ExcelTo hide a divide-by-zero error in Excel, wrap the division in IFERROR, or use an IF test when…
- Excel File Large for No Reason? Causes and Fixes to TryAn Excel file that seems far too large usually contains hidden formatting, an oversized used range, embedded objects,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.