While working on Excel spreadsheets, some of the tasks are used commonly, such as selection, grouping, sorting out data, and so on. Talking about the selection task, you will tenderly need to use it while working with multiple formulas or other functions. You may have to use it to select one or more rows. Well, these are some of the general examples of selection. You might be needing to select data in every other row in Excel and for this, you can use the best ways mentioned below.
How to Select Every Other Row in Excel?
Here we have some useful tricks that you can follow to describe the possibility of how to select every other row in Excel. Let’s find it out:
- With CTRL and Mouse Click
- Using Conditional Formatting
- Format Data as a Table
- Go To Special hack
- VBA (Automation)
Using CTRL and Mouse Click to Select Every Other Row
To select every other row in Excel, you can follow the easiest way of using CTRL and Mouse click. In this option, you will have to hold down the CTRL button right from your keyboard ((⌘ if you are using MAC) while selecting the number of rows.
When you click on the number of rows, you will see that the rows are being highlighted. You can simply select every other row and remember that such selections are known as the non-contagious range.
In case, if you don’t want to keep the row highlighted and want to deselect it, click on it again. Apart from that, you can even apply individual cells and columns’ selection to perform your desired option. It will help you increase the speed and eventually you can select rows promptly. That’s why you can make changes in the rows setting all at once speedily.
Well, remember that it could be tiring at times, especially when you are having larger datasets because the selection of all the rows manually will surely bring pain to the fingers.
How to Select Every Other Row in Excel With Conditional Formatting?
When using conditional formatting, you are free to make changes in the cell while keeping the content structure in mind. To select every other row in Excel, you can use this flexible tool conveniently. Here, we have come up with an example in which we need to color the rows randomly starting from the first row.
Firstly, choose the data on which you want to apply a conditional formatting tool other than the header. After choosing, press on the Home tab > Conditional Formatting > New Rule.
After that, you will see a dialog box named New Formatting Rule. Right from this dialog box, you have to choose the “Use a formula to determine which cells to format”. Choosing this option will let you add a formula to run the conditional formatting feature firmly. Now, you have to enter the formula in the box.
Here is the formula:
=MOD(ROW(B2),2)=0
In the next step, where you need to color the alternate rows, you have to hit on the Format option. Fill and choose the color, now press OK.
Once again click on OK to get out of the rules box of conditional formatting. You will the application of your rule instantly.
Format Data as a Table
In Excel, most of the time people need to put data in a table format. Using tables in Excel comes up with loads of advantages. The most common one is that alternate colored rows are used to format data, which makes it attractive to others. When you need to change a range into a data table, firstly, you will have to choose the data and you have to choose the header as well.
Using Filter with Go To Special Feature
In the given example, you will see another column named Row Even/Odd. It is added to use Filter with Go To Special feature while understanding how to select every other row in Excel. For even row, you will see TRUE and for odd rows, you will see False.
Apart from that, you can even use the ISEVEN function. As given in the example, choose the F4 cell and apply your formula as mentioned below:
=ISEVEN(ROW())
Now, you will have to add the formula in the formula bar or else you can add it to the selected cell.
Press ENTER key and the fourth row has an even number that’s why you will see TRUE.
In the end, for all the other cells you may have to use the Fill Handle option to AutoFit formula.
Once it is done, follow these steps:
Choose the range where you need to add the Filter. In this example, all the columns are selected.
Later on, open the Data tab and choose Filter.
Furthermore, you can even try the CTRL + SHIFT + L keys right from your keyboard. These shortcut keys help you in applying the Filter to all the columns.
To activate the Filter option, choose the Row Odd/Even column, from which you can select the TRUE value to Filter. Now, click OK.
Keep in mind that when there are TRUE values, all the column values will be Filtered.
Now is the time to choose the range where you need to apply this GO To Special Feature.
For this, go to the Home tab, and choose Find & Select option from the Editing group. And select Go To Special feature.
You will see a dialog box, from which you need to choose the Visible cells only. Click OK and wind up the functioning.
VBA Automation
Whenever you don’t like to go for tricky ways, the ultimate solution to any problem is in VBA. Visual basic for applications is a built-in programming language in Excel. Similar to other functions, you can use it to select every other row in Excel automatically of a given range.
In the VBA Editor, you may have to copy and paste the script into the new module. For a new module, you need to go to Developer and then Visual Basic. If the Developer option is not activated, you will not see it on the ribbon. That’s why you have to activate it first to make it functional.
In the VBA Editor, click on the Insert and then 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
A selection of cells is needed to run this code. And the cells would be those you need to select every other row from first.
To Sum Up
In this post, you have found all the possible ways that can help in selection of every other row in Excel. Along with VBA coding, now the options are increased to select every other row in Excel. Try these different approaches to fulfill your needs.
