To add color to alternating rows in Excel, select your data and choose Insert > Table, then select a table style with banded rows. For a custom color or a range that should not become a table, use conditional formatting with the formula =MOD(ROW(),2)=0. Both methods make long lists easier to scan; the best choice depends on whether you want Excel to manage the data as a table.
The steps below are for Microsoft Excel for Microsoft 365 on Windows. The main ribbon commands are also available in Excel for Mac, although tab names and shortcuts can differ slightly. You do not need an add-in, and you can use either method on a new or existing worksheet.
What You’ll Need
- An Excel workbook containing the rows you want to format. A header row is useful, but the conditional-formatting method can also be used without one.
- A color choice that keeps text readable. Pale fills usually work well for lists with black text.
- Optional: a decision about whether you want a formatted Excel table or just alternating color on a normal range.
Step-by-Step
-
Choose the cells to format. Identify the full set of columns and rows you want to shade, including any header if you plan to make a table. For example, if your headers are in row 1 and your records run from rows 2 through 50 in columns A through D, your data range is A1:D50. Avoid selecting entire worksheet columns unless you actually want formatting applied far beyond your list.
If you are using conditional formatting, you will select the data cells only when creating the rule. Exclude the header row if you want it to keep a separate header style. Make a note of the first row in your selected data; it matters when you set the formula so the first record gets the intended color.
-
Use a table for the quickest, automatically banded result. Click any cell in your data, then choose Insert > Table. Check that the range in the dialog covers all the intended columns and rows. If the first row contains column labels, check My table has headers; otherwise, leave it unchecked. Select OK.
On Windows, you can also select a cell in the data and press Ctrl+T. In Excel for Mac, Command+T commonly opens the Create Table dialog; if that shortcut is intercepted by your setup, use Insert > Table. Creating a table adds table features such as filter buttons and structured column headings, so use the next method if you only want color and do not want those features.
-
Pick a table style with alternating bands. Click inside the new table to show the Table Design tab on the ribbon. In the Table Style Options group, make sure Banded Rows is checked. Browse the Table Styles gallery and choose a style whose alternating fills are easy to distinguish without making the text hard to read. On some Mac layouts the contextual tab may be labeled Table rather than Table Design.
If you do not see the colors change, check that the active cell is inside the table and that Banded Rows is turned on. You can switch styles later from the same gallery. A table’s header has its own formatting, so the first colored data band begins with the first record beneath the header.
-
Use conditional formatting if you want to keep a normal range. Select the data cells, leaving the header out if it should remain unshaded. Choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, choose Use a formula to determine which cells to format.
With the data beginning in row 2, enter
=MOD(ROW(),2)=0in the formula box. This rule is TRUE for even-numbered worksheet rows, so it shades rows 2, 4, 6, and so on within the selected range. It does not matter which column the selection starts in because ROW() returns the worksheet row number. If your selected data begins on row 3 and you want that first selected row shaded, use=MOD(ROW()-ROW($A$3),2)=0instead. Replace A3 with a cell in the first row of your selected range; the column reference is anchored so the formula works across the selection. -
Choose the fill color for the rule. In the New Formatting Rule dialog, click Format, open the Fill tab in Format Cells, and select the background color you want. Click OK to close Format Cells, then click OK again to create the rule. Choose a light fill if the cells contain text or numbers that need to remain easy to read.
Conditional formatting does not directly color the font unless you set a font option too. The Fill choice controls the cell background. Avoid choosing a fill that is almost the same as your worksheet grid or that makes existing font colors difficult to see.
-
Check the range and the alternating pattern. Look at the first few data rows and confirm that the color appears on every other row, not every other cell. If the first row is the wrong color, edit the rule formula to reverse the test or adjust the starting-row version. If some columns are missing the fill, edit the rule’s Applies to range so it covers all the intended data columns.
To review the rule, select a cell in the range and choose Home > Conditional Formatting > Manage Rules. Set the Show formatting rules for menu to This Worksheet if you need to find a rule that is not listed for the active selection. You can edit the formula, the fill, or the Applies to range there.
-
Decide how future rows should be handled. A table normally extends its table style when you add records directly below it, keeping the banded appearance. A conditional-formatting rule applies only to its Applies to range. To cover additional rows, edit that range in Manage Rules or extend the selection before creating the rule. For example, if your list may grow, you can apply the rule to a reasonably sized block of rows, while avoiding a needlessly huge range that may make a large workbook slower.
Common Mistakes
Selecting the header and treating it like a record: With conditional formatting, including the header can color it according to its worksheet row number. Select only the rows you want striped, then format the header separately if needed. With a table, the header is styled independently, so confirm that Excel recognized it by checking the My table has headers option.
Using the wrong starting-row formula: The basic MOD formula alternates according to worksheet row numbers. If your data begins on row 3, the first selected row will not be colored by the even-row rule. That is not a broken rule; it is a mismatch between the formula and the desired starting color. Use the relative starting-row formula shown above, or reverse the test with =MOD(ROW(),2)=1 when the selected range starts on an odd-numbered row and that is the desired pattern.
Applying the rule to only one column: Conditional formatting colors only cells inside its Applies to range. If you select column A but expect the full record across columns A to D to be shaded, the other columns will stay unchanged. Select the complete data width first, or update the Applies to range later.
Adding direct fill colors on top of conditional formatting: A cell’s visible appearance may be controlled by a conditional-formatting rule, so choosing a regular fill color may not give the result you expect. Review or remove conflicting rules through Home > Conditional Formatting > Manage Rules before applying a different pattern.
Tips
Use a table when the range is a real list that you plan to sort, filter, or extend. Besides banded rows, tables provide filter controls and keep a consistent style as the table grows. If you only need a visual stripe pattern on a report, template, or fixed block of cells, conditional formatting gives you more control without converting the range into a table.
Pick a subtle color and preview it against the actual contents. A pale blue or gray often gives enough contrast to help the eye follow a line across several columns, while darker fills can reduce text legibility. If the worksheet already uses color to indicate status, avoid reusing a strong status color for every other row; readers could mistake the stripe for meaningful data.
For a custom two-color pattern, you can create two conditional-formatting rules, each with its own fill. For a range starting in row 2, use =MOD(ROW(),2)=0 for one band and =MOD(ROW(),2)=1 for the other. This is useful when alternating colors are part of a template, though in most cases leaving one band white and coloring the other is simpler and clearer.
After sorting or filtering, review the appearance of the list. Table banding is designed to follow table rows as part of the table style. A rule based on ROW(), by contrast, evaluates worksheet row numbers; after a sort, the color marks row positions rather than staying attached to a particular record. If the color is meant to identify a record category or status, use a rule based on the relevant data value instead of row parity.
Troubleshooting
No rows change color: First confirm that the conditional-formatting rule exists and that its Applies to range includes the selected cells. Open Manage Rules and look for a formula that has been entered without the leading equals sign, contains a typo, or refers to the wrong starting row. For a table, click inside it and confirm Banded Rows is checked, then try selecting a style from the Table Styles gallery.
The first colored row is the opposite of what you expected: The standard formula tests even worksheet row numbers. Switch the test from 0 to 1, or use the formula that subtracts the row number of the first selected data row. Check the result on the first two or three records so you can see that the pattern alternates as intended.
Only part of each row is colored: Expand the rule’s Applies to range to include all the columns. If the range is correct but cells still look different, another conditional-formatting rule may be taking precedence or a manually applied fill may be confusing the result. Review the rules for that range and remove or adjust conflicting formatting.
New records are not striped: If you used conditional formatting, add the new rows to the Applies to range in Manage Rules. If you used a table, enter or paste the new records immediately beneath the table and check that Excel has expanded it; a blank row between the table and new data can prevent the new entries from joining the table automatically. You can also resize the table from Table Design > Resize Table.
The pattern looks inconsistent after a sort: A row-number-based rule colors positions, not particular records. This behavior is expected. If stripes should remain attached to individual records, add a helper column or use formatting based on a meaningful field; if stripes are only for readability, position-based alternating bands are usually appropriate.
Frequently Asked Questions
Can I add alternating row color without making a table?
Yes. Select the data range and create a formula-based conditional-formatting rule through Home > Conditional Formatting > New Rule. Set the fill color and make sure the Applies to range covers the entire block. This changes the appearance without adding table filters or table-specific behavior.
Will the color update when I add more rows?
A table style generally continues onto rows added to the table. A conditional-formatting rule updates only within its Applies to range, so extend that range or include likely future rows when you first create the rule. Check that new entries are inside the table or formatted range.
Can I start with the second data row colored instead?
Yes. Change the formula so the first selected row evaluates as FALSE and the next one as TRUE. For example, if the selected range starts in row 2, =MOD(ROW(),2)=1 colors odd worksheet rows, making row 3 the first colored record. The starting-row formula can also be adjusted to match any selected range.
Can I use a different color for each alternating row?
Yes. Create two conditional-formatting rules for the same range, one for each MOD result, and choose a separate fill for each rule. For a starting range in row 2, use the even-row test for one color and the odd-row test for the other. Check the range and preview both fills for readable text.
Does this work in Excel for Mac or Excel for the web?
The table and conditional-formatting approaches are available in Excel for Mac, though the ribbon layout and shortcut keys may differ. Excel for the web also supports tables and conditional formatting, but some dialog options may be arranged differently. If a shortcut does not work, use the ribbon paths in the steps.
Related
- Excel File Too Large to Send? Fixes and Workarounds
- Export to CSV vs Excel: Which Format Should You Choose?
- Extract Data from Google Maps to Excel: Practical Options
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Lock Cells in Excel With a PasswordTo lock cells in Excel with a password, first unlock any cells people should still be able to…
- Google Sheets IF ISBLANK: Check for Empty CellsIn Google Sheets, use IF with ISBLANK to return one result when a cell is truly empty and…
- How to Split a Cell in Google Sheets: Text and DataTo split text in a Google Sheets cell into separate columns, select the cell or range, then choose…
- Freeze Panes in Excel: How to Keep Rows and Columns VisibleIn Excel for Microsoft 365 on Windows, use View > Freeze Panes to keep rows or columns visible…
- XLOOKUP in Google Sheets: How It WorksYes—Google Sheets has a built-in XLOOKUP function, so you can search a row or column and return a…
- Hanging Indent in Word: Simple Steps for Any DocumentTo add a hanging indent in Word, select the paragraph or paragraphs, open the Paragraph settings, choose Hanging…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.