How Do You Add and Customize Banded Rows in Excel?

To alternate row colors in Excel, apply a table style or use conditional formatting with a formula based on row numbers. Select the range and choose a table style when the list may grow, because new rows can follow the table's formatting automatically. If you prefer to keep the range as a regular worksheet area, use conditional formatting to create alternating row colors in Excel without converting the data into a table.

Add alternating row colors in Excel

The simplest method is Format as Table. It adds banded rows automatically, keeps the pattern lined up after sorting or filtering, and usually extends the color pattern when you add new records.

For a quick contact list, task tracker, budget, or inventory sheet, this is usually the method I would choose first. The only reason to avoid it is if you specifically need the range to stay as ordinary cells without table features.

Select the data range

Select only the cells that belong to the list, including the header row if you have one. Avoid grabbing blank spacer rows, notes, totals, or unrelated columns, because Excel will treat them as part of the formatted area.

A good check before you format: click the first cell of the list, then press Ctrl + A once. If Excel selects exactly the block you expected, your range is probably clean enough for table formatting.

Apply a table style with banded rows

1. Select the full list, including headers if they exist.
2. Go to Home and choose Format as Table.
3. Pick a style that has clear but light row shading.
4. Confirm the selected range.
5. Check My table has headers if the first row contains labels.
6. Click OK.

Light styles are usually better than dark ones. Dark banding may look neat on screen, but it can make numbers harder to read and can print poorly, especially on reports with small text.

Turn row banding on or off

Click inside the table, open the Table Design tab, and use the Banded Rows checkbox. Turning it off keeps the table features, such as filters and automatic expansion, but removes the striped look.

This is handy for a file that changes purpose. For example, you might keep banded rows while reviewing a long monthly expense list, then turn them off before sending a cleaner-looking version to someone who only needs the final totals.

Add alternating colors without an Excel table

Conditional formatting is the better choice when you want alternating colors but do not want filter arrows, table names, or structured references. It is also useful for forms, printable templates, and worksheets where the layout is already carefully arranged.

Apply conditional formatting

Select the range you want to shade, then go to Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format.

Enter =MOD(ROW(),2)=0, click Format, choose a fill color, and confirm the rule. Excel will shade every even-numbered row in the selected range.

Use a formula for every other row

The basic idea is simple: ROW() returns the row number, and MOD() checks whether that number falls into the part of the pattern you want to shade.

• =MOD(ROW(),2)=0 shades even-numbered rows.
• =MOD(ROW(),2)=1 shades odd-numbered rows.
• =MOD(ROW()-6,2)=0 shifts the pattern so row 6 acts as the starting point.

If your data begins under a title area or instruction block, use the shifted version. Otherwise the stripe pattern may technically work but look visually "off" because Excel is counting from the worksheet row number, not from the first row of your list.

Extend colors to new rows

Conditional formatting only applies to the range listed in the rule. If your rule stops at row 100, row 101 will not automatically inherit the color pattern unless Excel expands the range or you update it yourself.

For a recurring sheet, such as a weekly attendance log or invoice tracker, apply the rule to extra blank rows from the start. If the sheet is already built, go to Conditional Formatting > Manage Rules and edit the Applies to range.

Customize alternating row colors

Once the basic striping works, customize only what improves readability. A slightly different fill color, a better starting row, or a grouped pattern can help, but heavy styling can make a spreadsheet harder to use.

Change banded row colors

For an Excel table, click inside the table and choose another style from the Table Design tab. If you need a reusable look, you can create or duplicate a table style in versions of Excel that support custom table styles.

For conditional formatting, open Manage Rules, select the alternating-row rule, choose Edit Rule, and change the fill under Format. Pale gray, soft blue, or a muted green usually works better than bright colors because the data stays easier to read.

Start colors from a specific row

Use an offset in the formula when the real data starts lower in the worksheet. If your first data row is row 6, use =MOD(ROW()-6,2)=0 to make row 6 the anchor for the pattern.

This matters in templates with a logo, title, or instructions at the top. You do not want the decorative rows to control the striping; you want the first actual record to set the rhythm.

Create custom row patterns

You can build patterns beyond one shaded row and one plain row. For example, MOD(ROW(),3) can support a three-row cycle, while MOD(ROW(),4) can support grouped patterns such as two shaded rows followed by two plain rows.

Use custom patterns sparingly. They can help in printed schedules, classroom rosters, or inspection sheets where rows are reviewed in sets, but they are usually unnecessary for a normal list of transactions or names.

Fix alternating row color problems

When row colors disappear or stop lining up, check the formatting source first. The fix is different depending on whether the colors came from an Excel table, a conditional formatting rule, or manual fill colors pasted into the sheet.

Restore missing row colors

If the range is a table, click inside it and check whether Banded Rows is still enabled under Table Design. Also try another table style if the current style has very subtle or no visible banding.

If the colors came from conditional formatting, open Manage Rules and confirm two things: the rule still exists, and the Applies to range still covers the affected cells.

Keep colors after adding rows

For a list that grows often, an Excel table is the safer setup. Type directly below the last table row and Excel normally expands the table, carrying the banded rows with it.

With conditional formatting, plan ahead. Either include extra blank rows in the rule range or update the range after adding more data. This is the difference between a low-maintenance sales log and a sheet where the newest rows keep appearing unformatted.

Remove conflicting formatting

Manual cell fills and copied formatting are common reasons alternating row colors look broken. If someone pasted data from another workbook, the pasted cells may carry their own fill color or conditional formatting rule.

• Use Paste Special > Values when importing data.
• Clear direct fill colors from cells that should follow the stripe pattern.
• Delete duplicate conditional formatting rules in Manage Rules.
• Check rule order if several rules apply to the same range.

To remove alternating colors completely, turn off Banded Rows for a table or delete the conditional formatting rule that creates the striping.

Conclusion

For most growing lists, use an Excel table and let banded rows handle the work for you; for fixed layouts or templates, use conditional formatting with a row formula. The main thing is to choose the method based on how the worksheet will be used, because a simple stripe pattern should make the data easier to read, not create another formatting task to maintain.