To shade every other row in Microsoft Excel, select the range, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =MOD(ROW()-ROW($A$2),2)=0, choose a fill color, and confirm. For a list that will grow or be sorted, formatting it as an Excel table is often simpler because table styles provide alternating row bands automatically.
Choose a method for your worksheet
Excel offers several ways to create alternating row shading, and the best one depends on how your data is used. A table style is convenient for a changing dataset. Conditional formatting gives you precise control over which rows receive color and works on an ordinary cell range. Manual fills can be enough for a small, fixed layout, but they need maintenance if rows are added or rearranged.
Before applying a format, decide whether the color should follow the row’s position or identify a particular record. Alternating bands usually mark position: the first data row is shaded, the next is not, and the pattern repeats. If a row should remain colored because it represents a particular category or status, use a condition based on that value instead of a simple every-other-row pattern.
- Use a table for sortable, filterable lists that may expand.
- Use conditional formatting for a normal range, a custom starting row, or a pattern you want to control.
- Use manual fill for a small layout that is unlikely to change.
Apply alternating bands with Format as Table
For many lists, an Excel table is the most straightforward option. Click a cell in the data, choose Insert > Table, check that the proposed range is correct, and confirm whether the data has headers. You can also select the range first. Excel adds table formatting, and most table styles use alternating bands by default.
To change the appearance, click inside the table and open the Table Design tab. Choose a style from the Table Styles gallery. In Table Style Options, make sure Banded Rows is selected. You can choose a lighter or darker style, or apply a custom table style if the built-in colors do not suit the workbook.
Tables offer more than stripes. They make it easier to sort and filter, can expand when you enter data directly below them, and use column names in formulas. Those features are useful for ongoing lists such as inventory, schedules, or sales records. A table is not always appropriate, though: it changes how a range behaves, and some layouts with subtotals, merged cells, or multiple header rows may need to remain ordinary ranges.
To remove the banding without converting the table back to a range, choose a plain style or clear Banded Rows under Table Style Options. To remove the table itself, use Table Design > Convert to Range; the data remains, but table features no longer apply.
Use conditional formatting on a regular range
Conditional formatting is a flexible choice when you do not want to create a table. The example below assumes your headers are in row 1 and the data occupies A2:G100. Select A2:G100, then go to Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format, enter the formula below, and select Format. On the Fill tab, choose a color and confirm the dialogs.
For that selection, use =MOD(ROW()-ROW($A$2),2)=0. The first selected data row, row 2, is shaded; row 3 is not; row 4 is shaded, and so on. The formula is evaluated for each cell in the selected range, while the relative row reference changes as Excel applies the rule down the selection. The dollar signs keep the starting cell’s reference fixed.
If you want row 3, rather than row 2, to be the first shaded row, change the final test to =MOD(ROW()-ROW($A$2),2)=1. The key is to select the full area you want formatted before creating the rule. If you select only column A, only column A will be shaded, even if the formula correctly identifies alternating rows.
After creating a rule, check its range in Home > Conditional Formatting > Manage Rules. The Applies to box shows which cells receive the formatting. If you later add rows beyond the original selection, expand that range or create the rule for a larger area. When you insert rows within the formatted range, Excel generally adjusts the rule, but it is worth checking after substantial worksheet changes.
Understand the alternating-row formula
The formula uses ROW to return a row number and MOD to find the remainder after division. MOD produces 0 for even multiples of 2 and 1 for the intervening rows. The comparison to 0 therefore returns TRUE for every second row. Conditional formatting applies the chosen style when the formula evaluates to TRUE.
Subtracting ROW($A$2) makes the pattern relative to the beginning of the selected data rather than to the worksheet’s absolute row numbers. That detail matters when the data starts well below the top of the sheet. For example, if the data starts in row 7, select the data range beginning in row 7 and use =MOD(ROW()-ROW($A$7),2)=0 to shade that first row. If you instead use a formula based on ROW() alone, the first shaded row depends on whether its worksheet row number is odd or even.
The reference column in the starting cell is not important for this row-based pattern; the row number is what matters. The reference must, however, identify the first row of the range you selected. If a formula rule appears to start with the wrong band, check both the first row of the selection and the row number used in the formula.
Customize the color and the area it covers
Alternating rows are often used to make wide tables easier to scan. A subtle light fill usually keeps text readable while separating neighboring records. In the Format Cells dialog, choose a fill color with adequate contrast against the text and any existing borders. Conditional formatting changes the fill only where its rule applies; it does not automatically add borders or change the font.
You can apply the same rule across all columns in a record. For example, selecting A2:G100 colors the complete row segment from column A through G. If new columns are added later, they will not necessarily be included in the original Applies to range, so update the range if the shading should cover them too.
For a different visual pattern, create an additional rule or use another table style. Be careful when rules overlap: another conditional-formatting rule may also set a fill, and rule order or the Stop If True option can affect which format is visible. If the result is unexpected, inspect the rules for the selected cells in Manage Rules rather than adding more rules blindly.
What happens when you sort, filter, or add rows?
A table is usually the easiest choice when people will sort or filter a list. Its banded-row style is part of the table formatting, and sorting the records keeps the table presentation coherent. With conditional formatting based on worksheet row numbers, the shading is recalculated according to the rows’ current positions. That often produces the desired alternating pattern after a sort, but it means the color marks position, not a permanent identity attached to a particular record.
Filtering hides rows; it does not renumber the remaining worksheet rows. As a result, a conditional-formatting rule based on ROW can leave two visible records with the same shade if a row between them is hidden. If uninterrupted alternating colors among the visible records are essential, try a table style and check the result with the filters your readers will use. Do not assume that a simple row-number rule counts only visible records.
When adding new data, enter it directly below a table so Excel can extend the table, or update a conditional-formatting rule’s Applies to range. Pasting cells with their own formats can also introduce unexpected colors or extra rules. If a stripe pattern breaks after pasting, review the formatting rules and the selection range before rebuilding the worksheet.
Common problems and practical fixes
- Every row is the same color: Confirm that the rule formula is being applied to the intended range and that the formula compares MOD with either 0 or 1. A formula that is true for every row will color the entire range.
- Only one column changes: The Applies to range probably covers only that column. Extend it across the columns that make up each record.
- The first band is the wrong color: Match the row number in the formula’s starting reference to the first row in the selected data. Switch the final comparison between 0 and 1 to reverse the starting shade.
- Some cells keep a different fill: Check for overlapping conditional-formatting rules, a table style, or manually applied formatting. Use Manage Rules to inspect rules for the current selection.
- New rows are unshaded: Extend the Applies to range, or use a table that can expand with the data.
Remember that cell shading is a visual aid, not a data value. A shaded band does not mean the record is selected, complete, or part of a special category unless you explicitly define that meaning for the people using the workbook.
Manual shading and other alternatives
For a short, unchanging range, you can select every other row while holding Ctrl on Windows, then choose a fill color from Home > Fill Color. On Mac, use Command while selecting separate rows. This method is quick for a one-off layout, but it is easy to miss a row, and the pattern will not reliably adjust when you insert, delete, or sort records. Manual fill is therefore less suitable for a list that changes frequently.
Another option is to use a built-in table style even if you only want the visual bands: create the table, choose a style, and turn off options you do not need. If you are using Excel for the web, table styles and conditional formatting are available, although the placement or presentation of commands can differ from the Windows desktop ribbon. Excel for Mac also provides table styles and conditional formatting; menu and ribbon layouts can vary slightly by release. The formula logic is the same.
Frequently Asked Questions
Can Excel automatically shade every other row?
Yes. Format the list as a table and use a banded table style, or create a conditional-formatting rule using a formula such as =MOD(ROW()-ROW($A$2),2)=0. A table is convenient when the list grows; conditional formatting offers more control over an ordinary range.
How do I shade every other row without converting my data to a table?
Select the range and create a formula-based rule through Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format, enter the row formula, then choose the fill color. Confirm that Applies to covers every column you want shaded.
Can I start the shading on the second data row?
Yes. If the data begins in A2, use =MOD(ROW()-ROW($A$2),2)=1 to leave row 2 unshaded and shade row 3. The final 0 or 1 controls which of the two alternating groups receives the fill.
Will alternating shading continue when I add rows?
A table usually extends its formatting when you add data directly below it. With conditional formatting, new rows are shaded only if they fall inside the rule’s Applies to range. Check or extend that range if the worksheet grows beyond it.
Why do two visible rows have the same color after filtering?
A ROW-based conditional-formatting rule uses worksheet row numbers, not a count of visible records. Hidden rows still occupy row numbers, so the visible sequence may not alternate perfectly. A table style may suit filtered lists better; check the appearance with your particular filters.
Related
- Excel Unmerge Shortcut: How to Unmerge Cells on Windows
- Excel Undo Shortcut: How to Reverse Changes Safely
- Excel Subscript Shortcut: Apply Subscript to Cell Text
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Monthly Budget vs Actual Excel Template: Free SetupYes—you can make a free monthly budget-versus-actual template in Excel with columns for category, budget, actual spending, variance,…
- Excel Reference Table in a Dropdown ListYes. In Excel for Microsoft 365 on Windows, turn the source data into an Excel Table, create a…
- How to Create a Template in Outlook 2016To create an email template in Outlook 2016 for Windows, compose a message, choose File > Save As,…
- Dependent Dropdowns in Google Sheets: Setup and FixesTo make a dependent dropdown in Google Sheets, create a named range for each group of choices, then…
- Google Sheets Conditional Formatting for Full RowsYes. In Google Sheets, you can use conditional formatting to highlight an entire row when a cell in…
- Excel Heat Map: How to Create and Customize OneExcel does not have a worksheet function named HEATMAP; the usual way to make a heat map is…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.