The quickest way to shade alternating rows in Excel is to turn your data into a table: select a cell in the data, choose Insert > Table, confirm whether it has headers, and pick a banded table style. If you want to keep an ordinary range instead, use Home > Conditional Formatting and apply a formula rule; both approaches create alternating shading without coloring rows one at a time.
The fastest option: use a table
For a typical list of records, an Excel table is usually the easiest choice. Its built-in banded-row styles apply alternating fills automatically, and the formatting can extend when you add records to the table. Tables also offer useful features such as filter buttons and structured references in formulas, so they are often a good fit for data that will be sorted, filtered, or expanded.
- Click any cell inside the data you want to format.
- On the ribbon, choose Insert > Table. In Windows Excel for Microsoft 365, Ctrl+T is also a shortcut for creating a table.
- Check that the proposed range covers the intended data. Select My table has headers if the first row contains column names, then select OK.
- Choose a style with alternating row colors from the table styles on the Table Design tab.
- If the style is not banded, select a table cell and check Table Design > Table Style Options > Banded Rows.
Excel for Mac also provides Insert > Table; menu names and ribbon layout can vary slightly with the version. In Excel for the web, use Insert > Table. The table style is the key: the Table Design tab appears when a table cell is selected, and its styles determine the colors and borders. A header row may have a different fill from the alternating body rows, which is normal.
Apply a table style without changing the data
Creating a table does not replace the values in your range, but it does add table behavior. Excel may add filter arrows to the headers, and formulas that refer to table columns may use structured references rather than ordinary cell addresses. If those changes are unsuitable—for example, the range is part of a layout rather than a dataset—conditional formatting is a better way to add stripes while keeping the range as it is.
To change a table’s appearance later, select any cell in it, open Table Design, and choose another style. You can also turn off Banded Rows if you want a solid fill, or turn off Header Row if the table does not need a distinct header. The precise options displayed depend on the table and Excel version. Changing the style affects presentation, not the underlying cell values.
Tables are especially convenient when the list will grow. Typing or pasting directly into the first row below a table commonly expands it, carrying the style into the new row. Expansion is not guaranteed in every workbook arrangement: a blank row or column, nearby content, or a paste that does not align with the table can affect what Excel includes. Check the table boundary after adding data, particularly before sharing or printing.
Shade alternate rows with conditional formatting
Conditional formatting is the flexible alternative. It evaluates a rule for each cell in the selected range and applies a fill when the rule is true. For example, if the data occupies A2:F100 and row 2 is the first data row, this formula shades row 2, then every second row after it:
With data in A2:F100 and the intended first shaded row at 2, use =MOD(ROW()-ROW($A$2),2)=0.
- Select the full range to stripe, such as A2:F100. Select the data cells, not the header, unless you also want the header governed by the rule.
- Choose Home > Conditional Formatting > New Rule.
- Select Use a formula to determine which cells to format.
- Enter
=MOD(ROW()-ROW($A$2),2)=0. The reference $A$2 identifies the first row of the selected data range. - Select Format, open the Fill tab, choose a light color, and confirm with OK. Confirm again to create the rule.
The rule shades every cell across each qualifying row within the selected range. If you want row 3 to be the first shaded row instead, change the final comparison to =MOD(ROW()-ROW($A$2),2)=1. The column letter in the anchor is not important to the row calculation, but anchoring it as $A$2 makes the starting row clear. When your range begins on a different row, replace 2 in the anchor with that range’s first row number.
How the alternating-row formula works
ROW() returns the worksheet row number of the cell Excel is evaluating. MOD divides a number and returns its remainder. As Excel checks successive rows, the remainder after division by 2 alternates between 0 and 1. Comparing that remainder with 0 makes the rule true for one row, false for the next, and true again after that.
Subtracting ROW($A$2) makes the pattern relative to the top of the selected data rather than to the worksheet’s row numbering. That matters when a list begins on an odd-numbered row: a rule based only on even worksheet rows might shade the second data row instead of the first. For a range beginning in row 2, =MOD(ROW(),2)=0 also shades the first data row, but the anchored version makes the intended starting point easier to preserve if the range is moved.
Conditional-formatting formulas are entered as formulas, beginning with an equals sign. Excel adjusts relative references as it evaluates the rule through the selected range. The absolute row in $A$2 stays fixed, while ROW() evaluates the current row. Avoid putting a dollar sign before the row in ROW(); a fixed row calculation would prevent the pattern from alternating.
Keep empty rows from being shaded
If your selected range includes spare rows for future entries, basic striping colors those rows too. You can make the rule depend on a column that is filled for every real record. For example, if column A always contains a name or ID, apply this formula to A2:F100:
With records in A2:F100 and a required value in column A, use =AND($A2<>"",MOD(ROW()-ROW($A$2),2)=0).
AND requires both conditions to be true: column A is not blank, and the row is one of the alternating rows. The reference $A2 fixes the check to column A but lets the row change as Excel evaluates the rule. Choose a field that is reliably present; if names can be blank but IDs cannot, check the ID column instead. If a cell contains a formula that displays an empty string, the comparison to an empty string generally treats that displayed result as blank for this purpose.
This conditional-formatting method can be useful when the range should remain a normal range but needs a larger prepared area. It does not automatically guarantee that the rule covers rows added outside the rule’s applied range. To inspect or extend that range, use Home > Conditional Formatting > Manage Rules, select the rule, and update its Applies to range. If you frequently add records, a table may require less maintenance.
Choose a readable fill and keep it consistent
Alternating fills are meant to help the eye track across wide tables, not compete with the data. A pale tint usually keeps black text readable and leaves stronger colors available for warnings or important categories. If the sheet already uses color to represent status, avoid choosing a stripe color that could be mistaken for one of those meanings. Check the result at normal zoom and in print preview, especially when the range contains narrow columns or long text.
Excel’s table styles may include header formatting, borders, and different shades. Conditional formatting lets you choose a fill yourself, and you can edit the rule later through Home > Conditional Formatting > Manage Rules. Use a single alternating-row rule for simple banding. Adding multiple overlapping rules can make the final color harder to predict, since rule priority and settings such as Stop If True can affect which formats appear.
Shading is only a visual cue; it does not change values, sort order, or the meaning of a record. Do not rely on alternating color alone to communicate information that readers need to understand. Clear headers, adequate contrast, and meaningful labels make a spreadsheet easier to use for people who cannot distinguish the fill colors.
Common problems and practical fixes
- The first row is not shaded. The rule may use worksheet row parity rather than the first row of your selected range. Use the anchored formula and make sure its row number matches the top data row.
- Only one column changes color. The rule may have been created while only one column was selected. Open Manage Rules and extend Applies to across the full data range, such as =$A$2:$F$100.
- The header is striped. Start the selected range at the first data row, not the header. For a table, use the table’s Header Row option and banded style rather than applying the rule to the whole sheet.
- Blank rows are colored. Limit the rule’s Applies to range or use the AND formula with a column that must contain a value.
- Colors appear to vanish. A table style, manually applied fill, or another conditional-formatting rule may be interacting with the format. Check whether the range is a table and review its style and rules in Manage Rules.
- New rows have no stripes. Extend the conditional-formatting range, or use a table if the data is an expanding list. A rule only applies where its Applies to range says it does.
When editing a rule, pay attention to the range shown in Applies to. This is separate from the formula: the formula decides which rows meet the condition, while Applies to decides which cells can receive the fill. A correct formula applied to only A2:A100 will not color columns B through F.
Which method should you use?
Choose a table when the data is a conventional list with headers and you expect to sort, filter, or add records. Its banded rows are quick to set up and are designed to travel with table data. Choose conditional formatting when you need to preserve a normal range, control the exact fill, include only selected columns, or base the shading on a rule. For a one-off small range, either method is straightforward; the important distinction is whether table features are useful in the workbook.
Neither method is a substitute for manually filling rows if each row needs a different, intentional color. Also, if the goal is to emphasize a property—such as overdue items—use a condition based on that property rather than simply alternating rows. A stripe rule is positional: it marks every other row in the current range, not a category or status. After sorting or rearranging data, check the visual result and confirm it still serves the intended purpose.
Frequently Asked Questions
Can I shade every other row without turning the range into a table?
Yes. Select the range, then create a formula rule through Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Use a formula anchored to the range’s first row, such as =MOD(ROW()-ROW($A$2),2)=0 for a range starting in row 2.
How do I make the first data row shaded?
Use a formula whose remainder test is zero relative to the first data row. For a range beginning in row 2, =MOD(ROW()-ROW($A$2),2)=0 shades row 2 first. If the formula shades the next row instead, check that the anchor row matches the first row of the selected range.
Will alternating colors update when I add rows?
A table style usually extends as the table expands. A conditional-formatting rule only affects its Applies to range, so it may need to be extended when new rows fall outside that range. Check the range in Manage Rules after adding data.
Can I shade every other column instead?
Yes. In a conditional-formatting formula, use COLUMN() in place of ROW() and anchor the first column of the selected range. For example, with a range beginning in column A, =MOD(COLUMN()-COLUMN($A$1),2)=0 marks the first column and every second column. Apply the rule to the full range you want formatted.
Does row shading change when I sort the data?
Table banding is intended to follow the table’s row positions as the table is sorted. A conditional-formatting rule based on ROW() also evaluates the current worksheet row position, so the stripes are positional rather than tied to a particular record. If color represents a record’s status, base the rule on the status value instead.
Related
- Excel Number Format: How It Works and When to Use It
- Excel Number Format: Meaning, Examples, and How It Works
- Excel Number Format Arrow: What It Does and How to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Outlook Export Contacts: CSV Steps and Common FixesTo export Outlook contacts, open People and choose Manage contacts > Export contacts in new Outlook or Outlook…
- Can You Hide a Column in a Power BI Table Visual?Not with a dedicated hide-column switch in a Power BI table visual. To make a column disappear, remove…
- Excel Shortcut to Select All Data: What Ctrl+A SelectsIn Excel for Microsoft 365 on Windows, click a cell inside your data and press Ctrl+A to select…
- How to Set an Out of Office Reply in Outlook WebTo set an out-of-office reply in Outlook on the web, open Settings, find Automatic replies, turn them on,…
- How to Enable Strikethrough in Excel with ShortcutsTo enable strikethrough in Excel for Microsoft 365 on Windows, select the cell or cells and press Ctrl+5.…
- Google Sheets If Zero Then Blank: Formulas That WorkYes. To show a blank instead of a zero in Google Sheets, use an IF formula such as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.