How to Alternate Row and Column Colors in Excel

Alternating row and column colors in Microsoft Excel makes large datasets easier to scan, reduces errors when reading across a sheet, and gives your spreadsheets a polished, professional look. You can achieve this with Excel’s built-in table styles, which apply banded colors automatically, or with conditional formatting, which gives you more control over which cells are shaded. This guide covers both methods step by step, plus how to fix common issues.

What You’ll Need

• Microsoft Excel (2010 or newer; the steps are the same for Excel for Microsoft 365, Excel 2021, 2019, and 2016).
• A spreadsheet with data in rows and columns.
• Basic familiarity with selecting cells and using the Ribbon.

Step-by-Step

Method 1: Using Excel Tables (Easiest for Rows)

1. Select your data range. Click and drag to highlight all cells that contain data, including headers if you have them. If your data is surrounded by blank cells, you can click any cell inside the data and press Ctrl+T to select the entire range automatically.
2. Convert to a table. With the range selected, go to the Insert tab on the Ribbon and click Table (or press Ctrl+T). A dialog box appears asking where your data is. Make sure the range is correct and check the box “My table has headers” if your first row contains column titles. Click OK.
3. Apply a banded style. Your data is now a table, and Excel automatically applies a default style with alternating row colors. To change the style, click anywhere inside the table. The Table Design tab (called Table Tools Design in older versions) appears on the Ribbon. Click it, then browse the Table Styles gallery. Hover over any style to preview it, and click one that has banded rows (most do).
4. Toggle banding on/off. In the Table Design tab, look for the Table Style Options group. Check Banded Rows to alternate row colors. Uncheck it to remove banding. Check Banded Columns to alternate column colors instead (or in addition).
5. Remove the table format if you don’t want a table. If you only want the colors and not the table features (like filters or the structured reference), right-click the table, choose Table > Convert to Range, and confirm. The banded colors remain, but the table functionality is gone.

Method 2: Using Conditional Formatting (More Control)

1. Select the range. Highlight the cells you want to format. If you want the entire sheet, click the triangle at the top-left corner where the row numbers and column letters meet.
2. Open conditional formatting. Go to the Home tab, click Conditional Formatting in the Styles group, and choose New Rule.
3. Create a formula rule. In the New Formatting Rule dialog, select “Use a formula to determine which cells to format.”
4. Enter the formula for alternating rows. In the formula box, type: =MOD(ROW(),2)=0 for even rows, or =MOD(ROW(),2)=1 for odd rows. This formula checks the row number of each cell. If the remainder when dividing by 2 is 0, the row is even; if it’s 1, the row is odd. Choose one to format.
5. Set the fill color. Click the Format button, go to the Fill tab, pick a color, and click OK. Then click OK again to close the New Formatting Rule dialog.
6. Add a second rule for the other rows. Repeat steps 2–5 with the opposite formula (e.g., if you used =0, now use =1) and a different fill color. This gives you two alternating colors.
7. For alternating columns, use the COLUMN function. Follow the same steps but use =MOD(COLUMN(),2)=0 for even columns and =MOD(COLUMN(),2)=1 for odd columns.
8. Start from a specific row or column. If your data starts in row 5, for example, use =MOD(ROW()-5,2)=0 to make row 5 the first color, row 6 the second, and so on. The same logic applies to columns: =MOD(COLUMN()-3,2)=0 makes column C the first color.

Method 3: Manual Formatting (Quick but Inflexible)

1. Select the rows or columns you want to color. Click a row number to select the entire row, or a column letter to select the entire column. Hold Ctrl to select multiple non-adjacent rows/columns.
2. Apply a fill color. Go to the Home tab, click the Fill Color button (paint bucket icon) in the Font group, and choose a color.
3. Repeat for the alternate rows/columns. This method is fine for a one-off small range, but it doesn’t update automatically when you add rows or columns. It’s generally not recommended for dynamic data.

Common Mistakes

• Applying conditional formatting to the wrong range. If you select the entire sheet but your data starts in row 5, the formula will color row 1 as the first color, which may not align with your data.
• Using absolute row references in the formula. The formula =MOD($A$2,2)=0 is wrong because it always checks the same cell. You must use relative row references like =MOD(ROW(),2)=0 so each cell checks its own row.
• Forgetting to add a second rule. If you only add one conditional formatting rule, only the even (or odd) rows will be colored. The other rows will remain unformatted, not a second color.
• Converting a table to a range and then adding new rows. After converting, the banding is static. New rows you insert will not automatically match the alternating pattern unless you use conditional formatting.
• Using a table style that has no banding. Some table styles are plain. Check the Table Style Options to ensure Banded Rows or Banded Columns is checked.

Tips

• Use light colors for banding. Dark colors can make text hard to read. Light grays, blues, or greens are typical choices.
• Keep banding consistent with your brand or theme. Use colors that match your company’s palette or the purpose of the spreadsheet.
• Use table styles for simplicity. If your data is in a proper table, the banding automatically extends when you add new rows. This is the most maintenance-free method.
• Use conditional formatting for dynamic ranges. If you want banding to apply to a range that may grow, conditional formatting with a formula will automatically cover new cells as long as the range expands (e.g., if you insert rows within the range).
• Combine row and column banding. You can check both Banded Rows and Banded Columns in a table to get a checkerboard effect, but be aware that the column banding may override row banding depending on the style.
• Use the Format Painter to copy banding. If you have a range with conditional formatting, select a cell with the formatting, click the Format Painter (Home tab), and then drag over another range to apply the same rules.

Troubleshooting

• The banding doesn’t appear after applying a table style. Check the Table Style Options. If Banded Rows is unchecked, the style won’t show banding. Also, make sure the selected style actually has banding (some are flat). Try a different style.
• Conditional formatting colors don’t change when I insert a row. If you insert a row within a range that has conditional formatting, the formatting usually applies to the new row automatically. If it doesn’t, it’s because the range didn’t expand. Check the range in Manage Rules (Home > Conditional Formatting > Manage Rules) and adjust the range to include the new row.
• The colors are wrong (e.g., row 1 is colored but row 2 isn’t). This is usually a formula issue. For row banding, use =MOD(ROW(),2)=0. If your data doesn’t start in row 1, adjust the formula with a subtract offset. For example, if data starts in row 3, use =MOD(ROW()-3,2)=0.
• Banding disappears when I filter a table. This is normal. Table banding is based on the row position in the table, not the filtered view. When you filter, rows are hidden, and the visible rows may not show alternating colors. To fix this, you can use conditional formatting with a formula that uses the SUBTOTAL function, but that’s advanced. For most users, it’s acceptable.
• I converted a table to a range and now banding is gone. When you convert a table to a range, the banding is removed because it was part of the table style. To keep the colors, you need to apply conditional formatting or manual fill colors before converting.

Frequently Asked Questions

How do I alternate row colors without using a table?

Use conditional formatting with the formula =MOD(ROW(),2)=0 for one color and =MOD(ROW(),2)=1 for another. This works on any range, even if it’s not a table.

Can I alternate colors based on a column’s value?

Yes. Instead of using ROW() or COLUMN(), use a formula that checks a cell’s value. For example, to color every other unique value in column A, you’d use a more complex formula with COUNTIF. A simpler approach is to add a helper column with 1 and 0 alternating, then use conditional formatting based on that column.

Why doesn’t my conditional formatting update when I add new rows?

Conditional formatting only applies to the range you specified. If you add rows below the range, they won’t be formatted. You can either expand the range in Manage Rules, or use a table (which automatically expands).

How do I remove alternating colors?

For a table, go to Table Design > Table Style Options and uncheck Banded Rows/Columns. For conditional formatting, select the range, go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

Can I print with alternating colors?

Yes, but you need to enable background printing. Go to Page Layout > Page Setup > Sheet tab, and check “Black and white” or “Draft quality” might remove them. To print colors, make sure the printer is set to color and the “Print background colors and images” option is checked in Page Setup (it’s under the Sheet tab).

Does alternating colors slow down my spreadsheet?

Conditional formatting can slow down very large sheets (hundreds of thousands of cells) because it recalculates. Table styles are more efficient. If performance is an issue, use tables or limit conditional formatting to the used range.

Related

• Fix iPad Stuck in Recovery Mode
• Windows Factory Reset: Step-by-Step Guide
• Android Wi-Fi Hotspot Authentication Problem: Fixes