Skip to content

How to shade alternate rows or columns in Excel?

  • by
  • 4 min read

Excel is a widely used spreadsheet platform, and like every other spreadsheet, we work in rows and columns where we can write our day-to-day activities as well as expenditures. Shading the rows and columns add extra aesthetics to the whole spreadsheet and makes the data clear.

Here we explain how you can shade every other row in Excel. We will explain two methods for the same — via the Conditional Formatting and the Format as Table option.

Method 1: Applying the shade via Conditional Formatting

Conditional Formatting option lets you apply colours, data sets and icons according to the cell number that you have entered.

To shade the alternate row or columns in Excel, you will have to use the formula =MOD(ROW(),2)=0) for shading the rows. If you want to shade the columns, the formula that you will have to enter will be =MOD(COLUMN(),2=0). 

Follow the step mentioned below to enter the said formula.

Step 1: Select the cells where you want the shading. Remember, select all the cells irrespective of the rows that you want to shade. To select the whole sheet, click on the downward arrow at the corner of the spreadsheet. Step 2: Now that you have selected the cells, click on Home > Conditional Formatting.

Step 3: From the drop-down menu, select the New Rule option.Step 4: Now, in the window that will open, select the Use a formula to determine which cells to format option under the Select a rule type heading. Step 5: Enter the formula =MOD(ROW(),2)=0) in the box below Format values where this formula is true. Step 6: After you have entered the formula, click on Format.Step 7: Go to the Fill tab and then select the shade of your choice. Here, I have selected red. When you are done, click on OK and the rows will appear somewhat like this. To remove the shadings, click on Conditional Formatting and then select the Clear Rules from the drop-down menu. Alternatively, you can press Ctrl+Z to reverse the changes that you have made.

Also read: How to add text box in Google Docs?

Method 2: Applying the shades via Format as Table option

Here are the steps to shade alternate rows and columns in Excel via the Format option.

Step 1: Click on Home and then click on Format as Table option in the Styles heading.Step 2: Select the format of the table that you want. You can select from Light, Medium and Dark options. Step 3: After you have selected the theme for your table, click on OK.Step 4: You will see that the rows are now coloured. To shade the column, click on the Branded Columns option from the top. Notice that the Branded Rows option has a tick by default. Also read: How to add a Trendline in Excel?

Kumar Hemant

Deputy Editor at Candid.Technology. Hemant writes at the intersection of tech and culture and has a keen interest in science, social issues and international relations. You can contact him here:

  • >
    Exit mobile version