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.
- Using CTRL and mouse click
- Conditional Formatting
- Format Data as a Table
- ‘Go To Special’ hack
- 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.
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…
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.
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.
Click OK again to exit out of the Conditional Formatting rules dialog box and your rule will be applied immediately to your selection.
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:
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.
Your data will now be formatted as a table object. The default color formatting will be applied and every other row will be colored.
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.
‘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.
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.
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.
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.
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.
