Easy Ways to Shade or Highlight Every Other Row in Excel

In Excel, things like cells and cell ranges can be changed or altered to make them different from the rest of the worksheet.

These options which are available in Excel can enable you to do things like change the color and style of a worksheet.

While working, you might come to a point when you want to shade in different parts of the document to make values or data stand out.

Or perhaps you want to Shade or Highlight Every Other Row in Excel to make it easier to read and follow.

As such, you will need an effective solution that will allow you to get the desired highlighting in your document.

Contents:

Here’s how to Shade or Highlight Every Other Row in Excel:

Applying Conditional Formatting to Highlight Every Other Row in Excel

Conditional formatting is a feature in Excel that can be implemented for a variety of different tasks.

It can make visualizing, finding, and locating information, values and pieces of data much easier.

This can be very useful if you need a way to make certain data more apparent, while leaving cells which contain other data unchanged.

Conditional formatting allows you to alter how a cell looks depending on a condition that you define for it.

For example, when conditional formatting is applied to a spreadsheet in a certain way, the cells that contain certain data or values will be formatted or changed in a specific way – whereas other cells which doesn’t contain the specified data will not be formatted.

Conditional formatting is also something that you can use to shade or highlight every other row in Excel.

This method can be used for either rows and/or columns – depending on the style that you seek to add to your spreadsheet.

To use conditional formatting to do shade alternate rows in Excel, follow these easy steps below:

Choose whether you want alternate shading for the whole spreadsheet or only for a certain range of cells, then select the desired cells. If you want alternate shading for a range of cells, click and drag to select the cells that you wish to be shaded.

If you want alternate shading for the entire spreadsheet, click ‘Select All’ or press ‘CTRL + A’ on the keyboard to select it.

Make sure to select all of the cells that you want to be highlighted so that you aren’t missing anything.

Click on ‘Home’ in the ribbon menu, then go to ‘Conditional Formatting’. Next, you should see the New Formatting Rule dialog box. This step is important, as it will show you the options necessary to format the rows as desired.
Under ‘Select a Rule Type’, select the option ‘Use a formula to determine which cells to format’. This step will allow you to use and apply a formula to get the rows highlighted.
Under ‘Edit the Rule Description:’, within the box type the formula =MOD(ROW(),2)=0 . This should allow you to apply alternating color to rows.

Note: To apply alternating color to columns instead of rows, use this formula in place of the formula in the current step: MOD(COLUMN(), 2)=0

Next, click on ‘Format’. This step should open up a box with options that will allow you to format the cells which you have selected from the first step.
Within the ‘Format Cells’ box, select and press the ‘Fill’ option. After doing this, different options for colors should now appear and be available for you to select.
Choose the color that you want, then press ‘OK’. The color that you want the rows to be should now be selected and ready to be applied to the spreadsheet.
Look over the selection within ‘Sample’, then make any changes if needed or press ‘OK’ if the preview looks alright.

Note: If for any reason you would like to remove conditional formatting from any cells, you can choose and select the cells which you wish to have cleared by pressing ‘Home’ in the menu, going to ‘Clear’, then selecting ‘Clear Formats’.

Once you have used conditional formatting as specified, every other row should become shaded or highlighted as expected.

Table Format Method

Another option which can be useful for coloring rows or columns in Excel are table formats.

Table formats in Excel can make it a lot easier to see data in Excel.

A table format can change the appearance of cells quickly and efficiently.

It can turn a spreadsheet with information that is hard to find into an easily scannable document.

These formats can be already set and ready for use in order to get different coloring in alternate rows.

This technique is also one of the quickest ways to achieve alternate coloring of a spreadsheet.

With this method, you will use a table format which is already preset and apply it in order to shade in every other row.

You could also use the table format method for columns if you wish as well.

To use table formatting to shade every other row in Excel:

Click and drag to choose the range of cells that you wish to have alternate coloring or shading. Be sure to have all of the cells that you want to be formatted selected in this step.
At the top of the ribbon menu, click on ‘Home’, then click on ‘Format as Table’. This should bring up options that will allow you to pick the look or design of the table that you want to appear in your spreadsheet.
Choose a style for the table that you wish to create. This style should have the alternating coloring for rows that you want.
Click OK or Apply to apply the changes. A table should appear within the spreadsheet after this.

Note: If you want to shade columns instead of rows, after the previous step, click on the table to select it, then click on ‘Design’ in the menu. Next, click on the box next to ‘Banded Rows’ in order to remove the check mark next to it and deselect this option. Then click the check next to ‘Banded Columns’ to select it. This should make the shading appear in columns instead of rows. Only do this if you wish the columns to be shaded – not rows. If not and you want rows shaded, skip over this.

Upon getting through all of the steps of the table format method, you should see that every other row in Excel has been highlighted.

VBA Highlight Method

VBA is short for Visual Basic Applications.

This special tool is useful for programming and using code in Excel.

It can also be used to carry out an assortment of important tasks – including shading or highlighting every other row as well.

Amazingly, this technique is very simple and easy too – saving you time in the process.

To apply VBA code and add color to alternating rows:

Choose, click, and drag in order to select the cells that you want to have alternating shading or color. Select as many cells as needed until you reach the desired amount for your worksheet.
In the menu, go to ‘Developer’, then click on ‘Visual Basic’. A VBA window should now open appear.
Press ‘Insert’, then proceed to ‘Module’, then copy and paste the code that shades rows into the module.

An example of a code that can shade or highlight alternating rows is as follows:

Sub HighlightOrShadeAlternatingRows()

Dim Rng As Range

Set Rng = Selection

For i = Rng.Rows.Count To 1 Step -2

Set shadingRows = Rng.Rows(i)

shadingRows.Interior.Color = RGB(192, 192, 192)

Next i

End Sub

Copy the code above or another appropriate code which places alternating shading into the spreadsheet and paste it into the module window or you could type the code into the module if you desire.

The code above should give the rows a gray color.

Make sure to replace the color codes (within the parentheses next to “RGB”) with color codes that match the colors you want your rows to be.

Also, within the code, on the fourth line, you can replace to negative digit at the end of the code line so that the rows are shaded (i.e. -2 would make every second row shade, -3 would make every third row shaded and so on).

Run the code. This can be done by pressing the run button (the button with a small arrow on it) or by going to Developer, then Macros, select_alt_cells, then ‘Run’.

After the code has been run, you should see that every other cell has been highlighted or shaded.

Kutools Method to Add Shading to Every Other Row

Kutools is a very helpful tool that has many uses and can make working with functions a simpler process.

Kutools can also be useful for a variety of things in Excel – including shading or highlighting every other row.

Note: In order to use this method, you will need to have Kutools downloaded and installed. If you don’t have Kutools yet but want to try this technique, you can download it before starting. If you already have it installed or after installing Kutools, proceed onto the next steps:

Click and select all of the cells which you wish to add shading to.
In the menu, click on Kutools, ‘Format’, then ‘Alternate Row/Column Shading’. A dialog box should now appear.
In the dialog box, click on ‘Rows’. This will allow you to make the necessary changes to the rows that you have selected.
Select ‘Conditional formatting’. After doing this, you should see options for formatting the spreadsheet – including an option to change the color.
From the ‘Shade color’ options, pick the color that you want the rows to be.
Within the ‘Shade every’ section, type in the number for the alternating rows that you want to use (i.e. 1). This should allow you to specify which rows should be shaded.
Press ‘OK’ to complete the process.