To highlight every other row in Excel for Windows, select your data, choose Home > Format as Table, and pick a style with alternating row colors. For shading without converting your data to a table, use conditional formatting with the formula =MOD(ROW(),2)=0.
A banded table is useful when you keep adding records because its shading extends to new table rows. Conditional formatting gives you control over which rows receive a color. The desktop steps cover Excel for Microsoft 365 and Excel 2024, with separate instructions for the web and mobile apps.
The short version
- Fastest option: Apply a table style with alternating colors and enable Banded Rows.
- Keep a regular range: Create a conditional formatting rule using
=MOD(ROW(),2)=0to shade even worksheet rows. - Choose the starting row: Use an offset formula when the pattern should begin at the first row of your selected data.
- Small selections: Manual fills work for a few rows but do not establish an automatic banding rule.
| Method | Best use | Main catch |
|---|---|---|
| Banded table style | Data that grows with new records | Converts the selection to a table |
| Conditional formatting | Automatic shading in a regular range | The formula controls where stripes begin |
| Manual fill | A small, fixed selection | Each alternate row needs formatting |
| Copy existing formatting | Reusing a shaded row pattern | Manual fills remain static when copied |
Apply alternating colors with a table
Select the data range you want to shade. In Excel for Windows, open Home > Format as Table and choose a style with alternating row colors. If your selection includes column headings, check My table has headers, then select OK.
You can also create the table through Insert > Table in Windows or Excel for the web. Check My table has headers when appropriate and select OK. Choose an alternating-color style under Table Design > Table Styles.
On a Mac, select the range and choose Insert > Table. Check My table has headers if your selection includes headings, select OK, then choose a banded style on the Table tab.
For a keyboard shortcut, select the range and press Ctrl + T on Windows or Command + T on Mac. Complete the table dialog, identifying existing headers where appropriate, and choose a banded style if the default does not show visible stripes. These shortcuts assume a US keyboard layout.
Fix table stripes that disappear or stop expanding
If an existing table has no visible stripes, click a cell inside it. On Windows or the web, open Table Design > Table Style Options; on Mac, use Table > Table Style Options. Check Banded Rows and choose a style with contrasting row fills.
If the stripes are still hard to distinguish, choose a different table style with visibly different alternating fills. Uncheck Banded Rows when you want to turn the alternating shading off.
Table banding automatically extends when rows are added to the table. If you need to insert a row in Excel, keeping the data as a table lets its banding continue as the table grows.
On Windows, if typing directly below the table does not extend it, open File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type. Check Include new rows and columns in table, then select OK to close the dialogs. Enter the next record directly below the table.
Use conditional formatting without a table
Select the cells you want to shade, leaving out any header row that should stay unshaded. In Excel for Windows, choose Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format. Enter this formula:
=MOD(ROW(),2)=0Choose Format > Fill, pick a color, and select OK in both dialogs. The rule shades even worksheet rows, such as rows 2, 4, and 6, within your selection. To apply the rule across the whole worksheet, use Select All before creating it.
For Excel on Mac, choose Home > Conditional Formatting > New Rule. Set Style to Classic, choose Use a formula to determine which cells to format, and enter the same formula. Under Format with, choose Customised format, open Fill, set Background color, and select OK until the dialogs close.
Choose which row gets the first color
The basic formula follows worksheet row numbers, rather than counting from the top of your selection. If you want odd worksheet rows shaded instead, replace it with the following formula, then finish the rule using your platform’s fill controls:
=MOD(ROW(),2)=1When your data begins farther down the sheet, an offset formula lets the pattern start at the first selected row. For a range beginning at A5, create a formula rule using the following expression. Replace $A$5 with the first cell of your actual range:
=ISODD(ROW()-ROW($A$5)+1)To use two alternating colors based on worksheet row numbers, create two rules with different fills. Use =ISODD(ROW()) for odd rows and =ISEVEN(ROW()) for even rows. If Excel requires a different local formula separator, replace the commas in formulas that contain them.
Keep stripes alternating across visible rows
Ordinary rules based on ROW() include hidden rows in their pattern. For Excel on Windows or Mac, a running count with SUBTOTAL can alternate only the visible rows. This requires an identifier column with a nonblank cell in every data row.
For data in A2:F100 with identifiers throughout A2:A100, select A2:F100 and create a formula-based conditional formatting rule. Enter the following formula, choose a fill, and finish the rule using the Windows or Mac controls described above. Its running count excludes both filtered-out and manually hidden rows:
=MOD(SUBTOTAL(103,$A$2:$A2),2)=1Adjust the selected range and column references to match your data. Keep the starting identifier reference fixed, as in $A$2, while the ending row reference changes, as in $A2. If the pattern stops alternating between visible records, check the identifier column for blanks, since each visible record needs to add one to the count.
Highlight alternate rows on a phone or tablet
In the current Excel mobile apps with workbook editing available, select your data range. On iPhone, open Show ribbon; on an Android phone, open More options. Open the tab menu and choose Insert > Table, then enable Table has headers if your first row contains headings.
Select a cell in the new table, reopen the tab menu, and choose Table > Table Styles. Pick a style with alternating row colors. On iPad or an Android tablet with the ribbon visible, begin directly at Insert > Table.
For a few rows on iPhone or an Android phone, you can also select the cells across one row and open Fill color on the toolbar. Choose a color, then repeat for every other row. These individual fills do not create an automatic banding rule.
Apply or copy a static row pattern
On Windows, hold Ctrl while selecting row numbers 2, 4, 6, and so on. Select only the corresponding cells instead if you want the shading limited to your data columns. Choose Home > Fill Color and pick a color.
In Excel for the web, select each desired row range and apply its fill through Home > Fill Color, repeating as needed. On Mac, select the cells in one row and choose Format > Cells > Fill. Pick Background color, select OK, and repeat for every other row.
To reuse existing row formatting on Windows or Mac, select the formatted cells, choose Home > Format Painter, then drag across the corresponding cells in another alternate row. Repeat as needed, or copy a shaded row or a two-row pattern to paste only its formatting. If copying fails, fix cut and paste in Excel before repeating the formatting transfer.
After copying, select the destination cells. On Windows, choose Home > Paste > Formatting; on the web, choose Home > Paste > Paste Formatting. On Mac, choose Home > Paste > Formatting. Repeat the pattern across the destination rows.
Keep the colors or remove the shading
After applying a banded table style, you can convert the table back to a regular range while retaining its existing formatting. On Windows, choose Table Design > Convert to Range; on Mac, choose Table > Convert to Range. Select Yes to confirm, keeping in mind that automatic table expansion stops.
In Excel for the web, right-click the table and choose Table > Convert to Range. This also retains the existing formatting but stops automatic table expansion.
To remove conditional formatting on Windows, select the affected cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. If other rules need to stay, open Manage Rules and delete only the alternating-row rule. In the web ribbon, begin at Home > Styles > Conditional Formatting.
For manual fills on Windows, select the affected cells and choose Home > Fill Color > No Fill; in Excel for the web, select them and choose Clear > Clear Formats. Match the removal method to how you created the shading, since table banding, conditional formatting, and manual fills each have their own controls.
Frequently Asked Questions
Can you choose two custom colors for a banded table?
Yes, in Excel for Microsoft 365 and Excel 2024 on Windows or Mac, select a table cell, choose Home > Format as Table > New Table Style, and name the style. Under Table Element, format First Row Stripe and Second Row Stripe through Format > Fill, choosing different colors. Select OK, then apply the style from Custom.
How do you highlight every other row in a PivotTable?
In Excel for Microsoft 365 or Excel 2024 on Windows or Mac, select a cell in the PivotTable, then open Design > PivotTable Style Options and select Banded Rows. Choose a suitable style from PivotTable Styles. This formats the report rows rather than the original source data.
Can you save alternating row formatting for future workbooks?
Yes, in Excel for Microsoft 365 or Excel 2024 on Windows or Mac, prepare a workbook with a banded table or conditional formatting rule and save it as a template. On Windows, use File > Save As, select Excel Template (*.xltx), and save it in your personal templates folder, which is usually Custom Office Templates or the folder set under File > Options > Save > Default personal templates location. Then create subsequent workbooks through File > New > Personal. On Mac, use File > Save as Template, select Excel Template (.xltx), and save, then reuse it through File > New from Template.
Is there another quick way to turn selected data into a banded table?
In Excel for Microsoft 365 or Excel 2024 on Windows, select populated data and click the Quick Analysis button at the lower-right corner of the selection. Choose Tables > Table, then select a banded table style if needed.