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:
- Select the area to which you want to apply the formatting.
- Then click on "Conditional Formatting" in the "Start" tab.
- Click “New Rule” in the menu that appears.
- 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).
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.
- Clicking on "Formatting" in the same window will take you to the area where you can specify the desired formatting.
- In the "Fill" tab, select the color you want your rows to be colored in.
- After confirming with "OK" in both windows, the defined formatting is applied to the previously selected area.
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!