Wish you could design your spreadsheets within seconds and not hours? If you do a lot of work in Google Sheets, you might find this formatting trick useful. While Microsoft Office has the Quick Styles feature to create alternating row colors for tables, it’s not that easy for those who have to use Google Sheets. So how do you alternate row colors in this web-based spreadsheet tool? With conditional formatting and a custom formula.
Open a new Google Spreadsheet and click on Formatting from the top menu. Select Conditional formatting. The formatting box will appear on the right side.
Check that the highlighted tab is for Single Color, then click the field under “Apply to range”. With the cursor inside this box, highlight the rows you want to apply the formatting to. Click “OK” on the pop-up box that appears.
Under “Format cells if”, find and select “Custom formula is”. On the field that appears below it, type the formula:
You can now change the Fill color by clicking on the drop-down formatting tool below the formula’s text field. This applies custom formatting to all even-numbered rows. Click “Done” when you’re happy with your row’s first color.
Click on “Add another rule” on the bottom. This time, you need to enter a different formula:
Select a second row color from the color palette and click “Done”. As the formula indicates, it will apply any custom formatting to all odd-numbered rows.
You will see the two formatting styles listed on the right pane. If you want to change the row color combinations, you can simply click on each of the rule and adjust or change colors.
Since you can apply formatting to specific areas of the spreadsheet, you can have two different “zebra stripes” within a sheet (as shown below). Just add a new rule, use the same custom formulas but change the cell range.
In addition to row colors, you can also customize the font color and style of rows. Just select and apply it from the same formatting toolbox where you selected your row colors. Here, you can change fonts to any color, make it bold, underlined, italics or strikethrough. Always remember to click on “Done” before closing the formatting pane.
Note that rows with conditional formatting cannot be changed in the normal way. You need to remove the formatting first by going back to the Conditional formatting window and deleting each rule. To delete a rule, simply click on any cell where the formatting is applied, go to Format>Conditional formatting to bring up the list of rules. Hover over a rule and click on the trash bin that appears.
This method can also be applied to columns, but the formula should be:
=ISEVEN (COLUMNS()) or =ISODD (COLUMNS())
All other steps remain the same.
Things need not be tedious when creating documents, especially in a web tool like Google Sheets. The formula is simple, and there are no limits to add more rules. With this little trick, you should be able to create tables and presentations in lesser time than before.