Updated November 2021
Using Excel or Google Sheets can greatly increase your productivity and organization. If you’ve used a spreadsheet tool for a while, you probably know many of the tips and tricks when it comes to formatting and formulas.
However, you might not be familiar with some of the more aesthetically pleasing ways to manipulate an online spreadsheet. Whether you’re using Microsoft Excel or Google Sheets, you can change the colors of the cells, add column colors, and much more in just a few clicks. Read on to find out how to alternate row color in Excel.
How to Alternate Row Color in Excel
For those using Microsoft Excel, there are a few steps to alternate row colors. The most common way is to format your sheet (with the selected rows highlighted) as a table. A more complicated but more effective way is to use a formula to modulate the colors in a spreadsheet. This article will teach you both ways.
How to Alternate Row Color in Excel in Table Settings
The simplest way to customize Excel alternate row color is to format your sheet as a table. Here are the steps.
- Select and highlight the cells you wish to format. If you want the whole spreadsheet to alternate colors, hit Control (or Command if you’re on an Apple computer) and “A” at the same time.
- Go to “Home” and click “Format as Table.” This will format the entire sheet as a table (individual cells), but won’t affect any previous formatting or formulas you have added.
- When you click “Format as Table,” choose a style of table that automatically has alternate shading. You can choose from any of the colors available or personalize with your color.
Note: This process is the easiest way to add color to alternate rows in Excel. If you wish to color-code your columns instead, you can adjust this in “Table Tools.” Under “Design,” select “Banded Columns” instead of “Banded Rows” and the sheet will automatically make the change!
How to Alternate Row Color in Excel with a Formula
The other common way to create an alternate row color Excel sheet is to use conditional formatting. This method is slightly more complicated but works just as well as the table settings. It is also helpful if you want to choose more individual colors or patterns than the pre-designed tables allow.
- Select the range of cells you wish to edit. If you want the entire spreadsheet to have alternating rows, select the entire sheet.
- Go to the “Home” tab and look for “Conditional Formatting” under the “Styles” tab. This will allow you to format each row individually or use a formula to format the entire document’s rows or columns.
- Click on “use a formula to determine which cells to format” and enter the formula =MOD(ROW(),2)
- Choose the color, and click “OK.” Your table should be properly formatted with alternating colors!
Note: If you wish to alternate columns instead, use COLUMN instead of ROW in this formula. Also, you can change the number to vary the number of shaded columns or rows. This method works well if you want any other alterations or if you don’t want your spreadsheet formatted as a table. It is also applicable for excel 2010 alternate row coloring.
How to Alternate Row Color in Google Sheets
Many businesses and organizations utilize Google Sheets instead of Excel. It is free, accessible on any computer, and has many of the same features and functions. Google Sheets also has two separate ways to alternate the column or row color: the preset way and conditional formatting.
How to Alternate Row Color in Google Sheets
Google has made it easy to edit the design and color of spreadsheets. If you’re using Google Sheets, you’ll be able to customize almost everything about your sheet without needing to worry about formulas and intense formatting. Here are the simple steps to create alternate row color Google Sheets.
- Select the cells you wish to change. If you want to edit the entire spreadsheet, hit “Command + A” or “Control + A” on your keyboard.
- Find the “Format” tab at the top of the page and click it.
- Hit the “Alternating colors” section near the bottom of the drop-down menu, and choose your style and colors. You can choose one color and alternate with white, or pick two different colors to alternate between.
Hit “Done” and make sure that your document is saved (Google Docs automatically save unless you’re offline).
Note: if you prefer a header or footer in your table, you can choose those options in the alternating colors menu panel. The header and footer will be darker than the rest of the shaded rows to help differentiate.
How to Alternate Row Colors in Google Sheets with Conditional Formatting
While Google has a convenient and easy way of formatting the rows of your table, you might want a little more personalization. If you want to color every third row or have an alternate column color Google Sheets page, you might need conditional formatting. Here are the steps for more individual formatting.
- Find the “Format” tab and click on “Conditional formatting.” It is right above “Alternating colors” that will lead you to the proper place to input your formula and personalize the cells on your spreadsheet.
- Select the range you wish to format. For the entire document, type “A1:Z100” into the “Apply to range” box.
- Under the “Format cells if…” select “Custom formula is” and prepare to enter your formula.
- Enter the custom formula. The most common formulas entered are =ISEVEN(ROW()) for coloring even rows and =ISODD(ROW()) for odd rows. However, there are more options (see note below) for other variations.
- Choose the color of the fill (the paint bucket icon) and hit “Save.” The formatting will stay on the selected cells, and Google will automatically populate added rows with the same format.
Note: if you wish to color every third row, you will have to input =MOD(ROW(),3)=0 instead of the odd or even row formula. The number can be changed, depending on how many spaces you want between row colors. For columns, replace ROW with COLUMN.
With these steps, you will be able to alternate rows or columns in colors on Google Sheets and Excel. It will help you organize better, categorize items, and make the sheet easier to read and understand. Visit our blog for more Excel and Google Sheets tips and tricks!
Do you need to convert a Word, PPT or other kind of document to PDF?
Use Sizle’s free PDF Converter for fast and free file conversion.