How To Select Every Other Row In Excel: 4 Excellent Methods

When your work centers around handling huge datasets on Excel, there are some functions and tools you should always be a master of. Knowing how to select every other row in Excel is one of those must-know things.

We understand that it gets confusing easily when you are trying to learn a new technique. This is why we are going to discuss four easy methods on how to select every other row in Excel. After going through this guide, you will feel that you can handle any task of selecting alternating rows in any sheet filled with data.

To make things easier for you as you progress through this article, we are also sharing the Excel file with you. You can examine every method in that file. You can also practice all the methods and decide which one is best for you.

Let’s begin!

How to Select Every Other Row in Excel in 4 Easy Ways

In this visual guide, we are sharing different techniques for you to complete the task of selecting every other row in an Excel sheet. Understandably, one may find a method easy while someone else would find it otherwise. This is why we are discussing the easiest four methods to do the job most effectively so that it is always “easy” for everyone.

We strongly suggest that as you read through every step in a method, you practice it simultaneously. It will help enforce your understanding of the techniques. You can open Excel and use your own dataset to practice. Or, you can simply download the practice file we are sharing with you.

However, we recommend using our practice file because it will be more helpful for you as they match the pictures used in this article. Let’s start.

Method 1: Conditional Formatting Using MOD Function

Using the MOD function is one of the best ways to select every other row in Excel. The MOD formula returns the remainder of a row number that is divided by 2 in this case. We are taking the help of Conditional Formatting to implement this technique.

1. Select all the cells where you want to define and select every other row. You can click the top-left cell, keep the mouse button pressed and drag it towards the bottom-right to select the intended data. Alternatively, you can click on the top-left cell first. Then, by pressing and holding the CTRL + SHIFT buttons, press the Right Arrow Key once to select all the columns with data and then Down Arrow Key once to select all the rows with data.

Conditional Formatting Using MOD Function 1

2. Click on Conditional Formatting in the Styles group under the Home ribbon. Click New Rule… from the drop-down menu.

Conditional Formatting Using MOD Function 2

3. A New Formatting Rule mini-window will appear. Click on the rule type Use a formula to determine which cell types to format. Below Edit the Rule Description, click on the formula box under Format values where this formula is true:

Conditional Formatting Using MOD Function 3

4. Now, write the mod formula =MOD(ROW(),2)=0 in the box. It will select every second or even row of your selection. If you want to select every alternating row starting from your first row of selection, use this =MOD(ROW(),2)=1 formula.

After writing the formula, click on the Format button.

Conditional Formatting Using MOD Function 4

5. A new Format Cells window will pop up. Click on the Fill tab and then choose a color for your selected rows. Next, click on the OK button.

Conditional Formatting Using MOD Function 5

6. You will be taken back to the previous mini-window. Click on the OK button again to complete the process.

Conditional Formatting Using MOD Function 6

7. You will see that your selected data has every alternating row colored with the choice you have made in the previous step. Do not deselect the data at this stage.

Conditional Formatting Using MOD Function 7

8. While the data is selected, go to the Data ribbon and click Filter in the Sort & Filter group. Instantly you will notice that there are small filter tabs in the first row of your selection.

Conditional Formatting Using MOD Function 8

9. Click on one of the filter tabs. When the menu opens, hover your mouse pointer over Sort by Color which will open Sort by Cell Color to one side. Click on the color that your rows are highlighted in.

Conditional Formatting Using MOD Function 9

10. You will see that only the highlighted rows are being shown. Do not deselect data just yet. Hit F5 on your keyboard to bring up the Go To window. Click on the Special tab.

Conditional Formatting Using MOD Function 10

11. You will now see a Go To Special mini-window. Click on the Visible cells only radio button and then click OK to complete the process.

Conditional Formatting Using MOD Function 11

12. All your alternate rows are now selected individually as you can see in the picture below. Now, the next step is the final one that makes it all come together!

Conditional Formatting Using MOD Function 12

13. While the data is still selected, click on one of the Filter tabs and then hover your mouse pointer over Sort by Color. Then, click on the color to remove the filtering rule. It will show all your rows again now. But this time, you will see that every other row is selected!

For the final touch, under the Data ribbon, click on Filter in the Sort & Filter group to remove filtering altogether.

Conditional Formatting Using MOD Function 13

Important Tip: To remove the highlights, you can follow three techniques based on the circumstances.

1. You can select your whole data range and then remove the formatting rules from Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

Conditional Formatting Using MOD Function 14

2. If your sheet does not have any other rules applied currently, simply remove the formatting rules from Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet as shown in the picture above.

3. The third technique is applicable if your sheet has other rules applied to them. So if you clear rules from the entire sheet, it will wreak havoc.

Conditional Formatting Using MOD Function 15

In this case, simply go to Conditional Formatting > Clear Rules > Manage Rules. Find the rule that you have applied in this method. Notice the rule and format (color of the highlights) to make sure that you find the right one. Click on the rule to select it, hit the Delete Rule button, and then click OK to finish the process.

Method 2: Selecting Every Other Row with Conditional Formatting Using Odd/Even Row Formula

In this method, we will show how you can select every other row in Excel using the ISODD or ISEVEN functions for the odd and even rows. We understand that a lot of the time, selecting each and every odd or even row is in the requirements. Method 1 follows a different technique from this one. So it is not possible to select rows based on their MOD formula order using that method.

This is why we are presenting you with this excellent way to select your rows.

1. First, select the cells. You can simply drag and select or use the keyboard shortcut CTRL + SHIFT + Right Arrow and Down Arrow to make a selection of the range of data.

Conditional Formatting Using Odd or Even Row Formula 1

2. Within the Style group under the Home ribbon, click on Conditional Formatting and select New Rule… from the drop-down menu.

Conditional Formatting Using Odd or Even Row Formula 2

3. In the New Formatting Rule window, click on the Use a formula to determine which cells to format. In the formula box, write down =ISODD(ROW()) for selecting odd rows or =ISEVEN(ROW()) for selecting even rows. Afterward, click the Format button.

Conditional Formatting Using Odd or Even Row Formula 3

4. In the popped-up Format Cells window, click on the Fill tab and choose your preferred color to highlight the rows you want to be selected. Then click OK to close the window.

Conditional Formatting Using Odd or Even Row Formula 4

5. If you have tried out Method 1, this will start to look familiar from this point onward. You will now return to the New Formatting Rule mini-window. You can see the preview of what your selected rows are going to look like. Click on the OK button to finish the process here.

Conditional Formatting Using Odd or Even Row Formula 5

6. Now, you will see that based on your preference, the odd or even rows are highlighted within your selected range. Do not deselect the range just yet.

Conditional Formatting Using Odd or Even Row Formula 6

7. Click on the Data ribbon now. Then in the Sort & Filter group, click on Filter. You will see that the top row of your selection now has some filter tabs. Click on one of them, hover over your mouse pointer on Filter by Color and then click the color of your rows under Filter by Cell Color.

Conditional Formatting Using Odd or Even Row Formula 7

8. Now you will see that the rows are trimmed down and only the highlighted odd or even rows are shown. Do not click anywhere. Move to the next step.

Conditional Formatting Using Odd or Even Row Formula 8

9. Press the F5 button on your keyboard once. It will bring up the Go To mini-window. Click on the Special… button in that window. Now, in the newly opened Go To Special window, click on the Visible cells only radio button. Finally, click OK.

Conditional Formatting Using Odd or Even Row Formula 9

10. And there you go! Your intended selection of odd or even rows based on the input of ISODD or ISEVEN formula is now individually done. But one final step remains to complete the whole process.

Conditional Formatting Using Odd or Even Row Formula 10

11. Keep the selection and click on the Filter tab again. Hover your mouse pointer over Filter by Color and click on the color to remove the filtering rule of Filter Cell by Color. This will show all the rows again with the selected rows still intact with selection. Finally, click on Filter in the Sort & Filter group under the Data ribbon to remove filtering totally and complete this process.

Conditional Formatting Using Odd or Even Row Formula 11

And there you have it! Your every other row is now properly selected based on your odd or even preference!

To remove the highlights from the rows, follow the Important Tip discussed in Method 1.

Method 3: Using ISODD/ISEVEN Formula

This method is going to be your favorite if you are looking for a way to avoid highlighting rows with formulas to select every other row in Excel. Some find this method to be requiring extra work while others think it is more straightforward than other methods.

Using this technique, you can select your rows based on their Odd and Even positions. We are going to use the ISODD or ISEVEN formula again to find out and select every other row. However, we are NOT using Conditional Formatting in this method unlike the previous one.

Go through the steps to try it out yourself and decide if you prefer this one more.

1. Take a new column right beside your existing columns. We are going to use it and the values in it as anchors to find and select every other row. Name the column Odd if you are going to select odd rows.

Contrarily, for your convenience, name it Even if selecting even rows is your goal. Either way works. We are naming it Odd for this tutorial.

Using ISODD or ISEVEN Formula 1

2. Look at the row numbers in your sheet and determine whether every other row you want to select has odd or even numbers. For this tutorial, we are going with the Odd numbered rows.

Click on the cell right beneath the heading of the newly added column. Write =ISODD(ROW()) for odd-numbered rows and =ISEVEN(ROW()) for even-numbered rows. Hit Enter after you are done writing the formula.

Using ISODD or ISEVEN Formula 2

3. After hitting the Enter button, you will see that the formula is showing TRUE or FALSE corresponding to the row number.

Using ISODD or ISEVEN Formula 3

4. Now, fill up the column with the formula by double-clicking on the Fill handle as marked in the left picture below. After doing it correctly, you will see that all the cells in the column have been filled with the formula. They are now showing a TRUE or FALSE value based on whether the row is occupying the odd-numbered positions or even-numbered positions.

Using ISODD or ISEVEN Formula 4

5. Now select the cell range. Select a cell with your mouse, hold it, and drag it across. Alternatively, select the top-left cell of your intended cell range and press and hold CTRL + SHIFT. While holding those two buttons, press first Right Arrow and then Down Arrow to make the complete selection.

When you are done selecting, click on the Filter button in the Sort & Filter group under the Data ribbon.

Using ISODD or ISEVEN Formula 5

6. You will now see that the topmost row has some filter tabs in every column. Click on the anchor column named Odd’s filter tab. You will find that in the filter list, all of the checkboxes are filled.

Using ISODD or ISEVEN Formula 6

7. Remove the tick on the FALSE rule checkbox. If you went with selecting the odd-numbered rows for your every other row selection, doing this will filter out the even-numbered rows. You will now see that all the odd-numbered rows are selected (as shown in the right picture below). Don’t deselect just now if you want to select all the rows individually. Move on to the next step.

Using ISODD or ISEVEN Formula 7

8. While the selection is in place, hit F5 on your keyboard. It will bring up the Go To window. Click on the Special… button. Now select the Visible cells only radio button on the newly opened up Go To Special mini-window, and then click OK.

Using ISODD or ISEVEN Formula 8

9. At this point, you will see that all the rows are selected individually. Now, if you need to bring up the rest of the rows while every other row is still in the selection, follow the next step.

Using ISODD or ISEVEN Formula 9

10. Click on the filter tab on your anchor tab. Select the FALSE rule again in the filtering list and click OK. As shown in the picture below-right, you will find all the rows visible now.

Using ISODD or ISEVEN Formula 10

There you go! You have every other row selected as shown in the picture above.

11. [Optional] To remove the filtering after finishing your work, go back to the Data ribbon and click on Filter. It will remove the filters from your dataset or selected range. To wrap it up, select the anchor column and hit the DELETE button on your keyboard to clean up the formula.

Using ISODD or ISEVEN Formula 11

Method 4: Selecting Every Other Row with Visual Basic Application (VBA)

We all know someone who loves using a bit of code to carry out a task. If you are one of them, you are in luck! In this method, we are going to show you how to select every other row in Excel using Visual Basic Application, famously known by its abbreviation, VBA.

We are keeping it pretty straightforward. Even if you are someone who does not know how to use VBA, fear not. We are sharing the code with you so that you can simply copy-paste it into the editor and be done with it. Let’s go!

1. Begin by selecting your range of data. Follow a technique from the ones discussed in step 1 of Method 1

Selecting Every Other Row with VBA 1

2. Go to the Developer ribbon and click on Visual Basic within the Code group. Alternatively, you can press the ALT + F11 buttons to bring up the Visual Basic Editor. If you cannot find the Developer ribbon in your Excel, we will show you how to add it to your ribbons right after we are done discussing this method.

Now, when the Visual Basic opens, click on the Insert menu on the top, and then click Module from the list.

Selecting Every Other Row with VBA 2

3. Now you will see the code editor in front of you. Copy the code from below.

Sub EveryOtherRowSelection()
Dim yRange As Range
Dim aRange As Range
Dim rowCount, i As Long
Set yRange = Selection
rowCount = yRange.Rows.Count
With yRange
Set aRange = .Rows(1)
For i = 3 To rowCount Step 2
Set aRange = Union(aRange, .Rows(i))
Next i
End With
aRange.Select
End Sub

Paste the code in the editor. Afterward, click on the Run button or simply press the F5 button on your keyboard. Then close the Visual Editor.

Selecting Every Other Row with VBA 3

4. Voila! Your every other row is now selected! See how easy it is?

Selecting Every Other Row with VBA 4

Enabling Developer Option in Excel Ribbons:

1. Go to the File menu and click on Options.

Enabling Developer Option in Excel Ribbons 1

2. Click Customize Ribbon on the left. On the right under Customize the Ribbon, make sure that you tick the Developer checkmark on the list. Finally, hit OK to finish the task.

Enabling Developer Option in Excel Ribbons 2

Now you will see that the Developer ribbon has been activated in your Excel program.

Last Words

One by one, we have shown you four different methods on how to select every other row in Excel. From selecting through highlighting rows to using formulas with filtering to using Visual Basic Application, we didn’t leave anything behind to discuss this matter.

It is now up to you regarding which method you are going to learn and use. From our experience, we can say that Method 4 is probably the best way to do it. It works excellently with huge amounts of data as well. If you don’t know how to code, you can simply just copy and paste the code. It will get the job done perfectly!

However, copy-pasting the code may seem unproductive to some. In that case, choose one from the other three methods. Method 1 and Method 2 involve marking every other row by highlighting and then filtering through cell colors. Method 3 is more straightforward from that angle.

Try out all the methods. And as practicing is the key to getting better at what you do, do not forget to practice repeatedly if you want to become a master of Excel!

Leave a Comment