Excel tables with alternating row colors

2 min read
November 24, 2022

Virtually every person knows them, but hardly anyone knows how to create them in a fixed way: Excel tables with alternating line color.

5 steps to alternating row colors in Excel

Table templates in Excel already offer some useful variants, but there is no simple tool for defining colors and another formatting yourself. This can be remedied with a simple Excel formula and conditional formatting:

excel alternating row colors

  1. Select the area to which you want to apply the formatting.

  2. Then click on "Conditional Formatting" in the "Start" tab.
    Conditional formatting to create alternating row colors in excel

  3. Click “New Rule” in the menu that appears.
    create new rule to alternate row colors in excel

  4. In the opened window, select the last entry "Use formula to determine cells to format" and insert the following formula in the input field under "Format values for which this formula is true: =REST(ROW();2)=1 (each row with odd row number will be formatted).
    create new formatting rule in excel to alternate colors
    By changing the numbers in the formula, different formatting can be achieved: For example, the setting can be applied to every even line by using a 0 as a remainder, or the divisor in the formula (in the example "2") can be replaced by any other number, so that instead of every second line, for example, every third line can be formatted differently.

  5. Clicking on "Formatting" in the same window will take you to the area where you can specify the desired formatting.
    formatting menu to select alternating colors excel

  6. In the "Fill" tab, select the color you want your rows to be colored in.Excel select color to fill alternating row colors
  7. After confirming with "OK" in both windows, the defined formatting is applied to the previously selected area.
    result alternating row colors excel

Are you looking for ways to work in Excel while adhering to your company's corporate design? Excel is difficult enough to handle and all the formatting to adjust the design takes a good portion of your workday?

Our Brand Control solution helps employees adhere to the corporate design. Our Office Add-Ins make the formatting easy and quick, enabling employees to focus on their really important tasks. 

Contact us for more information! Our experts are looking forward to improving your company's efficiency with you!

New call-to-action

Subscribe to our newsletter