The quickest way to shade alternating rows in Excel is to select your data, choose Home > Format as Table, and pick a style with banded rows. If you need to keep the data as a plain range, use conditional formatting with a row-based formula instead.
The fastest method: Format as Table
For most lists, an Excel table is the simplest option: its banded-row appearance is built in, and the stripes continue when you add records to the table. In Excel for Microsoft 365 on Windows, follow these steps:
- Select a cell in your data, or select the full range you want to format.
- Choose Home > Format as Table and select a style with alternating row shading.
- In the dialog box, check that the displayed range is correct. Select My table has headers if the first row contains column names, then choose OK.
Excel turns the range into a table, not just a colored block of cells. Tables normally include filter controls in the header and can expand when you type directly below them or add a row from within the table. That makes this method useful for lists that will grow or be sorted and filtered. If you only want stripes and do not want table behavior, use conditional formatting instead.
After creating the table, click inside it to show the Table Design tab. The Banded Rows option there controls whether alternating row shading is shown; the available table styles control its colors and contrast. On Excel for Mac, the table feature is available too, though the ribbon layout can differ. In Excel for the web, use the Home tab’s Format as Table command; some table-design controls may be arranged differently from the Windows desktop ribbon.
What banded rows do
Banded rows use alternating background fills to make it easier to follow a record across a wide sheet. The effect is sometimes called zebra striping. It does not change cell values, formulas, or the order of your data; it is a visual formatting choice. A light tint is usually enough to distinguish one row from the next without competing with the text.
Excel table styles can apply more than row shading. Depending on the style, they may also emphasize the header, first or last column, or total row. If you want only the alternating fill, choose a restrained style and turn off other options such as First Column or Total Row on Table Design if they are enabled. The table’s filter buttons are separate from its banded appearance.
Use conditional formatting for a plain range
Conditional formatting is the better choice when you do not want to convert a range into a table, or when you want to control exactly which rows are shaded. Select the range first, then choose Home > Conditional Formatting > New Rule. In the New Formatting Rule window, choose Use a formula to determine which cells to format. For a range from A2 to F100, enter this formula:
With the range A2:F100 selected and its first row at worksheet row 2, use this conditional-formatting formula: =MOD(ROW(),2)=0
Choose Format, open the Fill tab, select a color, and confirm the dialogs. The rule colors even-numbered worksheet rows within the selected range: rows 2, 4, 6, and so on. Because the rule is applied to all six columns, the fill runs across each selected row. To shade the other set of rows instead, change the comparison from 0 to 1.
The formula is evaluated for each cell in the applied range, but ROW() returns the worksheet row number, so cells on the same row receive the same result. MOD divides that number by 2 and returns the remainder. Even row numbers have a remainder of 0; odd row numbers have a remainder of 1. The equals test returns TRUE for the rows that should receive the format.
Make the first selected row the shaded row
Worksheet parity is not always the same as range parity. For example, if your data begins at row 3, the formula above shades row 4 first, leaving the first data row unshaded. That is not an error; it simply starts with even worksheet rows. If you want the first row of the selected range shaded regardless of where it begins, base the pattern on the range’s first row.
With A3:F100 selected and A3 as the first row of the range, use this formula in the conditional-formatting rule: =MOD(ROW()-ROW($A$3),2)=0
Here the first selected row produces a difference of zero, the next a difference of one, and the pattern repeats. If you want the second row shaded instead, change the final 0 to 1. Keep the reference to the anchor row fixed with dollar signs, and make sure the anchor matches the first row of the range to which the rule applies. If the range starts at row 8, replace A3 with A8.
Choose between a table and conditional formatting
Both approaches can produce alternating stripes, but they suit different jobs. A table is convenient when the data is a coherent list with headings and may grow. Its style travels with the table as you add rows, and built-in sorting and filtering are ready to use. Conditional formatting is more flexible for a fixed report, a custom layout, or a range that should remain an ordinary cell range.
- Choose Format as Table for a growing inventory, contact list, or transaction log where table features are useful.
- Choose conditional formatting when you want stripes on a specific range without adding table behavior, or when you need a custom start row or fill color.
- Choose manual fill only for a tiny, stable block that will not be sorted, edited, or extended. Manual coloring is less reliable for a changing list.
You can use table styles and conditional formatting in the same workbook, but avoid applying competing fill rules to the same cells unless you have a reason. If a table style and a conditional-formatting rule both affect a cell, the visible result can depend on the rule and formatting precedence. A single method is easier to manage.
Adjust the color and coverage
For a table, click inside it and select a different option in Table Design > Table Styles. If the style gallery does not show enough choices, expand the gallery to browse more. If the rows no longer alternate, check that Table Design > Banded Rows is turned on. The same tab may also contain options for header, total, or emphasized columns.
For conditional formatting, the selected range determines the coverage. Selecting A2:F100 shades only those six columns; it will not automatically extend across a note in column G. To cover a wider record, select the full intended width before creating the rule. You can later inspect or revise rules through Home > Conditional Formatting > Manage Rules. In the rules window, check the Applies to range as well as the formula, especially if the pattern stops partway down the sheet.
Use a subtle fill when the sheet already uses color to communicate status, priority, or category. Alternating row bands should support reading, not replace meaningful labels or color keys. If the workbook will be printed, check Print Preview because a very pale fill may be less apparent on paper, while a dark fill can reduce text contrast.
Common problems and fixes
- The first row is not shaded. With a rule using ROW(), this depends on whether the starting worksheet row is odd or even. Use the range-relative formula above to make the first selected row shaded, or reverse the comparison.
- Only part of each row changes color. The rule’s Applies to range probably covers only some columns. Expand it to the full width of the records.
- The stripes stop at a particular row. Check whether the conditional-formatting range ends there. For a table, confirm that new entries are being added as part of the table rather than in a disconnected area.
- Every row looks the same. In a table, turn on Banded Rows and check the selected style. For conditional formatting, confirm that the formula is a formula rule and that the fill was selected in Format.
- Shading appears to conflict with other colors. Look for existing conditional-formatting rules or manually applied fills. Review Manage Rules and remove or adjust overlapping rules as appropriate.
When troubleshooting, remember that changing a cell’s value does not affect a ROW()-based stripe. The rule depends on the cell’s row position, not its content. If you insert, delete, or rearrange rows, Excel reevaluates the row-based rule, so the alternating pattern follows the worksheet positions.
Sorting, filtering, and hidden rows
Tables are designed for sorting and filtering, which is one reason they are practical for data lists. Conditional formatting based on ROW() also recalculates as rows move, but its alternating pattern follows worksheet row numbers. When a filter hides records, the remaining visible rows may not appear as a perfectly uninterrupted sequence of alternating bands, because hidden worksheet rows still have row numbers. If uninterrupted visible-row striping is essential, test the behavior with the actual filter setup before relying on it; a standard parity rule does not count only visible records.
For a grouped report with blank rows between sections, a simple row-parity rule counts those blank rows too. As a result, the first data row after a blank can have the opposite shade from the preceding data row. A table works best when the data is one continuous rectangular list, while custom conditional formatting may be more suitable for reports with headings, breaks, or special layout requirements.
Frequently Asked Questions
How do I shade every other row in Excel without making a table?
Select the desired range and create a formula-based rule through Home > Conditional Formatting > New Rule. Use a ROW()-based formula such as =MOD(ROW(),2)=0, then choose the fill color. The table-relative start-row example above lets you make the first row of your range the shaded one.
Can I shade every other column instead?
Yes. Select the target range and use a conditional-formatting formula based on COLUMN() rather than ROW(). The same MOD idea tests whether the worksheet column number is even or odd. This is useful for alternating columns in a matrix, but it is different from shading every other row.
Will the shading update when I add rows?
A table style normally carries banding into rows added to the table. A conditional-formatting rule updates only within its Applies to range, unless Excel expands that range as you add data in a way it recognizes. Check Manage Rules if newly added rows are not formatted.
Can I alternate two custom colors?
A standard table style can provide alternating row bands, and you can choose a different style from the gallery. For a custom two-color pattern on a plain range, create two conditional-formatting rules with opposite even/odd tests and assign a separate fill to each. Ensure both rules cover the same intended range.
Does banded-row shading change when I sort the data?
It does not alter the data itself. A table’s banded style remains attached to the table’s rows as the records are sorted. A ROW()-based conditional-formatting rule shades worksheet row positions, so the visible colors are recalculated according to where records land after the sort.
Related
- Notepad to Excel Conversion: Steps and Common Fixes
- New Employee Training Schedule Template in Excel
- New Hire Checklist Template in Excel: What to Include
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Validation List Search: Setup and Fixes GuideIn current, updated Excel for Microsoft 365, a data-validation dropdown can filter its choices as you type, making…
- How to Rank in Excel with RANK.EQTo rank numbers in Excel, enter =RANK.EQ(B2,$B$2:$B$6,0) in a cell beside the first number, then fill the formula…
- Excel Dropdown List: How to Create and Use OneTo create and use a dropdown list in Excel for Microsoft 365 on Windows, select the cells, choose…
- Excel Mac: Change the Default Date Format for New WorkbooksTo change Excel’s default date format on a Mac, first adjust the date format in macOS under System…
- How to Write Superscript in Excel: Shortcut and StepsTo write superscript in Excel for Microsoft 365 on Windows, select the character or text you want to…
- Excel Default Zoom: How to Set a View That Sticks Each TimeTo change the default zoom for a worksheet in Excel for Microsoft 365 on Windows, open the sheet,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.