Free counters!

 

Header_top
 

Ever needed to apply automatic formatting to every second row in MS Excel? - But need to have a different formatting based on what the contents of the cell are?

Using the MOD and ROW functions, you can tell Excel to perform tasks on data in every other row. - An aditional condition will make it possible to format the cells additionally based on the content on the cell, using the "Cell Value Is" Option.

Click here for basic tutorial, and general information about the ROW & MOD functions

This method can be extended to become a very powerfull time saver for creating visually appealing, and easily edited spreadsheets very quickly.

(Click On Thumbnails for bigger pictures!)

Method:

1.

Select your data range.  

Conditional Formatting
Conditional formatting Box
Conditional Formatting

2. On the Format menu, click "Conditional Formatting."
3. Under Condition 1, select "Cell Value Is".
4. In the data entry box, type”X” - (or what ever data you wish to match & Change formatting for)
5. Click the Format button. In the Format Cells dialogue box, click the Patterns tab.
6. Select a colour from the pattern Dialogue, then click OK  

Excel Format Cells
Format Box

   
7. Under Condition 2, select "Formula Is".
8. In the data entry box, type”=MOD(ROW(),2)=0
9. Click the Format button. In the Format Cells dialogue box, click the Patterns tab.
  Alternate Row Formatting Results With X Cell Value Option
10 Select a colour from the pattern Dialogue, then click OK
11 Click OK again in the Conditional Formatting box.
 

The formula in step 8: =MOD(ROW(),2)=0, checks whether the row number divided by 2 has a remainder of 0. If this is true, the row is coloured in. If false the row is left as it is.

The condtions are formatted in order, this means that if the first condition is met, the 2nd will not be processed.

 

 
Bottom_Border