- Alternating Row or Column Color Fill in Excel
1.1 Using the MOD Function for Alternating Row Colors
First, select the cell range, then click on the menu: Home → Conditional Formatting → New Rule, and enter the formula: =MOD(ROW(),2)=0 (for alternating every two rows), as shown below:
| Item No | Flame Retardant | Quantity |
|---|---|---|
| F20201 | Y | 50 |
| F20202 | N | 60 |
| F20203 | Y | 100 |
| F20204 | Y | 200 |
| F20205 | N | 300 |
| F20206 | N | 5 |
| F20207 | Y | 6 |
1.2 Using the MOD Function for Alternating Column Colors First, select the cell range, then click on the menu: Home → Conditional Formatting → New Rule, and enter the formula: =MOD(COLUMN(),2)=0 (for alternating every two columns), as shown below:
| Item No ||||| Flame Retardant ||||
F20201 |||||| Y ||||
F20202 |||||| N ||||
F20203 |||||| Y ||||
F20204 |||||| Y ||||
F20205 |||||| N ||||
f201206 &&&&&;N &&&&&;
f201207 &&&&&;Y &&&&&;
different quantities of flame retardants are displayed accordingly.
normally used quantities include:
popular quantity ranges from [50-100] to [300].
automatically highlight a row based on data value.
below are steps for setting up dropdown menus and highlighting conditions:
begin by selecting cells where you want to set up a dropdown menu,
and navigate to Data Validation under Data tab,
in Allow choose List option,
and select your desired range before confirming.
to apply highlights based on selected values,
you need to choose your data area first,
and go back into conditional formatting choosing new rule again,
here opt for using a formula to determine which cells should be formatted,and input this specific formula =$A1=$F$3 after clicking format fill sectionto pick your preferred color.note that A1 is referencingthe first cell of your model columnand $F$3 indicates our chosen model's position.