So you dye only every other row in an Excel list

11:28 pm May 16, 201712496

especially in large lists it’s hard to be able to follow a line with the eye from left to right. Because the procedure for proven itself to colorize each row alternately differently. There are two different ways.

as alternating row color

  1. mark the area where you want to have the alternate coloring. start
  2. , to the Ribbon.
  3. click the button as table format. Then a large selection window with predefined formatting works out.

    The simplest method leads to colored lines over the table formatting of Excel.

  4. find you one of the variants, which works with alternating coloring and click on.
  5. now for the desired color scheme, but also the corresponding formatting for the header row of the selected area.

that is way over the prefabricated formatting although quite simple, but offers the full flexibility. If you link fill conditional formatting with the formatting feature, you can, for example, also by column color or use more than two colors.

so you color rows or columns individually

  1. Mark throughout the range, which should get the streaks. Thereby, it is easiest to use the boxes with the line number, because automatically complete lines are marked. Click Start in the register formatting conditional on the button
  2. and select there new rule
  3. In the new window, you use the last option formula to determine the cells you want to format to determine .
  4. as formula for colouring line enter: =REST(ZEILE();2)=0
  5. want to you differ, however, columns, use: =REST(SPALTE();2)=0
  6. then select formatting a fill color that you like.
  7. the end result looks, for example, like this:

    Excel-Tabelle zeilenweise eingefärbt
    via conditional formatting, you can individually colorize Excel spreadsheets.

the formula used by the means of ZEILE() or SPALTE() delivered each row/column number and calculated by REST(), the rest of the Division by 2 (i.e. a modulo operation).

the result of this residual calculation changes values 0 and 1 line at a time between the. The result of 0, so the line number “smooth” by 2 is divisible, then attack the formatting.

begins the coloring for you with the wrong color, you just change the formula to … = 1 . Then, the formatting starts with the different color.

to change the formula or the color, use the best Manager for conditional formatting .

how to use three different dyes

you want to have several different colors, then increase the dividers and insert a new rule each additional Variant.

to get for example three different line colors, create a conditional formatting formula =REST(ZEILE();3)=1 and you apply formatting maybe a pastel blue by .

the more variant you create with =REST(ZEILE();3)=2 and give a light gray all applicable cells.


in conjunction with the default white background of all rows, whose line number is divisible by 3 in smooth, achieve three alternating background colors.

last updated on the 19.12.2016 / affiliate links / images from the Amazon product advertising API