How To Shade Every Other Line In Excel | Cleaner Sheet View

When a sheet gets busy, your eyes start jumping to the wrong row. That’s where alternating shading helps. A light fill on every second row gives the sheet a cleaner rhythm, makes line-by-line reading easier, and cuts down on copy mistakes when you’re working with long lists.

Excel gives you two solid ways to do it. You can use conditional formatting, which is the better pick when you want the shading to react to inserted rows and changing data. Or you can turn the range into a table and let Excel apply banded rows with one click. Both work. The right one depends on how your sheet is built and how often it changes.

This article walks through both methods, shows the formulas that control the pattern, and fixes the common snags that make alternate-row shading stop working. If you want a method that still looks clean after sorting, filtering, or adding data, start with conditional formatting.

Why Alternating Row Shading Works So Well

Plain white grids can get tiring once you pass a few dozen rows. Alternating shading breaks the block into readable strips. It does not change your data. It just changes how easy that data is to track with your eyes.

This is most useful in sheets like expense logs, stock lists, order exports, class rosters, time sheets, and project trackers. In those cases, the goal is not decoration. The goal is faster reading with fewer row-matching errors.

Best Method For Most Sheets

The most reliable way to shade every other line is with a conditional formatting rule tied to a row formula. Microsoft’s own steps for applying color to alternate rows or columns use the same idea: Excel checks the row number, then fills only the rows that match the rule.

Set Up The Rule

Select the range you want to format. Go to Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format. In the formula box, enter =MOD(ROW(),2)=0. Then pick a fill color and click OK.

That formula shades even-numbered rows. If you want the pattern to start on the first row of your selected range instead, switch it to =MOD(ROW(),2)=1. The pattern flips right away.

What The Formula Is Doing

ROW() returns the row number of each cell. MOD(…,2) checks whether that row number is even or odd. If the result matches your rule, Excel applies the fill. That means the striping stays tied to the row numbers, not to the text sitting in those rows.

That matters because manual fill color looks fine at first, then falls apart when data shifts. A formula rule keeps the banding alive after inserts, deletes, and copied data.

How To Shade Every Other Line In Excel With Conditional Formatting

If you want a clean setup that still works next week, this is the one to use. Microsoft’s page on using conditional formatting in Excel also shows how rules can be edited later, which is handy when you need to change the shaded range or tweak the fill color.

1. Select the cells you want to stripe.
2. Open Conditional Formatting from the Home tab.
3. Create a new rule with a formula.
4. Use =MOD(ROW(),2)=0 for even rows or =MOD(ROW(),2)=1 for odd rows.
5. Choose a light fill that does not drown out the text.
6. Save the rule, then test it by adding a new row in the range.

If your data starts below a title block or a blank area, you can make the pattern start from the first row in your data instead of from row 1 on the sheet. A formula like =MOD(ROW()-5,2)=0 starts counting from row 6. Change the number to match the row just above your data.

That small tweak makes a sheet feel much more polished. Your stripes line up with the data block, not with the worksheet grid as a whole.

When Turning The Range Into A Table Is Better

If your data is already structured with headers and you like built-in sorting and filtering, Excel tables are the easy route. Once you convert the range into a table, you can turn on banded rows and let Excel handle the pattern. Microsoft’s page on formatting an Excel table shows the Table Style options for banded rows and columns.

Pick the range, press Ctrl + T, confirm whether your table has headers, then choose a table style with banded rows. This method is fast, and it grows with the table when you add new data below the last row.

There is one trade-off. Table styles are less flexible than formula-based rules when you need custom striping, such as starting after a title row, skipping subtotal lines, or shading every third row instead of every second one.

Method	Best For	What To Expect
Conditional formatting with =MOD(ROW(),2)=0	Dynamic data ranges	Updates with row changes and gives full control over color and pattern
Conditional formatting with =MOD(ROW(),2)=1	Starting the stripe on row 1 of the sheet	Same pattern as above, but the first shaded row flips
Adjusted formula like =MOD(ROW()-5,2)=0	Data that starts lower on the sheet	Lets the pattern begin from the first row of the selected block
Excel table with banded rows	Lists with headers, filters, and routine updates	Fast to apply and expands as the table grows
Excel table with custom table style	Sheets used for repeated reporting	Keeps a consistent look across many tabs
Manual fill color	One-off static sheets	Looks fine at first, then breaks when rows move or data grows
Alternating column shading	Wide sheets with many fields	Helps left-to-right reading more than row tracking

Pick The Right Formula For Your Sheet

You do not need many formulas for this job. You just need the right one for the layout in front of you. The table below shows the patterns most people use and what each one changes.

Formula	Use Case	Result
=MOD(ROW(),2)=0	Shade even rows	Rows 2, 4, 6, 8 and so on get filled
=MOD(ROW(),2)=1	Shade odd rows	Rows 1, 3, 5, 7 and so on get filled
=MOD(ROW()-n,2)=0	Start striping from a later row	The pattern begins where your data block starts
=MOD(ROW(),3)=0	Shade every third row	Useful for grouped records or print layouts

Fix Problems When The Shading Looks Wrong

The Pattern Starts On The Wrong Row

This is the most common issue. The rule is working, but it is counting from the sheet’s row numbers, not from the first row in your data block. Use an adjusted formula with a subtraction value so the pattern starts where you want it to.

The Rule Does Not Extend To New Data

If you applied conditional formatting to a fixed range, new rows outside that range will stay plain. Open Conditional Formatting > Manage Rules and widen the “Applies to” range. If you add data all the time, turning the range into a table may save you from this step.

Sorting Or Filtering Makes The Banding Feel Off

When you sort data, conditional formatting still follows the row numbers in the displayed range. That usually looks right. If the sheet still feels messy, check whether old manual fills are sitting under the rule. Clear those fills first so the formula rule is the only thing controlling the color.

The Fill Color Is Too Dark

Use a soft tint. Pale gray, light blue, or a muted accent color works better than a heavy block of fill. Dark shading fights with the text and makes the sheet harder to read, which defeats the whole point.

Ways To Make Banded Rows Look Cleaner

Keep the header row distinct from the striped rows. Bold text and a stronger header fill help the body rows stay subtle. Also, leave gridlines on unless your table style already provides enough contrast. Too much styling makes a working sheet feel crowded.

If the worksheet is printed, test the shading once before you share it. Some light fills barely show on certain printers. A pale gray often prints more consistently than a pastel color.

For shared workbooks, stick with one striping style across tabs. A steady pattern makes the file easier to use, especially when someone opens it cold and needs to find data fast.

A Practical Choice For Most Users

If you want the most control, use conditional formatting. If you want the fastest setup on a structured list, use an Excel table with banded rows. Either way, alternating row shading is one of those small formatting moves that makes a sheet feel calmer, easier to read, and easier to trust.