Create Custom Zebra Lines
When you have to deal with lists that has a large number of rows, zebra lines can make spotting changes in the data much easier. Excel does provide an easy way for regular zebra lines (colored rows: 1-3-5-… ). But in order to create custom zebra lines, you need to know this trick.
We are going to review our employees based on their level in our grading system. We have a list of employees sorted by level. But we want virtually distinguish each level on the list with zebra lines.
In order to create custom zebra lines, we need to mark the rows that has the same level value with same number and increase the number for each change in level. To do this, add following if formula to cell J2.
This formula does exactly what we need. Checks each level in each cell in column B with the cell above and get a value based on this comparison.
- In cell J2, formula will result as 1 (Condition is not met, B2=A, B1=Level so formula will result as Cell J1 + 1, which is 0+1=1).
- In cell J3, formula will again result as 1 (Condition is met, B3=A, B2=A so formula will result as Cell J2, which is 1).
Copy the formula downwards until the end of the list. Here is how it is supposed to look like:
Now we are ready to apply some conditional formatting to create custom zebra lines.
Select first row in your list and add conditional formatting with the rule set as the formula shown below:
This formula divides the value in column J in each row with 2 checks whether the remainder is 0. Formula will result TRUE for even values and FALSE for odd values. Click “Format” button after you write your formula and set fill color as any color you like (I chose light orange).
Apply the condition and exit from conditional formatting interface. You will not see any changes in formatting yet, because first row has an odd number in column J (1).
Now copy first row of the list and paste it “as formatting” on the rest of the list. Your list will be formatted with zebra lines that changes with each change of values in column J.
You can hide column J and use/print your viewer friendly list as you like.