Alternating Color Rows in Excel: 2 Easy Methods

To add alternating color rows in Excel, select your data and choose Insert > Table, then pick a table style with banded rows. For more control, select the range and use Home > Conditional Formatting to create a formula-based rule. Alternating fills make records easier to follow across wide worksheets, and you can apply them without changing the values in your cells.

The table method is quickest and automatically includes new rows added to the table. Conditional formatting is useful when you want to keep the data as a regular range, color a specific selection, or control which row gets the first colored band. The steps below are for Excel for Microsoft 365 on Windows; notes explain relevant differences in Excel for Mac and Excel for the web.

What You’ll Need

An Excel workbook with the data you want to format. A single header row is helpful, but not essential.
A clear idea of which columns and rows belong in the formatted area. Avoid selecting unrelated notes or totals unless you want them included.
For the table method, be prepared for Excel to add filter buttons to the header row. You can turn those off if you do not need them.

Step-by-Step

Check and select your data. Click a cell inside the data block and check that the columns have useful headings and that there are no completely blank rows or columns breaking up the records. Excel can usually detect the connected range for you. If you want to format only part of a sheet, drag to select that range instead. Include the column headings if you plan to create a table; for conditional formatting, you will usually select only the data rows so the headings remain distinct.
Choose whether to use a table or conditional formatting. Use a table if the data is a list that may grow, such as an inventory, schedule, or contact list. Use conditional formatting if you need a banded look on a normal range, want a particular first-row pattern, or do not want table features such as structured references and filter buttons. Both methods color the cells visually; neither changes the underlying text, numbers, or formulas.
For the table method, create a table. With a cell in the data selected, choose Insert > Table. In the Create Table dialog, check that the range is correct. If your first row contains column names, select My table has headers; leave it unchecked if the first row is data and you want Excel to create generic headings. Choose OK. On Windows, Ctrl+T also opens the Create Table dialog. On Mac, use Insert > Table if the shortcut differs in your Excel setup.
Pick a table style with alternating bands. Click inside the new table to show the contextual Table Design tab, then choose a style from Table Styles. The style preview shows the colors and borders; select a light or high-contrast option that keeps text readable. If the table is already created but rows are not banded, open Table Design and make sure Banded Rows is checked in the Table Style Options group. The exact placement of the style gallery can vary with window size, but the tab name remains Table Design in Microsoft 365 for Windows.
Adjust table options if needed. To hide the filter arrows, clear Filter Button in Table Design. To change the colors, select another style in the Table Styles gallery. If a style has a distinctive first or last column, check First Column or Last Column in Table Style Options; these settings can make the ends of the table look different from the middle. A table style is the right choice when you want consistent formatting and expect to add records later.
For conditional formatting, select the data rows. As an example, suppose the headers are in row 1 and your data fills A2:F100. Select A2:F100, not the heading row. If your data begins elsewhere, select its complete rectangular range. The rule will apply only to cells in the selected range, so include every column that should receive a colored fill. You can extend the range later by editing the rule.
Create the alternating-row rule. Choose Home > Conditional Formatting > New Rule. Select Use a formula to determine which cells to format. For the example range A2:F100, enter =MOD(ROW()-ROW($A$2),2)=0. This colors the first selected data row, row 2, then every other row after it. Select Format, open the Fill tab, choose a color, and select OK. Select OK again to create the rule. If your first data cell is A5, use =MOD(ROW()-ROW($A$5),2)=0 instead, so the pattern begins with the first row of your selection.
Check the result and manage the rule. Confirm that the fill alternates down the range and spans the intended columns. To inspect or change the range, select a cell in it and choose Home > Conditional Formatting > Manage Rules. In Show formatting rules for, choose Current Selection if the rule is not visible. Edit the Applies to range if rows or columns were missed, and use Edit Rule to change the formula or fill. On Mac, the same Conditional Formatting commands are available from the Home tab, though the dialog layout may differ. Excel for the web also offers table styles and conditional formatting, but some rule-editing options may be arranged differently from the desktop app.

For the conditional-formatting examples, the formula uses the row number relative to the first data row, not a value in the worksheet. The dollar signs in $A$2 lock the reference to the starting cell as Excel evaluates the rule across the selected range. Change that anchor to the first cell of your actual data range; otherwise the color sequence can start on the wrong row. The formula does not need to be copied into worksheet cells: enter it only in the New Formatting Rule dialog.

Common Mistakes

Including the headings in the banding rule. If you apply the formula to A1:F100 while anchoring it at A2, Excel may color a heading or make the first band look inconsistent. Select the data rows alone, or write a separate rule for the heading.
Choosing an incomplete range. Selecting A2:D100 when the records extend through column F leaves the last two columns unformatted. Check the first and last columns before creating the rule.
Typing a formula for the wrong starting row. The anchor in the formula must match the first row of the selected data. A formula anchored at A2 will not start the pattern correctly if the selected rows begin at A5.
Expecting a table style to behave like a fixed range. A table applies its style to the whole table and can expand when you enter data directly below it. If you need only a small section colored, conditional formatting may be a better fit.
Using a fill color that is hard to read. Dark fills can reduce contrast with black text. Choose a light fill, or check the text color and readability before applying a stronger shade.

Tips

Use a subtle fill, such as a pale blue or gray, for long lists. The aim is to help the eye track a row without overpowering text, borders, or other highlights.
Tables are convenient for growing datasets. When you type or paste a new record directly below a table, Excel generally expands the table and carries its style into the new row. Check that the new record actually joined the table if the banding does not continue.
With conditional formatting, keep the rule’s Applies to range limited to the actual data. Applying it to entire columns can work, but it can make rule management less clear and may interact with other formatting.
If you need the first data row to remain uncolored and the second data row colored, use the opposite test: for a range starting at A2, enter =MOD(ROW()-ROW($A$2),2)=1. The 0 and 1 results switch which alternating rows receive the fill.
For a table, change the table style rather than manually filling individual rows. Manual fills can make the pattern look uneven and may be replaced or obscured when the table style is changed.

Troubleshooting

No rows change color: For conditional formatting, select a cell in the intended range and open Home > Conditional Formatting > Manage Rules. Check that the rule exists, that its Applies to range includes your data, and that the formula has a leading equals sign. Confirm that the Fill tab has a color selected. If the rule is set to a different worksheet or range, correct Applies to and apply the change.

Every row has the same color: Check that the formula uses ROW and MOD as shown, and that the anchor points to the first data row. Make sure the formula was entered in the formula-based rule option, not in a cell or in a different type of rule. If another conditional-formatting rule applies a fill to the same cells, open Manage Rules and review rule order and formatting. A competing rule can affect what you see.

The pattern starts on the wrong row: The result depends on the top row of the selected range and the formula’s anchor. For example, if data starts at row 5, select the data beginning on row 5 and anchor the formula at $A$5. The first row will then be colored with the formula ending in =0. Use =1 instead if you want that first row left uncolored.

New table rows are not banded: Enter the new record in the row immediately below the table, or press Tab from the last cell in the final table row to create another row. If the new cells are outside the table, use Table Design > Resize Table to include them. If the table is banded inconsistently, check that Banded Rows is enabled and reselect a table style.

Existing colors remain visible: A manual fill or another conditional-formatting rule may make the alternating pattern difficult to see. For a standard range, select the affected cells and inspect Home > Conditional Formatting > Manage Rules. Remove or adjust rules that conflict. To clear ordinary cell fills, use Home > Fill Color and choose No Fill, but first check whether those fills contain useful formatting you want to keep.

Frequently Asked Questions

Can I alternate row colors without turning the data into a table?
Yes. Select the data rows, then create a formula-based rule through Home > Conditional Formatting > New Rule. This changes the appearance while leaving the range as ordinary cells.

Will alternating colors continue when I add more data?
A table style generally continues when you add rows within or directly below the table. Conditional formatting continues only as far as its Applies to range reaches. Extend that range in Manage Rules when you add records beyond it.

Can I use two custom colors instead of one color and white?
Yes. Create two conditional-formatting rules for the same range, using one formula for each alternating group, and assign a different fill to each. For a range starting in row 2, use =MOD(ROW()-ROW($A$2),2)=0 for the first group and =MOD(ROW()-ROW($A$2),2)=1 for the second. Check rule order if other rules also format those cells.

Can I color every other column instead?
Yes. Select the intended range and use a formula-based conditional-formatting rule with COLUMN in place of ROW. The column anchor should refer to the first column in the selection, so the pattern begins where you expect.

Do alternating colors change the data or formulas?
No. Table styles and conditional formatting change how cells look, not their contents. You can still sort, filter, edit, and calculate with the data. A table may add filter controls and table behavior, but the banding itself does not alter cell values.

Which method should I choose?
Choose a table for an expanding list that benefits from built-in filters and table formatting. Choose conditional formatting for a fixed range, a custom starting pattern, or a worksheet where you want to avoid converting the data into a table.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.