OmniDocShade Every Other Row Grey With Conditional Formatting in a Spreadsheet
Shade alternating rows in grey with a MOD and ROW formula in conditional formatting, plus fixes for offset, filtering and PDF-sourced tables.
If you work with long tables of names, prices or inventory counts, a plain white grid makes it hard to keep your place when you move across a line. Grey banding solves that. Every other row gets a light fill, so your eye can track one record from left to right. People often search for this as conditional formatting every other row grey, but most guides cover highlighting rows that meet a condition, such as sales above a target. Banding is a layout task, and it needs a different formula.
This guide shows the MOD and ROW pattern that shades alternating rows. It works in any spreadsheet app that supports conditional formatting with a custom formula. You will also learn how to start the stripes on your first data row, and what to check when filters or PDF-sourced tables get in the way.
How do you shade every other row grey? The short answer
Select your data, create a conditional formatting rule that uses a formula, enter =MOD(ROW(),2)=0, and give the matching cells a light grey fill.
| Your situation | What to do | Why |
|---|---|---|
| Plain table, headers in row 1 | Select from row 2 to the last data row, then use =MOD(ROW(),2)=0 | Even-numbered rows match, so shading starts at row 2 |
| You want the first data row shaded | Use =MOD(ROW()-ROW($A$2),2)=0 with your first data row in A2 | The formula counts from your first data row, not from row 1 |
| Table has filters switched on | Check the banding with filters off | The formula counts hidden rows, so visible stripes can look uneven |
| You want a darker or lighter grey | Change the fill color in the rule's format settings | The formula decides which rows shade; the fill decides how they look |
| Your table starts as a PDF | Export it to a spreadsheet, then apply the rule | Conditional formatting rules live in the spreadsheet file, not in the PDF |
Before you start
- Confirm your file is a spreadsheet. Use an .xlsx file or a similar format. Simple viewers often show only preset rules, so you need an editor that lets you enter a custom formula.
- Decide where the data starts. The formula counts rows from the sheet's row 1 unless you offset it. Knowing your first data row tells you whether you need the optional offset.
- Look for merged cells that span rows. Merged cells can break the stripe pattern, so unmerge them or keep them outside the range.
- Choose your grey before you start. A near-white grey, such as RGB 242, 242, 242, keeps text easy to read. Darker greys can make black text harder to see.
Applying grey banding with a MOD and ROW formula, step by step
- Select the data block. Click the first data cell (A2 if your headers are in row 1), then extend the selection to the last column and last data row. The rule only applies to what you select.
- Open the conditional formatting options. Find them on your spreadsheet app's toolbar and choose the option to create a new rule. This gives you a rule builder where you can type your own formula.
- Choose the formula option. Pick the rule type that decides formatting from a formula. Value comparisons cannot count rows, so this option is required.
- Enter the banding formula. Type =MOD(ROW(),2)=0. ROW() returns each row's number, and MOD(…,2) returns 0 for even rows, so those rows match the rule.
- Set the fill color. Pick a light grey for the fill. Leave the font color alone unless your text becomes hard to read.
- Check the range the rule applies to. Make sure the range in the rule matches your selection. A rule on the wrong range shades the wrong rows or columns.
- Save, then start on your first data row if needed. To shade starting at A2, replace the formula with =MOD(ROW()-ROW($A$2),2)=0. Subtracting the first data row's number makes that row count as zero, so it gets shaded.
Check the result
| Check | How to see it | If it's wrong |
|---|---|---|
| Shading starts on the right row | Look at the first two data rows | Switch to the offset formula, or fix the start cell in your range |
| Shading covers every column | Scroll to the last column | Reselect the full block, then reapply the rule |
| Stripes after sorting | Sort by one column and look at the rows | Stripes stay in position while data moves, which is expected for this formula |
| Stripes while filtering | Apply a filter and look at the visible rows | Remove the filter to read the banding, or accept uneven stripes while filtered |
| Grey is hard to read | Compare text against the fill | Pick a lighter grey in the rule's format settings |
If your table starts as a PDF
Conditional formatting rules are stored in a spreadsheet file, so you cannot apply them to a PDF directly. Use the PDF editor in the OmniDoc desktop client, available at download.omnidoc.top, to export the table to Excel format. Then open the exported file and apply the steps above. Compare the exported numbers and labels against the original PDF before you format them, because a converted table can differ from the source.
❓ FAQ
1. Do I need to reapply the rule when I add rows?
If you add rows inside the selected range, the rule usually expands with them. If you add rows below the last row, extend the rule's range to cover them, or reapply the rule to the new block.
2. How do I shade every third row instead?
Change the 2 to a 3 in the formula so it reads =MOD(ROW(),3)=0. Rows where the row number divides evenly by three will shade. The same offset idea from step 7 still applies if you want to start from your first data row.
3. Why not just fill every other row by hand?
A manual fill stays on the cells you colored. If you insert rows or sort, the pattern can break. A rule keeps the pattern tied to row positions, so new rows follow the same rhythm once the rule covers them.
4. Can I make the grey shading appear only in certain columns?
Yes. Select only the columns you want before you create the rule. The formula stays the same, but the shading applies only to the range you chose.
📝 Summary
Grey row banding comes down to one formula, =MOD(ROW(),2)=0, applied to a clean selection with a light fill. Use the offset version if you want shading to start on your first data row, and check the result with filters off. If your table begins as a PDF, export it to a spreadsheet first, then apply the rule.
