Skip to Content

How do I auto fill alternating colors in Excel?

Excel’s auto fill feature is a handy tool that allows you to quickly fill cells with data or formatting. One common use of auto fill is to alternate row colors in a table or spreadsheet. This can make your data easier to read and distinguish between rows. In this article, I’ll explain how to auto fill colors in Excel using formatting rules and conditional formatting.

Use AutoFill to Apply Alternating Colors

The quickest and easiest way to alternate colors in Excel is with AutoFill. Here are the steps:

  1. Select the cells you want to fill with color. Make sure to select at least two rows.
  2. On the Home tab, click the arrow below the AutoFill handle in the top-left corner of the selected cells.
  3. Choose “Fill” then select the color you want from the palette.

This will fill the first cell with the chosen color, the second cell with no fill (white), the third cell with the color, etc., alternating between the two.

You can then drag the AutoFill handle down to apply the alternating colors to additional rows. Just click and drag the small black cross in the bottom-right corner of the selection down the column.

Use Formatting Rules

Another way to auto fill alternating row colors in Excel is by using formatting rules. Here’s how:

  1. Select the cells you want to format.
  2. On the Home tab, click the Conditional Formatting drop-down and choose Manage Rules.
  3. In the Conditional Formatting Rules Manager, click New Rule.
  4. In the box that appears, select “Format only cells that contain” under Select a Rule Type.
  5. Set the rule to this formula: =MOD(ROW(),2)=1
  6. Click the Format button and choose the Fill tab. Pick the color you want.
  7. Click OK to create the rule.

This will apply the formatting rule to all selected cells, automatically filling every odd row with the chosen color. You can create additional rules to format even rows differently if desired.

Use Conditional Formatting

The most flexible way to alternate row colors in Excel is with conditional formatting. Here are the steps:

  1. Select the cells you want to format.
  2. On the Home tab, click Conditional Formatting > New Rule.
  3. In the New Formatting Rule dialog box, select “Use a formula to determine which cells to format”.
  4. Enter this formula: =MOD(ROW(),2)=1
  5. Click the Format button and select the Fill tab. Pick the color.
  6. Click OK to create the rule.
  7. Back in the New Formatting Rule dialog, click New Rule again.
  8. Enter this formula: =MOD(ROW(),2)=0
  9. Click Format, choose a different Fill color, and click OK.
  10. Click OK to add the second rule.

Now odd rows will be formatted with the first color and even rows will be formatted with the second color. You can add additional conditional formatting rules to alternate between more than two colors if needed.

Tips for Auto Filling Alternating Colors

Here are some additional tips for auto filling alternating row colors in Excel:

  • To quickly select all rows, click the number 1 header to select the entire column, then hold Shift and click the header of the last column.
  • You can apply the alternating color rules to columns instead of rows by switching ROW() to COLUMN() in the formulas.
  • Make sure to apply the formatting rules to all cells you want included. The colors won’t automatically fill downward if rows are added.
  • To remove the alternating color formatting, select the cells and clear the rules in the Conditional Formatting Rules Manager.
  • You can manually adjust the row colors using the Fill color picker after applying rules. The auto fill will override any manual colors.

Sample Alternating Color Table

Here is an example of a simple table with alternating row colors:

Item Quantity Price
Apples 5 $1.99
Oranges 3 $2.49
Bananas 7 $0.79

The colors help make the rows easier to read across the table. You could customize this further by formatting the header row, changing colors, adding borders, etc.

Using VBA to Auto Fill Alternating Colors

You can also use VBA code to automatically alternate row colors in Excel. This allows you to apply the formatting without manually creating rules each time.

Here is a simple VBA macro that will alternate colors in the selected cells:

Sub AlternateColors()

  Dim i As Long
  
  For i = 2 To Selection.Rows.Count Step 2
     Selection.Rows(i).Interior.Color = RGB(191, 191, 191)
  Next i
  
End Sub

To use this, follow these steps:

  1. Select the cells you want to format.
  2. Press Alt + F11 to open the VBE.
  3. Insert a new module and paste the above code into it.
  4. Change the RGB value to set the color (191, 191, 191 is a light gray).
  5. Run the AlternateColors macro.

This will instantly apply the alternating color formatting. The macro runs much faster than manually applying rules. You can customize the code further based on your specific needs.

Conclusion

Alternating row colors in Excel is easy once you know a few quick tricks. The AutoFill formats provide a couple clicks solution, while conditional formatting gives you more flexibility. And VBA macros let you apply the formats instantly without repetitive steps. Experiment with the different options to find the right approach for your data.