How to Select Every Other Cell in Excel (Or Every Nth Cell)

by

If you’ve ever tried selecting alternate cells or cells in a particular pattern in Excel, you would know that this is not as easy as it seems.

Well, if it is a matter of 10-20 cells then it’s no big deal. However, if it’s hundreds of cells that we’re talking about, that’s when things get tough.

Well, actually it need not be that tough.

Here are three ingenious tricks that you can use to quickly select every other cell in Excel (or every third, fourth, or Nth cell).

In this article, we will see how to select cells in every other row, every other column, and then we will see how you can use these selected cells for different purposes.

Why Would You Need to Select Every Other Cell in Excel?

There may be a variety of reasons that one might need to select alternate cells or every nth cell in a spreadsheet. Maybe you need to color every alternate cell with a particular color.

Maybe you need to do some sort of calculation or operation that involves only the odd or even-numbered cells, or every nth cell.

As of now, there’s no straightforward function or menu item to get this done. So, you have to go about it the long way.

How to Select Cells in Every Other Row

Now let’s jump into some methods you can use to select every other cell in a column in Excel

Method 1: Using Go to Special and Selection Offset

The easiest way to select alternate cells, in a column is by creating a new temporary column next to your target cell and using Selection offset in the following way:

  1. Create a temporary column next to your target column. So if you want to select cells from column A, create a temporary column in Column B. Let’s say your data is in A1:A50. Right-click on the row header and select ‘Insert’ from the popup menu.
  2. Enter any number, say 1 in cell B1, and a letter, say A in cell B2.
  3. Select both B1 and B2.
  4. Double click the fill handle and you will see a number and the letter A appear alternately in a pattern from cell B1 to B50 (something as shown below).Fill handle to fill the column with numbers and alphabets
  5. Click on the Find & Select button on the Home Tab’s ribbon. Select ‘Go to Special’ from the sub-menu.
  6. From the ‘Go To Special’ Dialog Box, select the radio button for ‘Constants’ and deselect all the checkboxes, except for ‘Numbers’.
  7. When you click OK, you will see only the numbers selected in Column B.Alternate Numbers selected in Column B

Notice that the numbers form every alternate cell in Column B. But we want alternate cells of Column A selected, not B. For this, we need to write a short module in VB Script.

  • From the Developer Menu Ribbon, select Visual Basic.
  • Once your VBA window opens, Click Insert->Module. Now you can start coding.
  • Type or copy-paste the following 3 lines into the module window:
Sub SelectAlternateCells()
Selection.Offset( , -1).Select
End Sub

When you run the above macro code (Developer –>Macros –>SelectAlternateCells  –>Run) you will see alternate cells of Column A selected.

Every Other cell selected in Excel column

What just happened?

The Offset function is used to activate the range that is a particular number of rows and columns away from a certain range.

In our case, we specified the offset function to activate the range that is 0 rows and -1 columns away from the range B1:B50. That means we want the range A1:A50 activated.

Note: If you want every third, fourth or nth cell selected, simply place a number in the first row and a letter in every n-1 rows below it. This will ensure that there is a number only every nth row. After that, repeat the same procedure as shown above.

To select every fifth cell in a column in Excel

Also read: How to Select Every Other Row (Alternate Row) in Excel?

Method 2: Using Filters

Let’s assume you again have your list in cells A1:A50.

Copy the following function into cell B1.

=ISEVEN(ROW())

After that, double click the fill handle

The ISEVEN function is used to check if a numeric value is an even number. It returns TRUE when the given value is even, and FALSE if it is odd.

We passed the ROW() function to this. This means the function will return TRUE for even-numbered rows and FALSE for odd-numbered rows.

In other words, you get a sequence of alternating TRUE and FALSE values in column B.

  1. Select Both columns (A and B)
  2. From the Data menu ribbon, select the Filter tool. You will notice two filter arrows next to each column header.Apply filter to the dataset
  3. Click on the arrow next to Column B’s header and select only TRUE from the dropdown.Click on the filter icon and select True

You will now only see the rows that have TRUE values for Column B.

  1. Select the range of cells that are visible in Column A.
  2. Click on the Find & Select button on the Home Tab’s ribbon. Select ‘Go to Special’ from the sub-menu.
  3. From the ‘Go-To Special’ Dialog Box, select the radio button for ‘Visible Cells Only’. This will select only the cells that are visible in the filter. Click OK
  4. Now, remove the filter by again clicking on the Date-> Filter tool.

All your numbers will now be visible, but only the alternate cells will be selected.

Note: If you want every nth row selected, then use the following function in place of ISEVEN:

=MOD(ROW(), n)=0

Replace ‘n’ with the number of cells you want to skip.

Also read: Add Text To End Of Cell Excel

Method 3: Using a VBA Macro Code

The third way is to just use a VBA script that we have created for specifically this purpose.

Below is the VBA macro code that will select every other cell

  1. From the Developer Menu Ribbon, select Visual Basic.
  2. Click Insert->Module
  3. Type or copy-paste the following lines into the module window: 
    Sub select_alt_cells2()
Dim Rng As range
Dim myCell As range
Dim myUnion As range
Set Rng = Selection
For i = Rng.Rows.Count To 1 Step -2
Set myCell = Rng.Cells(i)
If Not myUnion Is Nothing Then
Set myUnion = Union(myUnion, myCell)
Else
Set myUnion = myCell
End If
Next i
myUnion.Select
End Sub
  4. Close the Window
  5. Now in your worksheet, select the cells that you want to use.
  6. Go to Developer->Macros. From the Macro Dialog box, select the module named select_alt_cells2 and click Run.

That’s it, you should see alternate cells in your column selected.

Note: If you want every nth row selected, then in line 6 of the VBA script, replace the number 2 with the number of cells you want to skip. So if you want every 5th row selected, you change line 6 to:

For i = Rng.Rows.Count To 1 Step -5

How to Select Cells in Every Other Column

Under rare circumstances, you might need to select cells in every other column. In that case, you can just make a small change to the above VBA script and it will do the job just right.

To select cells in every other column, replace the keyword ‘Rows’ with the keyword ‘Columns’. So your line 6 should now be:

For i = Rng.Columns.Count To 1 Step -2

That’s all there is to it!

How to Highlight Every Alternate Cell

If you want to highlight every alternate cell by changing the background color, you don’t need to select any cell. You only need to set the ‘style’ property of alternate cells to “Note”.

This can be done by just changing line 14 of the script from myUnion.Select to:

myUnion.Style = "Note"

So your code will now be:

Sub select_alt_cells2()
Dim Rng As range
Dim myCell As range
Dim myUnion As range
Set Rng = Selection
For i = Rng.Rows.Count To 1 Step -2
  Set myCell = Rng.Cells(i)
  If Not myUnion Is Nothing Then
    Set myUnion = Union(myUnion, myCell)
  Else
    Set myUnion = myCell
  End If
Next i
myUnion.Style="Note"
End Sub

How to Sum up Values of Every Alternate Cell

Say you need to display in cell A51 the sum of values in every alternate cell, once again, you can change line 14 of our script to:

range("A51").Value = Excel.WorksheetFunction.Sum(myUnion)

Feel free to replace the cell location A51 with the location of any cell you wish to display your sum in.

How to Delete Every Alternate Cell

If you only want to clear the value of every alternate cell, you can replace line 14 of our code to:

myUnion.Delete

However, if you want to delete entire rows corresponding to alternate cells, then replace line 14 with the following line:

Conclusion

In this tutorial, we looked at three different ways in which you can select every other cell in Excel, whether the cells you want to select are in alternate rows or alternate columns.

One of the methods involves using a VBA script. You can use this script and tweak it depending upon your requirement.

We have shown how you can tweak it when you want to add the selected cells, delete them, or just highlight them.

I hope you found this tutorial helpful.

Other Excel tutorials you may find useful:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

1 thought on “How to Select Every Other Cell in Excel (Or Every Nth Cell)”

  1. I’m sorry, but the methods, and Especially, how they are brought in this Article, are Too CUMBERSOME!

    “Method 1” -> For only filtering out from current selection One Data / Row Number Multiple, You can also use “Go To Special” and select “Column differences”.

    “Method 2” -> Is only an Extension of “Method 1”. The “TRUE/FALSE” Values are Not Necessary for filtering data, and we can use Numbers/Letters method, as was shown in “Method 1”.

    “Method 3” -> Deleting Entire Rows is very-very EASY, without the need of any VBA Code. Using the 2 previous methods, we can use the Ribbon-Button: “Home” -> “Cells” -> “Delete” (down arrow) -> “Delete Rows” (or, just use a Right-Click Menu on a selection cell).

    Reply

Leave a Comment