How to Headline Highlight Every Other Row in Excel – Fast and Easy

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Highlighting every other row makes it easier to read the contents of the spreadsheet.

Some people also highlight their sheets to give it a bit of flair.

In this article, you’ll learn three easy steps on how to highlight every other row in Excel.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get this started!

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

Manually shading alternate rows

Before going on with the other methods, let’s see first how to manually highlight each alternating rows in a spreadsheet.

To highlight alternating rows, all you have to do is click on the row headers of each alternating row.

Then, click on the ‘Fill Color’ button on the ribbon.

How to manually shade alternating rows in Excel

This results in:

How a manually shaded alternating rows look like

Yes, highlighting alternate rows is this easy. But the problem with this is when you have thousands of rows to highlight. Doing so would take hours not counting the times you misclick…

Kasper Langmann, Co-founder of Spreadsheeto

The following two methods are great ways to highlight alternating rows especially when you have a large data set.

Use conditional formatting

Conditional formatting is an awesome way to change the layout and design of your Excel sheet.

To use conditional formatting in highlighting every other row in a spreadsheet, you need two more functions — the ‘MOD’ and ‘ROW’ functions.

To give you an overview, the ‘MOD’ function allows you to find remainders with ease while the ‘ROW’ function gives you the row number of a specific cell.

Kasper Langmann, Co-founder of Spreadsheeto

To apply a highlight on every other row, you will need to apply the following formula:

=MOD(ROW(),2)=0

To start, select the range of alternating rows you would like to highlight.

In this case, let’s select all the cells in the spreadsheet by clicking on the ‘Select all’ icon or by pressing ‘Ctrl’ + ‘A’ in your keyboard.

How to select all rows in Excel

Then, click on the ‘Conditional Formatting’ icon on the ribbon and select ‘New Rule…’ from the options.

How to create a new conditional formatting rule in Excel

On the window, select the most bottom option, ‘Use a formula to determine which cells to format’.

Enter the formula on the form provided and select the format or the fill color of the highlight.

Click the ‘OK’ button once you’re ready to proceed.

How to use a formula to determine which cells to format

After that, you will find that every other row in the spreadsheet is highlighted, as you can see from the middle of the data set.

Alternating shaded rows even in the middle of the data set

Easy, right?

With that, let’s proceed with the last one.

Apply Excel table style

Most beginners would love the second method as it’s easy and fast.

Unfortunately, using conditional formatting to highlight every other row in the spreadsheet is only okay if you aren’t using any sorting and filtering.

Kasper Langmann, Co-founder of Spreadsheeto

Sorting and filtering will not affect your spreadsheet in any way when using a table style format.

To do so, all you have to do is click on the ‘Format as Table’ button and select your table style of choice.

How to use table styling to highlight alternating rows

You will then be asked about the range of your table and whether or not your table has headers.

By default, all the rows and columns that have data will be selected.

Click on the ‘OK’ button to proceed.

How to format a spreadsheet as a table

Immediately, every other row on your table will be highlighted.

If you check on the table design, you will see that the header row and banded rows are checked. The banded rows represent the highlight on every other row of your table.

To check the table style, click on the ‘Design’ tab while the table is selected.

Where to find the design tab in Excel

That’s it! With this method, your table highlight will not be affected by sorting and/or filtering. 😊

Wrapping things up…

Highlighting every other row in the spreadsheet doesn’t have to be hard. However, manually shading each and every row is a waste of time especially when you have hundreds of rows. Using conditional formatting is a good way to do it if you don’t need any sorting or filtering.

But the best way to do is by applying Excel table style which formats your data as a table. A table already has sorting and filtering available so having one will not change your styling.

Kasper Langmann, Co-founder of Spreadsheeto