Ever found yourself bogged down with data in Google Sheets, wishing to jazz it up a bit? Coloring alternate rows can significantly improve readability and visual appeal. Here's a comprehensive guide on how to color every other row in Google Sheets, with a bonus tip for easy automatic application.

Google Sheets doesn't have a native feature for this, but with a little bit of knowledge about conditional formatting and Google Apps Script, you can achieve this in no time.

Using Conditional Formatting to Color Alternate Rows
Conditional formatting allows you to change the appearance of cells based on their values. In this case, we'll use it to change the background color of every other row.

First, let's understand the formula we'll use. The formula to get the row number is ROW(). By dividing the row number by 2 and checking if the remainder is 1 or not (with the MOD() function), we can alternate between two colors.
Apply conditional formatting to alternate rows

Here are the steps to apply this formatting:
1. Select the range you want to format (e.g., A1:Z100).
2. Click 'Format' in the menu, then hover over 'Conditional formatting'.

3. Under the 'Format cells if...' dropdown, choose 'Custom formula is'.
4. In the 'Value or formula' field, enter the following formula: `=MOD(ROW(),2)=1`.
5. Choose the formatting style you want (Background color, for example).

6. Click 'Done'.
Color the rows based on other criteria (Bonus)



















You can modify the formula slightly to base the formatting on other criteria. For example, if you want rows containing certain text to be colored, you could replace ROW() with the address of the cell containing that text, like this: `=MOD(COUNTIF(A:A,"search"),2)=1`.
Or, you could color rows based on the values in a cell: `=MOD(A1,2)=1`.
Automatically Apply Formatting to New Rows
What if you frequently add new rows to your sheet? Here's how you can automate the formatting:
We'll create a Google Apps Script that runs on every change to the sheet. It will use the above conditional formatting formula to apply the style each time.
Create and Run a Google Apps Script
1. Click 'Extensions' in the menu, then 'Apps Script'.
2. Paste the following script into the code editor:
```javascript
function onEdit(e) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const s = ss.getActiveSheet();
const r = s.getActiveRange();
let rng = r.getCells(SpreadsheetApp.Range.useRowColumns());
let rules = s.getConditionalFormattingRules();
let rule = rules.find(r => r.getDescription() == 'Alternate Row');
if (!rule) {
let formulas = [{
whenCellEmpty: false,
backgroundColor: new RichTextValue().setBehind(false).setBackgroundColor('bcd8b0'),
condition: {
type: RichTextValue.Types.TEXT_CONTAINS,
value: '=MOD(ROW(),2)=1'
},
UI="/**/);
}
rule = s.setConditionalFormatting(rng, formulas);
}
}
```
3. Click 'Save' and give it a name, like 'AlternateRow'.
4. To test, insert a new row and watch the row above it change color.
And there you have it! With just a bit of creativity and a little bit of code, you've transformed your Google Sheets from plain to vibrant. Whether you're analyzing data, creating a report, or just wanting to make things pop, coloring alternate rows can make an enormous difference.