Color Every Other Row in Excel: 5 Methods That Actually Work

Staring at a wall of Excel rows that all look the same? You scroll, you lose your place, you start over. Color every other row in Excel and that pain vanishes in about thirty seconds. The eye locks onto each row, your spreadsheet looks polished, and managers stop squinting in meetings.

There isn't one right way to do it. Excel gives you at least five, and each fits a slightly different job. A quick budget? Use the Table shortcut. A live data range that grows every week? Conditional formatting is your friend. A locked-down report for finance? VBA gives you precision the other methods can't touch.

Here's the part nobody tells you: pick the wrong method and you'll spend the next hour fighting Excel as new rows refuse to take the banded look, or worse, the color jumps when you sort. We've watched accountants redo entire reports because of this. So before you grab the paint bucket, decide whether the data is static, dynamic, or destined for a pivot. Then commit.

This walkthrough covers every method I use day to day, the keyboard shortcuts that shave real time, and the small print most tutorials skip, like why MOD(ROW(),2) sometimes colors the wrong rows and how to fix it in one click. By the end you'll know which technique to reach for first, how to undo a banded look that's gone wrong, and how to keep alternating colors when you filter or sort the table.

Quick heads up: every screenshot and shortcut here works in Excel 2016, 2019, 2021, Microsoft 365, and Excel for the web. Mac users get the Cmd equivalents called out where they differ.

Method 1: Format as Table (The 10-Second Fix)

If you only learn one method, learn this one. Format as Table is the single fastest way to color every other row in Excel, and it survives sorting, filtering, and new rows pasted at the bottom.

Click any cell in your data. Press Ctrl + T (Cmd + T on Mac). Excel auto-detects the range, asks if your data has headers, and applies a banded style. Done. Hit Enter to confirm and you'll see alternating row colors instantly.

Don't like the default blue? Click anywhere inside the table, head to the Table Design ribbon tab, and scroll through the Table Styles gallery. There are 60 built-in looks plus a custom builder. Hover for live preview, click to commit. If you want banded columns instead of rows, the checkboxes on the same ribbon toggle banding direction in one click.

The hidden bonus: when you add a new row by tabbing off the last cell, Excel extends the banding automatically. Sort the table by any column and the colors travel with the rows. This is why every Excel power user defaults to Tables for anything resembling a list.

One catch. Tables disable some advanced formula behaviors like array formulas spilling across rows. If you hit a weird formula error after converting, that's why. You can always strip the table back to a range via Table Design > Convert to Range while keeping the colors intact.

Method 2: Conditional Formatting with MOD(ROW(),2)

Tables are great, but sometimes you need banded rows on a range that isn't a table, like a pivot output or a report template where structure matters more than convenience. That's where conditional formatting shines.

Select the range you want to band. Open Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format. In the formula box, type:

=MOD(ROW(),2)=0

Click Format, pick a fill color (light gray and pale blue test best for legibility), and hit OK twice. Even-numbered rows now sport your chosen color. Swap the 0 for a 1 if you'd rather color the odd rows.

The MOD function returns the remainder after division. ROW() returns the current row number. Together they ask, does this row number divide evenly by 2? If yes, paint it. Simple math, powerful result.

Want every third row instead? Change the formula to =MOD(ROW(),3)=0. Every fifth? Use 5. You can stack multiple conditional formatting rules to create more elaborate patterns, like one color for every third row and a different color for every sixth.

The downside compared to Tables: when you sort, the banding stays attached to the row numbers, not the data. So your stripe pattern remains uniform, but if you wanted the color to follow a specific record, this isn't it.

Method 3: PivotTable Banded Rows

PivotTables ignore regular Table styles. Try Ctrl + T on a pivot and Excel scolds you. Instead, click any cell inside the pivot, go to the PivotTable Design ribbon, and tick Banded Rows.

The PivotTable Styles gallery sits two clicks away and works exactly like Table Styles. Light, Medium, and Dark groupings give you 84 presets. Banded Rows and Banded Columns checkboxes flip the stripe pattern. The style updates live as you change pivot fields, so a column added to the Rows area gets the band treatment automatically.

For finance pros: combine banded rows with the Subtotals at Top setting and you get a report that reads like a printed annual statement. Drop a subtle border under each group total and the document is presentation-ready in three clicks.

Custom pivot styles are saved in the workbook. To make a style available across files, right-click your custom entry in the gallery and choose Set As Default for This Document, then save the file as a template (.xltx) in your XLSTART folder.

Method 4: VBA Macro for Permanent Banding

Conditional formatting is fragile. A user can wipe every rule with two clicks. For high-value templates, VBA writes the color directly into the cell so it survives anything short of a full reformat.

Press Alt + F11 to open the VBA editor. Insert a new module via Insert > Module. Paste this:

Sub BandRows()
Dim rng As Range, r As Range
Set rng = Selection
For Each r In rng.Rows
If r.Row Mod 2 = 0 Then
r.Interior.Color = RGB(230, 240, 250)
End If
Next r
End Sub

Close the editor, select your range, press Alt + F8, pick BandRows, click Run. Every even row picks up a pale blue fill that won't budge.

Tweak the RGB values for any color you like. RGB(245, 245, 245) gives a soft gray. RGB(255, 250, 230) lands on warm cream. Need a sharp accent? RGB(217, 234, 211) is a calm sage that prints well.

Hook the macro to a button via Developer > Insert > Button and your users hit one click for instant banding. Save the workbook as .xlsm to retain the macro.

Method 5: Power Query Output with Table Style

Pulling data from another source via Power Query? The query result lands as an Excel Table by default, which means banded rows come free. Click the loaded table, open Table Design, pick a style. When the query refreshes and new rows arrive, the banding extends to cover them.

If your refresh keeps reverting to the default blue style, the culprit is usually Properties > Adjust column width being ticked. Disable that and your custom style sticks across refreshes.

Power Query also exposes the Preserve column sort/filter/layout option in External Data Properties. Enable it and your banded look, sort order, and filters all persist through every refresh, which is exactly what a daily report needs.

Common Mistakes and How to Fix Them

The banding looks right at first then goes off when you delete a row. Conditional formatting tied to ROW() rebuilds the pattern, but only if the formula scope still covers the new range. Right-click the range, open Conditional Formatting > Manage Rules, and extend the Applies To field. A quick fix is to overshoot the original range by 50 rows so future deletes don't push you out of bounds.

Stripes appear in the header row. Adjust the MOD formula to start counting from your data, not the sheet: =MOD(ROW()-ROW($A$2)+1,2)=0. The offset locks the pattern to start exactly where you want it. If your header is two rows tall, anchor to $A$3 instead.

Colors disappear after copy-paste to another workbook. The receiving workbook needs the same Table style applied or the formula range relinked. Paste Special > Values and Formats fixes most cases, but if the source used a custom Table style, you'll need to either rebuild that style in the target file or convert the table back to a range before copying.

Banding clashes with conditional formatting flags (red for over-budget cells, for example). Move the banding rule to the bottom of the Conditional Formatting Manager and untick Stop If True for the banding entry. Excel layers the colors and your urgent flags stay visible. Pro tip: pick a banding color that contrasts with your accent colors so the layered look reads cleanly. Pale gray underneath, vivid red on top, works every time.

The colors look fine in Excel but pixelated in screenshots. That's a display zoom artifact. Set Excel zoom to 100 percent before screenshotting, and use the snipping tool's window-capture mode rather than free-form, which can introduce aliasing on the banded edges.

Filter applied, but the banding looks wrong on visible rows. This is expected with conditional formatting because hidden rows still occupy their row numbers, so the visible result skips colors. Either accept it or switch to Tables, where banding recalculates over visible rows only.

Print and Accessibility Considerations

Banded colors look great on screen and underwhelming when printed on a draft-mode home printer. Test with File > Print Preview before sending a report to anyone with a black-and-white laser. If the contrast is too low, bump saturation by 10 to 15 percent. Some printers strip light grays entirely below the 5 percent saturation mark, leaving you with a flat sheet that defeats the whole point.

Accessibility matters for any document shared internally. Pure red on pure green fails for color-blind users. Use the Review > Check Accessibility tool to confirm your color choices pass WCAG AA contrast at 4.5:1 for text. Light gray on white passes; light yellow on white usually doesn't. Aim for hex values in the #F0F0F0 to #E8EEF5 range and you'll cover the vast majority of viewers.

Screen readers ignore fill colors entirely. If your alternating stripes carry meaning, like marking every other row as a different department, add a text column to convey the same information. Color is decoration, not data.

One more print tip: if your banded report has page breaks mid-stripe, viewers see a half-colored row at the bottom of one page and the rest at the top of the next. Set Page Layout > Page Setup > Sheet > Rows to repeat at top to lock your header on every page, then use View > Page Break Preview to drag breaks to land on whole rows. The polish is worth the two extra minutes.

Working with Large Datasets

Banding 50,000 rows can crawl on older hardware, especially with conditional formatting. Excel evaluates the rule on every selected cell, every time the sheet recalculates. On a slow laptop the lag becomes obvious.

Switch to Format as Table for large datasets. Table styles are applied at the format-cache level, not re-evaluated per change, so they scale to hundreds of thousands of rows without choking the calculation engine. If you're stuck with conditional formatting, narrow the Applies To range to only the rows that hold data. Painting a rule across all 1,048,576 rows when you have 200 entries is the single biggest performance killer in this whole topic.

Another speed trick: turn off Animations under File > Options > General > User Interface Options. Animated transitions look slick but force a redraw with every scroll, and on heavily banded sheets that adds up.

Combining Banding with Other Formatting

The real power move is layering. Banded rows underneath, conditional formatting on top to flag specific cells. A pale gray every other row keeps the eye moving. A bright yellow on overdue invoices in column F catches attention exactly where it should.

Order matters. Open Conditional Formatting > Manage Rules and confirm your urgent flags sit above the banding entry. Excel processes top to bottom, so the higher rule wins when both fire on the same cell. Leave Stop If True unchecked on the banding so it shows through everywhere your accent flags don't claim the cell.

Borders push the look further. Add a faint bottom border to every row at the same time as banding and the table reads like a printed ledger. Skip vertical borders entirely; they fight with the banded stripes and the result feels cluttered.

Final Word: Pick One, Master It

Five methods is overkill for daily use. Pick the one that fits 90 percent of your work and get fast at it. For most people that's Format as Table. Two keystrokes, instant banding, survives sort and filter. Done.

Keep conditional formatting in your back pocket for the edge cases: a report that can't be a table, a pattern with custom intervals, a sheet where the row position matters more than the data. And keep VBA for the templates you guard like a hawk, where any wandering user could nuke the formatting otherwise.

If you support a team, pick one method and standardize. Mixing approaches across a shared file looks sloppy and creates maintenance headaches three months later when someone tries to apply your style and discovers it doesn't behave the way they expect. A short note on the cover sheet (Banded rows applied via Table style ABCD) saves the next editor twenty minutes of detective work.

The honest truth: banded rows are a tiny detail that signals you actually thought about who reads your spreadsheet. People remember the report that was easy to scan. They forget the one that wasn't, except for that one moment they lost their place in a 400-row dump and quietly cursed your name. Make the small move. Color every other row in Excel and your work looks the part.

One last thing worth saying out loud: don't band a sheet that doesn't need it. A 10-row summary table looks worse with stripes, not better. Banding earns its keep at roughly 15 rows and above, when the eye starts hopping. Below that, clean borders and bold headers do more for legibility. Use the right tool for the job at hand, and your spreadsheets will look the part every single time without fail.