Select Every Other Row In Excel – 5 Different Ways

Screenshot showing how to select every other row in excel using the CRTL and mouse click method

In this post I will show you how to select every other row in Excel. Like many tasks in Excel, there is more than one way to achieve this. Here I show you 5 ways to select every other row in Excel. These 5 tips will improve your data manipulation skills.

Use the contents table below to skip to each section.

  1. Using CTRL and mouse click
  2. Conditional Formatting
  3. Format Data as a Table
  4. ‘Go To Special’ hack
  5. VBA (automation)

Using CTRL and Mouse Click To Select Every Other Row

The simplest way to select every other row in Excel is to hold down down the CTRL button on your keyboard (⌘ on MAC) and then the number of the rows you want to select.

Clicking on the row number itself highlights the whole row. By holding down CTRL, we are able to select every other row or even a bunch of single cells. This sort of selection is referred to as a non-contiguous range.

 

Screenshot showing how to select every other row in excel using the CRTL and mouse click method

 

To deselect a row, simply click on it again. You can also perform a non-contiguous selection on individual cells and columns.

The advantage of this technique is it’s speed. You can very quickly select the rows you want to alter and then apply the change to all of them in one go.

The downside to this technique is when you have large datasets and it becomes too laborious to select all the rows you want manually.

You can also see from our example that the row selection exceeds the boundaries of our data which is also something you may not want to do.

Highlight Every Other Row With Conditional Formatting

Conditional formatting alters the look of a cell based upon the value within it. It is a very flexible tool and we can use it to highlight every other row in a data set. We can use it to highlight (select) every other row in Excel.

In this example, I want to color alternate rows, starting with the first row after the header.

First I select the data I want to apply the Conditional Formatting to (excluding the header), then hit:

Home –> Conditional Formatting –> New Rule…

select every other row in excel - conditional formatting 01

 

Doing this will bring up the New Formatting Rule dialog box. From the options available, click on ‘Use a formula to determine which cells to format’.

This will change the dialog box to allow you to enter a formula that your conditional formatting will abide by. In the box, insert the following formula:

=MOD(ROW(B2),2)=0

NOTE: Substitute B2 above for the top-left most cell in your selection.

select every other row in excel - conditional formatting 02

Formula Explainer

The MOD function returns the remainder of a division expression. So if you passed in the number 3 to be divided by 2, you’d get 1.

The ROW function returns the number of the row of the cell reference that you pass in. So in this case, cell B2 is row number 2.

So by passing in the row number, and dividing it by 2 using the MOD function, we’ll be able to tell if a row number is odd or even. Odd row numbers will return 1, even row numbers will return 0.

When the formula evaluates to 0, our conditional formatting rule is applied!

Next you can set the color of that you’d like the alternate rows to be. To do this, simply click ‘Format…’ –> Fill and select your chosen color. Then hit OK.

select every other row in excel - conditional formatting 03

Click OK again to exit out of the Conditional Formatting rules dialog box and your rule will be applied immediately to your selection.

select every other row in excel - conditional formatting 04

 

Format Data As A Table

Excel has a built-in feature to format ranges as tables. There are many advantages to using tables in Excel and you can learn more about using them from this great post by Easy Excel on Data Tables In Excel.

One of those benefits is that the data is formatted nicely with alternate colored rows, making it simpler for us to eye over the data.

To convert a range to a data table, first select the data, including a header if you have one, and then click:

select every other row in excel - format data as a table 01

 

Keyboard Shortcut

To quickly insert a table, select your data, then press CTRL + T on the keyboard.

The ‘Create Table’ dialog box will appear. Double-check that the range of cells you are going to format as a table is correct.

If you need to make an adjustment, you can overwrite the cell range in the box or simply click the small upward arrow, and then select the cells from your worksheet using the mouse.

If you data has headers, make sure the ‘My table has headers’ box is checked. Click OK.

select every other row in excel - format data as a table 02

Your data will now be formatted as a table object. The default color formatting will be applied and every other row will be colored.

format data as a table 03

You can quickly adjust the color formatting by selecting any cell within your data table and then clicking Table Design up on the ribbon.

In the ‘Table Styles’ section, you have the option to change the table’s color from a selection of pre-set formats. Customization is also available.

format data as a table 04

 

‘Go To Special’ Hack

This technique works great if you want to select every other row in Excel within your data set to then delete it.

First we create a “helper” column in the first column to the left of our data set. Give it a header to clearly mark it’s purpose, it will be deleted later on.

In the first row of the helper column, insert an ‘X’. Then click on this cell and drag your mouse down to the cell below, so that both of the cells are now selected.

select every other row in excel - go to special hack 01

Now the first to cells are selected, notice the small green square in the bottom-right of the selected cells – this is the Auto Fill Handle.

Click and hold the Auto Fill Handle, and drag the mouse down to extend the selection to all of the rows in your data set.

select every other row in excel - go to special hack 02

 

Auto Fill Handle

The cursor changes to a small black plus sign when you mouse over the auto fill handle to indicate it is active.

Clicking and dragging down will extend the “X then blank” pattern to all of the cells you select.

Alternatively, you can double-click the fill handle and it will automatically extend down to the end of your data set.

The auto fill handle is a really useful feature on Excel. You can read more about it’s functionality in this post on 6 Ways To Use The Fill Handle In Excel

Now we have created a recognizable pattern of “X then blank” for every 2 cells in the helper column. With these cells still selected, click:

Home –> Find & Select –> Go To Special…

This will open the ‘Go To Special’ dialog box. Select the ‘Blanks’ radio button then click OK.

go to special hack 03

Excel will now create a non-contiguous cell selection on the blank cells in the helper column. Now we can right-click on any of the selected cells and click Delete. This will launch the ‘Delete’ dialog box where you can then select Entire Row and then click OK.

Every other row will be deleted and the data will be pushed up into the gaps.

go to special hack 04

 

go to special hack 05

You can now delete your helper column

 

VBA (Automation)

VBA (visual basic for applications) is Excel’s built-in programming language. We can use this small script to automatically select every other row of a given range.

To use the script, copy and paste it into a new module in the VBA Editor.

To open a new module, go to Developer –> Visual Basic. If you don’t see the Developer tab on the ribbon, you’ll need to activate it first.

When in the VBA Editor, click Insert –> Module

Sub SelectEveryOtherRow()

Dim userRange As Range
Dim alternateRowRange As Range
Dim rowCount, i As Long

Set userRange = Selection
rowCount = userRange.Rows.Count

With userRange
‘add row 1 of selected cells to the range variable
Set alternateRowRange = .Rows(1)

‘loop through every other row and add to range variable
For i = 3 To rowCount Step 2
Set alternateRowRange = Union(alternateRowRange, .Rows(i))
Next i

End With

‘select every other row
alternateRowRange.Select

End Sub

 

To run this code, a user will need to make a selection of cells that they want to select every other row from first. The code works by looping through the selected range and adds every other row to a range variable which is then selected at the end of the programme.

Summary

Here we have looked at 5 different ways to select every other row in Excel. As we have seen, there are many methods available for us to do this from conditional formatting to automated selection via VBA.

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top