How to highlight alternate rows in MS Excel

Reading through massive amounts of data in Microsoft Excel can get extremely confusing. Ever been the type to hover your mouse over a row while dragging it to the side, just so you won’t get lost while looking through your spreadsheet?

Well, instead of having to put up a ruler on your screen, you can figure out how to highlight every other row in MS Excel instead. 

Now, if you can excuse the random shopping list, the steps to highlight every other row in MS Excel are relatively simple. 

How to Highlight Every Other Row in MS Excel

  • Using a custom formula to highlight every other row

We’ll go over the detailed steps throughout the guide below, so you can finally arrange your Excel sheet better.

At the same time, we’ll go over formulas and conditional formatting if you’re interested in figuring that out.

You’ll find these sections at the end of the article. Furthermore, we’ll also teach you how you can completely remove or clear highlighted areas rows. Finally, you can do more than just highlight, but also format the font color, size, cell border, and so on. 

All of this will be discussed below, so keep an eye and your Excel sheet ready!

If you’re feeling hesitant about doing this because you’re bad with formulas, don’t worry.

This guide was made with beginners in mind, so hopefully, the steps should be easy to follow. 

Let’s get started!



Why Should I Highlight Every Other Row in MS Excel?

When you have an Excel sheet that’s completely full of products and product details, it’s easy to get lost when you’re trying to single one out. Without even realizing it, you might already be reading the product details of another product because of the arrangement. If you’ve read through a lot of spreadsheets, you’ll find that this mistake happens everywhere.

This mistake is very common with all sorts of spreadsheets, from product details, employee lists, and so on. With anything that usually has a long row full of data and numbers, you can bet that making mistakes will inevitably happen. That’s why highlighting every other row is a useful skill to learn!

By highlighting every other row, your eyes will be guided by the difference in background color as you go through the spreadsheet. Furthermore, you can even customize formulas to format every other row in different ways. For example, you can set every other row to italicize, bold, or display different borders instead!

There’s a lot of customizability to this, and we’ll go through all of that at the very end. For now, here’s how you can highlight every other row in MS Excel.


Method 1: Conditional Formatting through A Formula

To highlight every other row, we’ll need to create a custom conditional formatting rule. This will involve inputting out a formula and setting the custom format for your cells. 

Here’s how you can do that!

Step 1: Open up your Microsoft Excel file.

Before we begin, start by opening up your Excel file so you can follow through with the tutorial on your own screen. You can either make one entirely from scratch, or use your own personal Excel sheet that already needs to be highlighted at every other row. If you’re worried about making a mistake, don’t be. Removing the formatting is easy to do and can even be reverted by pressing Ctrl + Z

Step 2: Select the cells that you want the conditional formatting to apply to.

With your Excel file open, go ahead and select the cells where you want every other row to be highlighted. Simply left-click and drag your mouse across the rows and columns to create a selection. You can also do this by clicking on any row and highlighting the rest by pressing the Shift + Arrow keys

Step 3: Go to the Conditional Formatting tab and select ‘More Rules.’

From there, under the Home tab, click on the Conditional Formatting button near the center and top part of Microsoft Excel. Now, hover over Highlight Cells Rules and from the drop-down menu, click on More Rules…

Step 4: Select the custom formula Rule Type and input the formula.

A window, New Formatting Rule, should open up on your screen. Under Rule Type:, select Use a formula to determine which cells to format. After that, you should be able to input the formula that will highlight every other row in your Excel file. Under Format values where this formula is true:, type in:

=MOD(ROW(),2)=1

Where:

= MOD() – an Excel function that returns the remainder of two numbers after being divided

= ROW() – an Excel function that returns the number of the row specified in the formula. In this case, ROW() is empty, so it returns the row number where ROW() is present. 

2 – refers to the divisor that is used in the MOD() function.

= 1 – the operation that determines whether a row is highlighted or skipped. If the remainder is equal to 1 (=1), then that row will be highlighted. 

The window should look like the image below:

Step 5: Highlighting the cells with the color of your choice.

Before you click on OK, you have to figure out the format of the cells you’ve been highlighting. Simply click on Format… and you should be brought to a new window, with multiple tabs. From there, go to the Fill tab and click on a color of your choice. 

Once you’ve picked out a color, click on OK and you should see by the Preview: box an example of the format you’ve chosen. 

From there, simply click on OK and your cells should now be highlighted at every other row! 

Congratulations! You’ve successfully learned how to highlight every other row in MS Excel. With that being said, if you aren’t happy with the color you chose, then here’s how you can edit the conditional formatting rules you’ve already made.


Editing or Deleting Conditional Formatting Rules

Once you’ve formatted and finalized the conditional formatting rules, finding them again can be confusing. In this method, we’ll show you where you can find all the active conditional formatting rules on your Excel sheet, as well as teach you how to edit them.

Step 1: With your Excel file open, navigate to Manage Rules.

Before anything, you’ll want to start with opening an Excel file. You can either use the same Excel file from the previous method, or one where a conditional formatting rule is already active. Once that’s done, click on Conditional Formatting. You’ll find this at the top and center portion of Microsoft Excel. From there, click on Manage Rules

Step 2: Show formatting rules for this worksheet.

A window should pop up on your screen. From there, open up the drop-down menu beside the Show formatting rules for: section and select This Worksheet. That should display all active formatting rules on your worksheet. 

Step 3: Editing Or Deleting the Conditional Format rules.

From the window on your screen, look for the formatting rule you want to edit and highlight it by selecting the rule. If you want to delete the selected rule, simply click on Delete Rule near the top left corner of the window. 

Otherwise, simply double-click the rule to edit the formatting rule. You can also select the rule and click on Edit Rule for the same effect. 

Simply click on the Format button to change the format or select a new rule type if you want to redo the rule. With that being said, congratulations! You’ve successfully edited and configured a formatting rule on your spreadsheet in MS Excel!


Conclusion

That about wraps up this entire article! Hopefully, we’ve helped guide you through the steps of highlighting every other row in MS Excel. Now, here’s everything we went through today in a little summary.

Highlighting every other row in MS Excel is a useful skill to have, especially when you have a spreadsheet that’s full of numbers. By highlighting alternate rows, you’ll be able to look through data easier without getting confusing yourself on the position of the rows.

We hope you found this tutorial useful!

Leave a Comment