How to Highlight Alternating Rows in Excel

By Nick Peers

Make alternating rows stand out from the rest of your Microsoft Excel 2013 spreadsheet by highlighting them. Use one of two ways to highlight the rows: conditional formatting or table styles. Conditional formatting requires a formula to determine whether a row is odd numbered or even numbered. If you prefer to use a table style, you also get all the benefits of Excel tables, such as header rows and filtering options.

Using Conditional Formatting

Step 1

Launch Microsoft Excel 2013, open the spreadsheet you want to edit and then select the cells you want to format. To select the entire spreadsheet, click the "Select All" button in the top left corner of the spreadsheet.

Step 2

Click the arrow next to "Conditional Formatting" in the Styles group on the Home tab and then click "New Rule" to display the New Formatting Rule dialog.

Step 3

Select "Use a formula to determine which cells to format" from the "Select a Rule Type" list and then type "=MOD(ROW(),2)=0" (without the quotation marks) into the "Format values where this formula is true" field. The formula determines whether a row is odd numbered or even numbered. Click "Format" to display the Format Cells window.

Step 4

Click the "Fill" tab to view all fill options and then click one of the colors in the Background Color section to use it to highlight the rows. To get access to more colors, click the "More Colors" button to open the Colors dialog.

Step 5

Click "OK" to close the Format Cells window and then click "OK" again to apply the rule to the spreadsheet and highlight the alternating rows. Save the changes by pressing "Ctrl-S."

Using Table Styles

Step 1

Select the range of cells you want to convert to a table. Avoid selecting the entire spreadsheet by clinking the "Select All" button because converting it to a table may take a significant amount of time to complete.

Step 2

Click "Format as Table" in the Styles group on the Home tab and then choose one of the predefined table styles. Click "OK" to close the Format As Table dialog. Alternating rows are automatically highlighted when you create a table.

Step 3

Select a cell inside the table to display the Design tab, click the "Convert to Range" button in the Tools group and click "Yes" to convert the table to a range of cells and remove all table functionality; the table style is preserved. Save the changes to the spreadsheet by pressing "Ctrl-S."

Tips & Warnings

  • Highlight alternating columns instead of alternating rows using conditional formatting by typing "=MOD(COLUMN(),2)=0" (without the quotes) into the "Format values where this formula is true" field.
  • Highlight alternating columns instead of alternating rows using table styles by deselecting the "Banded Rows" option from the Table Style Options group on the Design tab and then selecting the "Banded Columns" option.
  • Click the "Custom" tab in the Colors dialog to select a color by using its RGB or HSL values. Click "OK" to apply the color to the rows.
  • Click the "Fill Effects" button in the Format Cells window to display the Fill Effects dialog. The options in the Fill Effects window help you add a gradient to the rows.
  • To undo changes to the spreadsheet, press "Ctrl-Z." You can undo up to 100 changes in Excel 2013.
  • The formatting is not applied automatically to new rows when you add more data if you convert the table to a regular range of data.
  • Converting the entire spreadsheet to a table may take a significant amount of time to complete because you are applying the change to 1,048,576 rows and 16,384 columns.