How to Make Every Other Line a Color in Excel Quickly

To make every other line (row) a color in Excel, select your data rows, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. For data in A2:F100, use =MOD(ROW()-ROW($A$2),2)=0, choose a fill color, and click OK. Excel will shade alternating rows, starting with row 2. You can also use a formatted table for automatic banding.

Alternating row colors make wide lists easier to scan and help readers keep values aligned across a row. The instructions below are for Excel for Microsoft 365 on Windows. The same conditional-formatting approach works in recent Excel versions; Mac menu labels can differ slightly.

What You’ll Need

An Excel worksheet containing the rows you want to format.
A decision about whether the first data row or the second data row should receive the color.
A fill color that contrasts clearly with your text and any existing cell formatting.

You do not need an add-in or a special formula-filled helper column. Conditional formatting applies the color based on each row’s position, while a formatted Excel table can apply built-in banded-row styles.

Step-by-Step

Identify the range and the header. Find the first and last columns and rows of the data. If row 1 contains headings and the records run from A2 to F100, the data range is A2:F100. Leave the heading row out if you want only records to be striped. If there is no heading, begin with the first actual data row.
Select the cells to shade. Click the Name Box to the left of the formula bar, type the range (for example, A2:F100), and press Enter. This is often easier than dragging across a large sheet. Select all columns belonging to each record so the color forms a continuous band across the row. You can select more rows than currently contain data if you want to plan for additions, but do not include unrelated notes or totals unless you want them striped too.
Open the formula-based rule. On the ribbon, choose Home > Conditional Formatting > New Rule. In the New Formatting Rule window, select “Use a formula to determine which cells to format.” This option lets one rule shade a whole row of cells according to the row number, rather than checking whether individual cells contain a particular value.
Enter a formula that starts with the first data row. With the header in row 1 and data in A2:F100, enter =MOD(ROW()-ROW($A$2),2)=0 in the formula box. This evaluates as TRUE on the first selected row, row 2, then every second row after that: rows 2, 4, 6, and so on. The anchored A2 reference marks the first data row; the other part updates as the rule is evaluated down the selection. If your data starts on a different row, replace both instances of row 2 in the selection and formula with the actual first data row. For example, when data starts on row 5, select a range beginning in row 5 and use $A$5 in the formula.
Choose a fill color. Click Format in the rule window, open the Fill tab, and choose a light shade. Click OK to close Format, then OK again to create the rule. A pale fill usually keeps black text readable. If you want a different font color or border as well, set those options in the same Format dialog, but keep the design restrained so the data remains easy to read.
Check the result across the selection. Rows 2, 4, 6, and every other selected row should now be shaded. Look at the first and last columns, not just the cells containing text, to confirm the bands cover the full width of the records. If the shaded pattern starts on the wrong row, edit the rule or use the alternate formula choice described under Tips.
Review the rule’s range if needed. Choose Home > Conditional Formatting > Manage Rules. Set “Show formatting rules for” to This Worksheet if the rule is not visible, then check the Applies to range. It should cover the data columns and rows you intended, such as =$A$2:$F$100. Correct the range if you added or left out columns, then click Apply and OK.
Consider a table for a growing list. If the data is a simple rectangular list, select a cell in it and choose Home > Format as Table. Pick a style with alternating bands, confirm the range, and indicate whether the table has headers. On the Table Design tab, make sure Banded Rows is checked under Table Style Options. A table style is convenient when you regularly append records because the table typically expands with new data and carries its formatting along. If you do not want table features such as filter buttons or structured references, keep the conditional-formatting method instead.

On Mac, use the Home tab’s Conditional Formatting controls to create the same formula rule; the dialog wording is similar, though its layout can vary by Excel release. For a table, look for the Table Design tab after selecting a table. In Excel for the web, conditional formatting is also available from Home, but dialog options and rule management may be arranged differently. If a specific dialog option is unavailable in your version, the table-style method is a straightforward alternative.

Common Mistakes

Including the header accidentally. If you select A1:F100, your rule may color the heading or shift the pattern. Select only the record rows, such as A2:F100, when the header should stay distinct.
Using a formula for the wrong starting row. The example formula is written for a selection that starts in row 2. If the selected range begins at row 5 but the formula still refers to A2, the alternating pattern will not begin where expected. Match the anchored row in the formula to the first row of the selected range.
Selecting only one column. A rule applied to A2:A100 cannot color the rest of each record. Include every column that should share the band, for example A2:F100.
Using a fixed color fill on the cells instead of a rule. Manually coloring every second row takes longer and can become inconsistent when you insert, delete, or sort records. Conditional formatting recalculates the pattern from row positions.
Forgetting that other formatting can overlap. A second conditional-formatting rule, existing cell fill, or table style may make the intended color look different. Review Manage Rules and check which rules apply to the same range.

Tips

Choose which row gets the color. The example shades the first data row. To shade the second data row instead, keep the same selection but change the ending test from 0 to 1 in the formula. For a selection beginning in row 2, that makes rows 3, 5, 7, and so on the colored rows.
Keep the fill subtle. A light blue, gray, or other pale shade is usually easier to read than a saturated color. Check contrast with the text and avoid using color alone to communicate a status or category.
Use the whole record width. If your list spans A through H, select the corresponding data rows in all eight columns. This makes the band easy to follow across the sheet, including blank cells between populated fields.
Make the rule extend to expected additions. You can set the Applies to range to include spare rows, such as =$A$2:$F$500, if that range is genuinely part of the list. For frequent additions, a table with Banded Rows enabled is often easier to maintain.
Use table styles for simple data lists. Tables offer built-in banding and are convenient for sorting and filtering. Use conditional formatting when you need to keep the existing range as a normal range or want more control over which rows receive the fill.
Remember that row stripes are positional. When you sort the records, the color follows the displayed row position, not a particular record’s identity. This is normally what you want for readable banding. If the color must stay attached to specific records, use a separate rule based on a value in the record instead.

Troubleshooting

No rows change color: Open Home > Conditional Formatting > Manage Rules and confirm the rule applies to the selected range. Check that the formula starts with an equals sign and that its anchored row matches the top row in Applies to. Also verify that the Format dialog has a Fill color set; saving a rule without choosing a visible format produces no obvious change.

The pattern starts on the wrong row: Check whether the header was included and confirm the selection’s first row. With a data range starting in row 2, the example shades row 2 first. To begin on the next data row, change the formula’s final comparison from 0 to 1. Do not change the range and leave the formula anchored to its old starting row.

Only part of each row is colored: Edit the rule and expand Applies to across all the data columns. A range such as =$A$2:$A$100 colors only column A; use =$A$2:$F$100 to shade the full six-column record.

New rows are not striped: A conditional-formatting rule only covers its Applies to range. Extend that range in Manage Rules to include the new rows, or convert the list to a table and enable Banded Rows. If there are blank rows inside the selected range, those may also receive color, which is expected.

The fill looks different from the chosen color: Another conditional-formatting rule or table style may be affecting the same cells. Check Manage Rules for overlapping ranges and review the table’s style settings. If you want the table design to control the stripes, remove the conflicting rule or clear its formatting, taking care not to delete unrelated rules.

Frequently Asked Questions

Can I color every other row without using a formula?
Yes. Select the data and choose Home > Format as Table, then pick a style with banded rows and check Banded Rows on the Table Design tab. This is the simplest option when the data can be an Excel table.

Does “every other line” mean every other cell or every other row?
In a spreadsheet, a horizontal line of cells is a row. The steps here color alternating full rows. To color alternating columns instead, the selection and formula logic would need to be based on column positions.

Will the colors update if I sort the data?
Yes. Conditional formatting evaluates the current row positions, so the alternating pattern is recalculated after a sort. The colored band is not permanently attached to one record. Table banding also provides alternating visual rows as part of the table style.

Can I use two alternating colors?
Yes. Create two conditional-formatting rules with different fill colors and formulas that distinguish odd and even positions, or choose a table style that alternates bands. For a simple readable sheet, one tinted row between unfilled rows is usually sufficient.

Will the formatting appear when I print the worksheet?
Cell fill colors generally print when the workbook is printed, provided the printer and print settings support color. Use Print Preview to check the result. If you print in grayscale, choose a fill light enough that text remains clear and rows can still be distinguished.

Can I remove the stripes later?
For conditional formatting, select the affected range and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. For a table, select a different style or turn off Banded Rows in Table Design. Clearing rules removes conditional formatting, so check the selected range first.

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.