Large tables in Excel look much more better and are easier to read when we choose to alternate row color. Each other row gets a background color, so it’s easier to read the values in the extreme right in association with the labels at the extreme left of the table.
3 ways of alternating rows color
1. Alternate row color using Conditional Formatting
This is the simplest solution for our objective.
Select the range for which you want to apply the alternation of background color and then select Conditional Formatting from the menu.
Choose Rule type: Use a formula to determine which cells to format and type the formula =MOD(ROW(),2)=0. This is translated like: change the cells background color if the cell is located in a row that satisfy the following test: the reminder of the division of the row number to 2 is null. Then select Format… where you choose the color of the background or borders. Click OK and there you have it.
2. Formatting row background using Format as Table
Select the range you want to format and use the function from the menu. Then select the design that suits you best. It looks like this:
This is also a simple method but sometimes it can become a headache because the range transforms to a table range and is not easy to work with it and you have to transform it back into a normal range by selecting the function Convert to Range from Table Tools-Design tab Tools group.
3. Alternate row color with VBA
This is one of my personal touch to Excel ergonomics. I have built a VBA macro excel file that offers some custom made functions to help me in the day to day work with Microsoft Excel. One of these macros helps achieve exactly the subject of this article.
I named it TheITFormula Macro Tool and I will offer it to anyone interested. This tool can be developed further, so that other useful macros and functions can be added.
You just have to save the excel file that holds the custom macros somewhere safe on your computer and set a quick access shortcut on your excel menu for the TheITFormula_Start macro (right click on the menu toolbar and select Customize Quick Access Toolbar. This macro displays the custom menu with the macros that you need.
The instructions are also inside the excel file.
After installation you just have to select the range you want to transform into a table and call the macro with the shortcut keys ALT+1 (if you position the start macro first in your quick access ribbon).
For the macro that alternates the rows background color you must select the range you want to design and press Alt+1 and click on the Table Format button in the form that opens.
The tool offers 3 more options: formatting numbers to show negatives in red, or to display numbers in thousands, or to show negative percentages in red. You can add many other macros that help you work faster and with a nice design in the same time.
The interesting thing is that you don’t have to keep open the excel file that holds the macros. The shortcut will open the file in the background and will close it after you select the macro-button that you need, without you seeing the file opening.
You can download The IT Formula Tool here.