In this tutorial, we’ll explain how to highlight every other row in Excel. We’ll show you how to improve the readability of your spreadsheet and shade alternate rows.
Working with large data tables and organizing ranges in Excel, sometimes not a too easy task.
In this Excel guide, I will cover the following topics:
As first, you’ll learn how to highlight every other row using conditional formatting. After that, we’ll introduce the excel-table based solution. Finally, we’ll write a short macro to get the work even faster for shading every other row.
Table of Contents
- 1 How to highlight every other row in Excel using Conditional Formatting?
- 2 Formatting Excel Tables for highlighting rows
- 3 VBA macro to Highlight Every Other Row in Excel
How to highlight every other row in Excel using Conditional Formatting?
Frequently you are facing the problem: It would be great to highlight rows in Excel! Conditional Formatting is a versatile tool for working colors, so we’ll use it in this example.
Example 1: Shade alternate rows using ISODD function
Here is our raw data table as you can see in the image below:
In this example, you are shading every other row in a range (for example D3: F17) in this data set.
You want quickly highlight every alternate row in Excel. To do this follow these steps:
1. Select the range which contains data.
2. Save your time using keyboard shortcuts! Use the Alt + O + D shortcut to open the conditional formatting rules manager window.
If you want to use the usual steps, click on the Home tab, select Conditional Formatting, and choose New Rule.
3. Apply a custom formula! As first, select the ‘Use a Formula to determine which cells to format’ option in the ‘New Formatting Rule’ dialogue box.
4. Now apply the formula to a range of cells! Select the ‘Edit the Rule Description’ field, and type the formula:
5. Click on the Format button and choose the fill color as the format.
6. Click OK. That’s it! You have the alternate rows highlighted.
Example 2: Combine ISEVEN() and ISODD() functions to apply unique Zebra Stripes
Add a second rule to Conditional Formatting Rules Manager. In this case, you can create unique styles based on two colors.
Use the formula below:
Both methods are simple. If you run a nested formula using ISEVEN and ROW, the expression will return TRUE if the number is even. The result will be FALSE is the row number is odd. This method highlights the 1st, 3rd, 5th, (and so on) row in a range.
Apply the ISODD and ROW combination. The result of the formula will TRUE if the row number is odd. Otherwise, you’ll get FALSE as a result of the expression.
Example 3: Highlight every other row using the MOD function
It’s worth knowing that MOD function can be useful if you are working with colors and rows.
You have to replace the formula in the Rules Manager.
expression and click OK.
Explanation of the MOD formula
Use the formula to get the remainder from the division. You can apply the combination of MOD and ROW functions to create a custom formula.
In this section, we’ll explain how the MOD(ROW(),2)=1 formula works. Check the first cell in a range. Because the ROW function returns 3, the
will be compiled into
MOD(3,2) = 1
You are waiting for 1 to shading the row. The first row highlighted because the result meets our initial criteria.
Go to the next row. If you are using the formula for D4, the =MOD(4,2) expression will get 0. It does not meet our criteria, and the row remains unchanged.
Example 4: Shading every nth row or column
In some cases, you want to highlight, for example, every 5th row. If you change the parameters of the ROW formula, you can work without limits.
Examples to alternate every nth row:
To highlight every third row in Excel us the formula below:
=MOD(ROW)),3 = 0
Tip: To shade every fourth row enter the =MOD(ROW(),4)=1 formula.
Formatting Excel Tables for highlighting rows
Let us see the lightspeed solution for shading every other row using Excel tables.
Example 1: Using built-in table styles
- Select the range which contains data.
- Choose the Home tab and click to the Format as Table
- Click on the predefined styles window and choose one of them.
Example 2: Create your custom style for alternate shading rows
The first two steps are the same as above.Click on the ‘New Table Style.’
1. Click on the ‘New Table Style.’
2. On the ‘New Table Style’ dialogue box, select ‘First Row Stripe.’
3. Click Format and select the color for shading. The preview window will show your chosen color scheme. If you want to apply this style frequently, you have to click to ‘Set as default table style for this document.’
4. Click OK.
You can modify the stripe size using the drop-down menu:
VBA macro to Highlight Every Other Row in Excel
You are on the right track. Now, you’ll learn the fastest way.
To insert a macro open Visual Basic Editor, on the Developer tab, and click Visual Basic. If you prefer keyboard shortcuts, press Alt + F11 to open the VBE window.
- Right-click on the workbook name.
- Select Insert -> Module from the context menu.
- Copy and paste the code to the right pane.
To start the macro, do these steps:
- Go to Developer Tab (if it does not appear, enable it)
- Click Macros
- The Macro dialogue box appears. Select the macro and click Run.
Don’t forget to check the source code too:
Sub ShadeEveryOtherRow() Dim Counter As Integer 'For every row in the current selection... For Counter = 1 To Selection.Rows.Count 'If the row is an odd number (within the selection)... If Counter Mod 2 = 1 Then 'Set the cell color to grey Selection.Rows(Counter).Interior.Color = RGB(200, 200, 200) End If Next End Sub
You may also be interested in:
- VBA macro collection
- How to sort by color in Excel
- Sorting Data – Tips and Tricks
- Data cleaning in Excel
- Compare two columns