Shade Alternate Rows In Excel

What Is Shade Alternate Rows In Excel?

Shade alternate rows in Excel is a way to highlight every other row in a spreadsheet in a chosen color and make the data more presentable and readable. Users can shade alternate rows in a worksheet when they must show Excel data, with a professional appeal, in PowerPoint presentations and business documents.

For example, the table below shows the units of an item sold per month.

And the requirement is to make the data more presentable by shading alternate rows in the dataset. Then, we can use the Conditional Formatting feature from the Home tab and apply the formula to shade alternate rows in Excel to achieve the desired outcome, as shown below.

In the above example, we must select the data range A2:B13 to choose the entire dataset, leaving the column headings. And then, we can access the New Rule option under the Conditional Formatting feature from the Home tab, which will open the New Formatting Rule window. Next, we can choose the last Rule Type to enter the formula to shade alternate rows in Excel and set the required format.

The formula determines the modulo of the current row number and 2. And if the modulo is more than 0 for a row, the corresponding row gets shaded in Green, the chosen format.

Finally, clicking OK will show the alternate rows in the chosen cell range in the specified format, Green shade.

What Is Shade Alternate Rows In Excel?

Shade alternate rows in Excel enables one to show every other row in a chosen cell range in the specified color. And hence, the data in the cell range appears more readable.
Users can shade alternate rows in a worksheet when they must use the Excel data in other software and programs for professional purposes.
We can shade alternate rows in a spreadsheet using the Conditional Formatting feature, with or without a helper column. Otherwise, we can use VBA coding.
Using the appropriate MOD and ROW functions-based expression as the condition in the Conditional Formatting feature makes shading alternate rows more straightforward.

How To Shade Alternate Rows In Excel?

We can utilize the following three methods to shade alternate rows in Excel:

Without A Helper Column (Using the Conditional Formatting Feature)
Using A Helper Column (Using the Conditional Formatting Feature)
Using VBA Coding

While the first two methods involve the Conditional Formatting feature, the third method uses a VBA code to automatically shade alternate rows in Excel.

Method #1 – Without Using Helper Column

The process to shade alternate rows in Excel without using a helper column is as follows:

To begin with, choose the data range without the column headings. Next, select the Home tab → choose the Conditional Formatting feature → choose the New Rule option to access the New Formatting Rule dialog box. Otherwise, access the window with the keyboard shortcut Alt + O + D.
Next, select the last Rule Type in the New Formatting Rule window to enter the formula that assesses the cells to shade.
Then, enter the applicable formula in the Edit the Rule Description section field in the New Formatting Rule window to shade the required alternate rows.

Now, we can utilize the following MOD and ROW functions-based formulas according to the requirement.

If the requirement is to shade every alternate even-numbered row. Then the applicable formula will be:

=MOD(ROW(),2)=0

However, if the requirement is to shade every alternate odd-numbered row, then the applicable formula will be:

=MOD(ROW(),2)=1

=MOD(ROW(),2)>0

Next, click the Format option in the New Formatting Rule dialog box to access the Format Cells dialog box.
Then, select the Fill tab in the Format Cells window to choose the shade to highlight the alternate rows in the output.
Now, click OK twice to close the Format Cells and New Formatting Rule windows and achieve the output with the required alternate rows shaded in the specified color.

Example

The table below shows the annual US population statistics and its growth rates from 2010-2023.

Shade Alternate Rows in Excel - Method 1

If the requirement is to shade alternate even-numbered rows in the given dataset. Then, we can utilize the applicable MOD and ROW functions-based expression in the Conditional Formatting feature to obtain the desired outcome.

Step 1: Choose the entire dataset, leaving the column headings. And then, choose the Conditional Formatting feature in the Home tab.

Step 2: Select the New Rule option under the Conditional Formatting feature.

The New Formatting Rule window opens.

Step 3: Choose the last Rule Type to apply the applicable formula to shade the required alternate rows.

Step 4: Enter the MOD and ROW functions-based expression as the condition to check for shading every alternate even-numbered row in the chosen dataset range. And then click Format to open the Format Cells dialog box.

Step 5: Choose the Fill tab in the Format Cells dialog box to select the color to shade the required alternate rows.

Clicking OK will close the Format Cells dialog box.

Finally, clicking OK in the New Formatting Rule dialog box will close it. And it will result in the following dataset, with every even-numbered alternate row shaded in the specified color.

Shade Alternate Rows in Excel - Method 1 - Step 5c

The formula entered in the New Formatting Rule window applies to the chosen cells in the range A2:C15.

And here is how the formula works in each row in the specified range. The ROW() determines the current row number. Next, the MOD() determines the modulo of the row number and the value 2. And if the modulo of the two values is 0, the condition holds. Then, the corresponding row gets shaded in the specified color. And if the modulo of the two values is not 0, the condition does not hold. And hence, the row remains unshaded.

Method #2 – Using Helper Column

The process to shade alternate rows in Excel without using a helper column is as follows:

Add a helper column containing values in a specific order at the beginning or end of the dataset.
Choose the data range without the column headings and the helper column. And select the Home tab à choose the Conditional Formatting feature → choose the New Rule option to access the New Formatting Rule dialog box.
Select the last Rule Type to enter the formula that assesses the cells to shade in the New Formatting Rule window.
Next, enter the applicable formula in the Edit the Rule Description section field in the New Formatting Rule window to shade the alternate rows.

We can apply the ISEVEN() when the requirement is to shade every alternate row, provided the helper column cell in the corresponding row contains an even number. And can apply the ISODD() when the requirement is to shade every alternate row, provided the helper column cell in the corresponding row contains an odd number.

And then, click the Format option in the New Formatting Rule window to access the Format Cells window.

Click the Fill tab in the Format Cells window to choose the shade to highlight the required alternate rows in the output.
Click OK twice to close the Format Cells and New Formatting Rule windows and achieve the output with the required alternate rows shaded in the specified color.

Example

The table below shows a list of laptop brands and their monthly sales at a store.

Shade Alternate Rows in Excel - Method 2

And the requirement is to use the Conditional Formatting feature with a helper column to automatically shade alternate rows in Excel. Assume we must shade alternate rows where the helper column cells contain even numbers. Then, here is how to use the Conditional Formatting feature with a helper column and achieve the required outcome.

Step 1: Add a helper column, Serial No., at the beginning of the dataset, containing the serial numbers for the laptop brands in ascending order.

Step 2: Choose the entire dataset, excluding the helper column and the column headings. And then, choose Home → Conditional Formatting.

Next, choose the New Rule option under the Conditional Formatting feature to access the New Formatting Rule dialog box.

Step 3: Select the bottom-most Rule Type in the New Formatting Rule window to apply the applicable formula to shade the required alternate rows.

And enter the ISEVEN() to shade the alternate rows where the serial numbers in the helper column are even.

=ISEVEN($A2)

Next, click Format to open the Format Cells dialog box.

Step 4: Choose the Fill tab in the Format Cells window to select the color to shade the required alternate rows.

Shade Alternate Rows in Excel - Method 2 - Step 4a

And clicking OK will close the window.

Finally, clicking OK in the New Formatting Rule window will close it. And it will result in the following dataset, with every alternate row shaded in the specified color, where the serial number in the helper column is even.

Shade Alternate Rows in Excel - Method 2 - Step 4c

The formula entered in the New Formatting Rule window applies to the chosen cells in the range B2:N11.

And here is how the formula works in each row in the specified range.

The cell reference supplied as the argument value to the ISEVEN() is mixed. While the column reference is absolute and the row reference is relative.

Thus, the column referenced in the ISEVEN() in every row in the chosen range remains the same. And the row referenced in the function varies with the corresponding row.

So, when the ISEVEN() executes in a row in the chosen range, it accepts the mixed reference to the helper column cell in that row. And if the helper column cell contains an even serial number, the ISEVEN() returns the value TRUE. And as the condition holds, the corresponding row gets shaded in the selected color. Otherwise, the row remains unshaded.

Method #3 – Using VBA Coding

VBA coding is another way to shade alternate rows in Excel, and here is how we can utilize this Excel feature:

With the source data in the active worksheet, press Alt + F11 to open the VBA Editor.
Choose the required VBAProject and select Insert → Module from the top menu to open a new module window.
Enter the VBA code in the module window to color the alternate rows in the required shade.
Click the Play icon in the top menu to run the code.
Open the current worksheet containing the source data to view the dataset with the alternate rows shaded in the specified color.

Example

The table below lists different grades of fruits and their order dates.

If the requirement is to display the above dataset with every alternate row shaded in Green. Then, we can use VBA coding to achieve the desired outcome.

Step 1: With the current spreadsheet open, press the keys Alt + F11 to access the VBA Editor.

Step 2: Choose the required VBAProject and select Module from the Insert tab in the top menu.

A new module window, Module1, will open.

Step 3: Enter the VBA code in the Module1 window to shade the alternate rows in the given dataset.

Sub shade_alternate_rows()

Dim dataRange As Range
Dim R1 As Long
Dim R2 As Long

R1 = RGB(255, 255, 255)
R2 = RGB(146, 198, 136)
Set dataRange = Range(“A2:C16”)

If dataRange.Rows.Count = 1 Then Exit Sub

For i = 1 To dataRange.Rows.Count
If i Mod 2 = 0 Then
dataRange.Rows(i).Interior.Color = R2
Else
dataRange.Rows(i).Interior.Color = R1
End If
Next i

End Sub

Step 4: Click the Play icon to execute the entered code.

Finally, when we open the active worksheet, we will find the dataset with its alternate rows shaded in the color we specified in the code.

Excel Shade Alternate Rows - Method 3 - Step 4b

The VBA code contains two Long variables, R1 and R2, with the color codes of the shades White and Green assigned to them.

Next, we enter a For loop, with the counter i value ranging from 1 to 15 rows in the specified data range A2:C16. And, for each value of the counter i, the For-loop condition checks if the modulo of the counter i value and 2 is 0 in the corresponding row.

So, if the specified condition holds in a row, the row gets shaded in Green. Otherwise, the row remains White or unshaded.

Important Things to Note

Ensure to exclude column headers during the dataset selection to shade alternate rows in Excel using the Conditional Formatting feature.Otherwise, the column headers may get shaded.
The MOD and ROW functions-based formula in the Conditional Formatting feature will vary based on whether to shade even or odd-numbered alternate rows.
Ensure the helper column contains values in a specific order to make them useful for shading the required alternate rows in Excel.

Frequently Asked Questions (FAQs)

1. How do you shade multiple rows that aren’t next to each other in Excel?

For example, the image shows a dataset containing a list of students, their grades and date of joining details.

The steps are:
To begin with, choose the entire dataset range, A1:C11, select Table > Insert.
Now, the Create Table window appears. Here, we can view the absolute reference to the chosen dataset range. Ensure the table headers check box remains checked, as the chosen dataset includes column headings.

Finally, click OK.

Thus, we see multiple rows not next to each other shaded in the chosen dataset.

2. How do I change the shading of a selected row in Excel?

With the required shaded row selected, choose the Fill Color option in the Home tab. Clicking the Fill Color option will show a color palette.

Click the color to apply and thus change the shading of the chosen row. Furthermore, if we want to apply a color other than the ones displayed, click the More Colors option to explore more color options.

3. How do I shade every other row in Excel for Mac?

The steps are:
1) First, choose the range of cells where we wish to shade every other row.
2) Next, choose Insert > Table option to open the Create Table window.
3) The Create Table window will show the absolute reference to the chosen cell range. And if our cell range contains column headings, keep the table headers option checked.
4) Finally, click OK.

Download Template

This article must be helpful to understand the Shade Alternate Rows In Excel, with its formula and examples. You can download the template here to use it instantly.

Shade Alternate Rows In Excel Download

Shade Alternate Rows In Excel