This wikiHow teaches you how to highlight alternating rows in Microsoft Excel for Windows or macOS.
Step
Method 1 of 3: Using Conditional Formatting on Windows
Step 1. Open the spreadsheet you want to edit in Excel
You can also double-click the associated file on your PC.
This method can be used for any data type. You will be able to edit the data as needed without changing the format
Step 2. Select the cells you want to format
Click and drag the mouse so that the cells you want to change are highlighted.
To highlight alternating lines throughout the document, click the button Select All, which is a gray square button in the upper-left corner of the spreadsheet.
Step 3. Click the icon
next to " Conditional Formatting ".
This icon is on the Home label on the toolbar that extends across the top of the screen. A menu will open.
Step 4. Click New Rule
This button will open the “New Formatting Rule” box.
Step 5. Select Use a formula to determine which cells to format
This option is under “Select a Rule Type”.
If you are using Excel 2003, set “condition 1” to “Formula is “
Step 6. Enter a formula to highlight alternating rows
Type the following formula into the typing area:
=MOD(ROW(), 2)=0
Step 7. Click Format
This button is in the dialog box.
Step 8. Click the Fill label
It's at the top of the dialog box.
Step 9. Choose a pattern or color for the shaded lines and click OK
You will see a preview of the color below the formula.
Step 10. Click OK
Alternating rows in the worksheet will be highlighted with the color and pattern of your choice.
You can edit a formula or formatting by clicking the arrow next to Conditional Formatting (on the Home label), selecting Manage Rules, then select a rule.
Method 2 of 3: Using Conditional Formatting on Mac
Step 1. Open the spreadsheet you want to edit in Excel
You can usually do this by double-clicking the file on your Mac.
Step 2. Select the cells you want to format
Click and drag the mouse to select all the cells you want to edit.
If you want to highlight alternating lines throughout the document, press Command+A on your keyboard. This step will select all the cells in the spreadsheet
Step 3. Click
icon next to Conditional Formatting ".
You can find this button in the Home toolbar at the top of the spreadsheet. Format options will open.
Step 4. Click New Rule on the Conditional Formatting menu
Your formatting options will open in a new dialog box, entitled " New Formatting Rule ".
Step 5. Select Classic next to Style
Click the Style drop-down menu in the pop-up window, and select Classic at the bottom of the menu.
Step 6. Select Use a formula to determine which cells to format under Style
Click the drop-down menu under the Style option, and select Use a formula to customize your format with formulas.
Step 7. Enter a formula to highlight alternating rows
Click the formula box in the New Formatting Rule window, and type the following formula:
=MOD(ROW(), 2)=0
Step 8. Click the drop down menu next to Format with
This option is below the formula box at the bottom. Your format options will open in a drop-down menu.
The format selected here will be applied to all rows in the selection area
Step 9. Select a format option on the “Format with” drop-down menu
You can click an option here, and view it from the right side of the pop-up window.
If you want to manually create a new highlight format with another color, click custom formats… in the below section. A new window will open, and you can manually select the font, border, and color you want.
Step 10. Click OK
This option will apply your custom format, and highlight each row that alternates in the selection area of the spreadsheet.
You can edit the rule at any time by clicking the arrow next to Conditional Formatting (on the Home label), selecting Manage Rules, then select the rule.
Method 3 of 3: Using a Table Style
Step 1. Open the spreadsheet you want to edit in Excel
Usually you can simply double-click the associated file on your PC or Mac.
- Use this method only if you want to add data to the table being browsed while highlighting each alternating row.
- You only need to use this method if you need to edit the data in the table after applying the style.
Step 2. Select the cells you want to add to the table
Click and drag the mouse so that all the cells you want to change the style are highlighted.
Step 3. Click Format as Table
It's on the Home label on the toolbar that extends across the top of the app.
Step 4. Choose a table style
Browse for the options in the Light, Medium, and Dark groups, then click the one you want to use.
Step 5. Click OK
This step applies the style to the selected data.
- You can edit the table style by selecting or deselecting the preferences in the “Table Style Options” pane of the toolbar. If you don't see this pane, click any cell in the table to bring it up.
- If you want to change the table back to the regular cell size so that the data can be edited, click the table to open the table tool in the toolbar, click label Design, then click Convert to Range.
