Learn how to fill every other row in Excel by following along with the tried and tested methods mentioned in this tutorial.

The default white background truly makes it difficult to focus on row data especially when the sheet is filled with endless data. To overcome this, you might want to highlight every other row in your Excel worksheet to distinguish alternative rows. Color-coded rows enhance the readability of your dataset and give it a professional appearance as well.

There are a few approaches you can try to fill color in alternative rows of a worksheet. Below, I’ve listed various methods involving manual editing, semi-automatic approaches, and fully automated techniques. Let’s get started!

📒 Read More: 8 Ways To Group Rows in Microsoft Excel

Using the Ctrl Key

This method is suitable when you rarely need to highlight alternate rows in a worksheet for a small dataset. It’ll save a lot of time compared to other methods because you don’t need to go through additional steps to set up a formula or macro.

Select the first row after the header row in your dataset using the mouse. Alternatively, you can use the Shift + Right Arrow key movements to highlight it. For example, it could be row 2.

Now, press the Ctrl key to enable non-contiguous row selection moves in Excel.

This time, use the mouse cursor to select row 4 and expand the selection to the right side as much as you need.

Now, repeat these steps to select all alternative rows until the end of the dataset.

Don’t let go of the Ctrl key in between.

Once you’re done with selecting non-contiguous rows in your worksheet, release the Ctrl key.

Go to the Font commands block in the Home tab and click on the Fill Color drop-down arrow.

Choose a color you like from the list of Theme Colors.

If you’re not satisfied with the default colors in the Fill Color menu, you can click the More Colors option in the context menu to pick a unique color from the color wheel.

As soon as you select a color, Excel will fill every other row with that shade.

📒 Read More: How To Insert Every Nth Row

Using the Fill Handle

You can use this technique in Microsoft Excel 2016 and newer Excel desktop apps. A drawback of this approach is if you try this on a dataset, it’ll change the values as well. So, it’s best suited for formatting your blank worksheets when data isn’t available yet.

Go to the target worksheet and select a row for highlight with the Fill Color command.

Now, select a color from the Fill Color context menu or More Colors menu.

Select the color-filled row as well as the blank row just beneath it.

A tiny solid green rectangle will show up in the bottom right corner of the range selection silhouette. That’s the Fill Handle.

Click on that and drag it down until the row which you want to highlight with a cell color.

As you drag the Fill Handle down, Excel will start highlighting the alternate rows.

📒 Read More: 8 Ways To Group Rows in Microsoft Excel

Using the Format Painter Command

If you’re using Excel 2016 or newer edition software, you can use the Format Painter tool to highlight every other row by copying a specific color from the source row. This method will copy other cell formatting elements as well so watch out for those changes as well.

Select and highlight the first row of the dataset with a desired background fill color.

Now, double-click on the Format Painter button. You should find it in the Clipboard commands block of the Home tab.

You’ll see that the selected row is showing a moving dashed line. This confirms the successful activation of the Format Painter tool for multiple-row formatting.

Now, simply select the first cell of the next row to be highlighted. For example, if you’ve selected row 2 as the source, the alternate row would be row 4.

Repeat the same for the next rows.

Once done filling every other row, simply press Ctrl + S to save the workbook and deactivate the Format Painter tool.

Using a Conditional Formatting Rule

You can also use Conditional Formatting, a semi-automatic approach, to fill alternative rows in your dataset. A great advantage of this method is the dataset automatically adjusts the alternative fill color structure when you delete or add new rows within the range where the rule has been applied.

Navigate to your worksheet dataset and select the cell range where you want to enforce alternate row background color filling.

Click on the Conditional Formatting drop-down arrow and select the New Rule option in the context menu.

Select the Use a formula to determine… option inside the New Formatting Rule dialog box.

Enter the following Excel formula into the Format values where this formula is true: field.

=MOD(ROW(),2)=0

Now, click the Format button to access the Format Cells dialog box.

Use it to implement custom formatting for your cells in the tabs, like Number, Font, and Border.

Now, navigate to the Fill tab and choose a color from the Background Color list. Excel will use this shade to highlight alternate rows in the dataset.

Click OK to save the Format Cells customizations.

Now, select OK on the New Formatting Rule dialog box. Ensure the Preview box shows the formatting you’ve created in the Format Cells dialog.

Microsoft Excel will instantly fill the alternative rows.

Suppose, you’ve added a new row within the boundaries of this data set.

Excel will automatically modify the Conditional Formatting rule to appropriately fill every other row taking into account the new row.

If you’d like to highlight every other row starting from the odd row, use the following formula when creating the Conditional Formatting rule:

=MOD(ROW(),2)=1

A drawback of this method is it’ll erase all previous cell formatting if you don’t add those when configuring the Conditional Formatting rule.

Using the Table Style Tool

This method involves converting your dataset into a table. If you’re okay with this transformation, follow along with the steps outlined below.

Select the entire dataset including the column header row.

Click on the Format as Table drop-down menu in the Styles command block of the Home tab.

Select a Table Style from the menu that follows. You can choose a style from the Light, Medium, and Dark themes that matches your alternative row-fill color requirement.

Click OK on the Create Table dialog box.

Excel will convert the dataset to a table and fill alternate rows according to the color selection of the Table Style you’ve chosen.

Using the Modify Table Style Tool

If you need to fill every nth row, like applying a fill color for a group of two rows, keeping the next row blank, and so on, this method will come in handy.

First, select the target dataset and open the Table Style menu by following the steps mentioned in the previous method.

Right-click on a Table Style that includes an alternate row fill color theme. For example, the Table Style Medium 3.

Select the Duplicate option from the context menu.

The Modify Table Style dialog box will open.

Select the First Row Stripe option inside the Table Element section.

Change the Stripe Size to 2, 3, etc.

Click OK to save the Table Style modification.

The new modified Table Style will show up at the top of the Format as Table dialog, inside the Custom group.

Click on that and select OK on the Create Table dialog box.

Excel will fill every other two rows in the selected cell range and also convert the range to an Excel table object.

Using Excel VBA

If you’re looking for a fully automated method to fill every other row, you can create a VBA macro using the Excel VBA Editor tool.

Before you can begin building the macro, learn how to do this by following this effortless Excel VBA tutorial:

📒 Read More: How To Use The VBA Code You Find Online

Now, use the following script to create the macro:

Sub HighlightAlternateRows()
    Dim rng As Range
    Dim colorRows As Integer
    Dim defaultRows As Integer
    Dim fillColor As Long
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim colorR As Integer
    Dim colorG As Integer
    Dim colorB As Integer
    
    ' Prompt to select the cell range
    On Error Resume Next
    Set rng = Application.InputBox("Select the cell range for alternative row fill coloring:", Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    
    ' Prompt to enter the number of rows to color
    colorRows = Application.InputBox("Enter the number of rows to color:", Type:=1)
    If colorRows <= 0 Then Exit Sub
    
    ' Prompt to enter the number of rows to keep in default background color
    defaultRows = Application.InputBox("Enter the number of rows to keep in default background color:", Type:=1)
    If defaultRows < 0 Then Exit Sub
    
    ' Prompt to enter the fill color in RGB format
    colorR = Application.InputBox("Enter the Red component of the fill color (0-255):", Type:=1)
    colorG = Application.InputBox("Enter the Green component of the fill color (0-255):", Type:=1)
    colorB = Application.InputBox("Enter the Blue component of the fill color (0-255):", Type:=1)
    fillColor = RGB(colorR, colorG, colorB)
    
    ' Apply the coloring pattern
    For i = 1 To rng.Rows.Count Step (colorRows + defaultRows)
        For j = 0 To colorRows - 1
            If i + j <= rng.Rows.Count Then
                rng.Rows(i + j).Interior.Color = fillColor
            End If
        Next j
    Next i
End Sub

Press Alt + F8 to get the Macro dialog box. There, select the HighlightAlternateRows macro and hit the Run button.

The VBA macro will run and show the following dialog boxes so you can personalize the result according to your choice:

• An input box so you can select the cell range.

• A dialog box to enter the number of rows to be filled with a color.

• Another input box asks you to indicate the number of rows to be left blank.

• Three dialog boxes so you can enter the RGB code for the row-fill color.

Once you enter all the details, Excel fills every other row with the selected cell background color.

Using Office Scripts

If you’re using Excel for Microsoft 365 desktop or web app, you can use Office Scripts to automate the process. Office Scripts also allows you to trigger automated Excel processes remotely using Power Automate.

Go to the source worksheet and click on the New Script command in the Scripting Tools block of the Automate tab.

The Office Scripts Code Editor interface will show up on the right side.

Select any previous script and hit Delete.

Now, copy and paste the following script into Code Editor:

function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();
  let range = sheet.getRange("A2:F11");
  let fillColor = rgbToHex(169, 213, 253);

  let rowCount = range.getRowCount();
  let columnCount = range.getColumnCount();

  for (let i = 0; i < rowCount; i += 2) {
    let rowRange = range.getCell(i, 0).getResizedRange(0, columnCount - 1);
    rowRange.getFormat().getFill().setColor(fillColor);
  }
}

function rgbToHex(r: number, g: number, b: number): string {
  return "#" + ((1 << 24) + (r << 16) + (g << 8) + b).toString(16).slice(1).toUpperCase();
}

Click Save script to save the code.

Hit the Run button to execute the script.

You’ll see that Excel has highlighted every other row in the selected cell range.

By default, the script will use the cell range A2:F11 for formatting with a color code in RGB 169, 213, 253.

To personalize the input cell range and color code, edit the following part of the Office Scripts code:

let range = sheet.getRange("A2:F11");
  let fillColor = rgbToHex(169, 213, 253);

Conclusions

So far, you’ve learned how to fill every other row in Excel using various techniques, involving manual editing, Conditional Formatting, Excel Table object, VBA, and Office Scripts.

Try and practice one or a few methods outlined above depending on your level of Excel expertise.

If the article helped you to learn a new Excel skill or you’d like to share feedback, comment below.