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 SpreadsheetoLet’s get this started!
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.
This results in:
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 SpreadsheetoThe 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 SpreadsheetoTo 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.
Then, click on the ‘Conditional Formatting’ icon on the ribbon and select ‘New Rule…’ from the options.
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.
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.
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 SpreadsheetoSorting 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.
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.
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.
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