Use conditional formatting to format even and odd rows

By using conditional formatting, formatting even or odd rows becomes a simple task.

Network administration - Some users prefer to change the color of a row so that their spreadsheet becomes more readable, especially when there is a lot of data. However, sometimes the limitations can make the problem complicated or from the beginning you may think so. For example, you might think that changing colors for odd or even rows is more difficult than changing the color of the remaining rows, but you're wrong. By using conditional formatting, formatting even or odd rows becomes a simple task:

  1. To format only even rows, use the conditional formula = EVEN (ROW ()) = ROW ().
  2. To format only odd rows, use the following formula = ODD (ROW ()) = ROW ().

Here we will guide you through a sample example:

  1. Select the rows you want to format. To select the entire worksheet, click Sheet Selector (the gray box intersects between the row and the column headers).
  2. Click the Home tab.
  3. Click Conditional Formatting in the Styles group and select New Rule .
  4. From the Select A Rule Type list , select Use A Formula To Determine Which Cells To Format .
  5. In Format Values ​​Where This Formula Is True field , enter = EVEN (ROW ()) = ROW ().
  6. Click Format .
  7. Specify the format available. For example, to change to red for all even rows, click the Fill tab and click Red , then click OK twice.

Use conditional formatting to format even and odd rows Picture 1Use conditional formatting to format even and odd rows Picture 1


Use conditional formatting to format even and odd rows Picture 2Use conditional formatting to format even and odd rows Picture 2

Note that all even rows are now red. To change colors for individual rows, you can repeat the steps above. In step 4, enter the formula = ODD (ROW ()) = ROW (). In step 6, select the color you like, such as blue as in our example. You should note, this technique is not only for changing colors but also for general formats.

Use conditional formatting to format even and odd rows Picture 3Use conditional formatting to format even and odd rows Picture 3

4.6 ★ | 7 Vote