How to Highlight Every Other Row in Excel

Method 1 of 3:

Using Conditional Formatting on Windows

  1. How to Highlight Every Other Row in Excel Picture 1
    Open the spreadsheet you want to edit in Excel. You can usually do this by double-clicking the file on your PC.
    1. This method is suitable for all types of data. You'll be able to edit your data as needed without affecting the formatting.
  2. How to Highlight Every Other Row in Excel Picture 2
    Select the cells you want to format. Click and drag the mouse so that all cells in the range you want to style are highlighted.[1]
    1. To highlight every other row of the entire document, click the Select All button, which is the gray square button/cell at the top-left corner of the sheet.
  3. How to Highlight Every Other Row in Excel Picture 3
    Click the
    How to Highlight Every Other Row in Excel Picture 4
    icon next to "Conditional Formatting." It's on the Home tab in the toolbar that runs along the top of the screen. A menu will expand.
  4. How to Highlight Every Other Row in Excel Picture 5
    Click New Rule. This opens the 'New Formatting Rule' dialog box.
  5. How to Highlight Every Other Row in Excel Picture 6
    Select Use a formula to determine which cells to format. You'll find this option under 'Select a Rule Type.'
    1. If you're using Excel 2003, set 'condition 1' to "Formula is."
  6. How to Highlight Every Other Row in Excel Picture 7
    Enter the formula to highlight alternating rows. Type the following formula into the typing area:[2]
    1. =MOD(ROW(),2)=0
  7. How to Highlight Every Other Row in Excel Picture 8
    Click Format. It's a button on the dialog box.
  8. How to Highlight Every Other Row in Excel Picture 9
    Click the Fill tab. It's at the top of the dialog box.
  9. How to Highlight Every Other Row in Excel Picture 10
    Select a pattern or color for the shaded rows and click OK. You'll see a preview of the color below the formula.
  10. How to Highlight Every Other Row in Excel Picture 11
    Click OK. This highlights alternating rows in the spreadsheet with the color or pattern you selected.
    1. You can edit your formula or formatting by clicking the arrow next to Conditional Formatting (on the Home tab), selecting Manage Rules, and then selecting the rule.
Method 2 of 3:

Using Conditional Formatting on Mac

  1. How to Highlight Every Other Row in Excel Picture 12
    Open the spreadsheet you want to edit in Excel. You can usually do this by double-clicking the file on your Mac.
  2. How to Highlight Every Other Row in Excel Picture 13
    Select the cells you want to format. Click and drag the mouse to select all the cells in the range you want to edit.
    1. If you want to highlight every other row in the entire document, press Command+A on your keyboard. This will select all the cells in your spreadsheet.
  3. How to Highlight Every Other Row in Excel Picture 14
    Click the
    How to Highlight Every Other Row in Excel Picture 15
    icon next to "Conditional Formatting." You can find this button on the Home toolbar at the top of your spreadsheet. It will expand your formatting options.
  4. How to Highlight Every Other Row in Excel Picture 16
    Click New Rule on the Conditional Formatting menu. This will open your formatting options in a new dialogue box, titled "New Formatting Rule."
  5. How to Highlight Every Other Row in Excel Picture 17
    Select Classic next to Style. Click the Style drop-down in the pop-up window, and select Classic at the bottom of the menu.
  6. How to Highlight Every Other Row in Excel Picture 18
    Select Use a formula to determine which cells to format under Style. Click the drop-down below the Style option, and select the Use a formula option to customize your formatting with a formula.
  7. How to Highlight Every Other Row in Excel Picture 19
    Enter the formula to highlight alternating rows. Click the formula field in the New Formatting Rule window, and type the following formula: [3]
    1. =MOD(ROW(),2)=0
  8. How to Highlight Every Other Row in Excel Picture 20
    Click the drop-down next to Format with. This option is located below the formula field at the bottom. It will expand your formatting options on a drop-down.
    1. The formatting you select here will be applied to every other row in the selected area.
  9. How to Highlight Every Other Row in Excel Picture 21
    Select a formatting option on the "Format with" drop-down. You can click an option here, and preview it on the right-hand side of the pop-up window.
    1. If you want to manually create a new highlight format with different color, click the custom format... option at the bottom. It will open a new window, and allow you to manually select fonts, borders and colors to use.
  10. How to Highlight Every Other Row in Excel Picture 22
    Click OK. This will apply your custom formatting, and highlight every other row in the selected area on your spreadsheet.
    1. You can edit the rule at any time by clicking the arrow next to Conditional Formatting (on the Home tab), selecting Manage Rules, and then selecting the rule.
Method 3 of 3:

Using a Table Style

  1. How to Highlight Every Other Row in Excel Picture 23
    Open the spreadsheet you want to edit in Excel. You can usually do this by double-clicking the file on your PC or Mac.
    1. Use this method if you want to add your data to an browsable table in addition to highlighting every other row.
    2. You should only use this method only if you won't need to edit the data in the table after applying the style.
  2. How to Highlight Every Other Row in Excel Picture 24
    Select the cells you want to add to the table. Click and drag the mouse so that all cells in the range you want to style are highlighted.[4]
  3. How to Highlight Every Other Row in Excel Picture 25
    Click Format as Table. It's on the Home tab on the toolbar that runs along the top of the app.[5]
  4. How to Highlight Every Other Row in Excel Picture 26
    Select a table style. Scroll through the options in the Light, Medium, and Dark groups, then click the one you want to use.
  5. How to Highlight Every Other Row in Excel Picture 27
    Click OK. This applies the style to the selected data.
    1. You can edit the style of the table by selecting or deselecting preferences in the 'Table Style Options' panel on the toolbar. If you don't see this panel, click any cell in the table and it should appear.
    2. If you want to convert the table back to a regular range of cells so you can edit the data, click the table to bring up the table tools in the toolbar, click the Design tab, then click Convert to Range.
5 ★ | 1 Vote

May be interested

  • How to Sort Cells Alphabetically in ExcelHow to Sort Cells Alphabetically in Excel
    excel is a powerful spreadsheet tool used to store and manage text or figures. alphabetical sorting is one of excel's useful features with the ability to help you sort, access and reference data quickly. to sort cells in excel alphabetically, simply double-click by highlighting the range of cells to sort, then click the 'az sort' or 'za sort' icon in the bar. standard tool. to sort cells alphabetically in excel using the advanced sort option, highlight the entire worksheet, click the 'sort' option from the 'data' menu. , then select the column and order you want to sort in the dialog box that appears.
  • How to copy and paste multiple highlight text at once in WordHow to copy and paste multiple highlight text at once in Word
    if you are reading a long document in word and marking important sections, you can quickly and easily find and select all highlighted text and copy them.
  • How to format data based on other cell conditions in ExcelHow to format data based on other cell conditions in Excel
    conditional formatting in excel makes it easier to categorize or highlight data, based on the conditions you set.
  • How to strikethrough text in Word, write strikethrough text in Word and ExcelHow to strikethrough text in Word, write strikethrough text in Word and Excel
    strikethrough in word and excel helps you highlight the text you want to edit or replace. the following article will guide you on how to create strikethrough text in word and excel and remove strikethrough text in existing documents.
  • Get started with Excel for beginnersGet started with Excel for beginners
    excel plays an important role in many areas. you work with computers and often use excel. are you still wondering and how to use excel?
  • Tips for adding in Excel you need to knowTips for adding in Excel you need to know
    cong is one of the basic excel operations.
  • How to fix the SUM function doesn't add up in ExcelHow to fix the SUM function doesn't add up in Excel
    in the process of summing with sum in excel, you will encounter some errors such as not jumping the number, not adding the sum. so how to handle this problem?
  • Guidance on how to align Excel correctlyGuidance on how to align Excel correctly
    many acquaintances manually align excel margins, adjust alignment margins, and forget how to set alignment in excel. in this article, network administrator will show you how to align standard in excel.
  • How to keep Excel and Excel columns fixed?How to keep Excel and Excel columns fixed?
    fixed excel box, fixed row, fixed excel column when scrolling mouse makes it easy to track and manipulate data sheet. in addition, you can split the excel view area to edit on individual excel areas.
  • What is ### error in Excel? how to fix ### error in ExcelWhat is ### error in Excel? how to fix ### error in Excel
    ### in excel is an error that excel frequently encounters and appears in many different cases when we enter data in excel, functions in excel, enter formulas in excel or when displaying results.