How to Select Every Other Cell in Excel (Or Every Nth Cell) - Spreadsheet Planet (2023)

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).
5. Click on the Find & Selectbutton 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.

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).SelectEndSub

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

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:A50activated.

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-1rows below it. This will ensure that there is a number only every nthrow. After that, repeat the same procedure as shown above.

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.
3. Click on the arrow next to Column B’s header and select only TRUE from the dropdown.

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.

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:

   Subselect_alt_cells2()Dim Rng As rangeDim myCell As rangeDim myUnion As rangeSet Rng = SelectionFor i = Rng.Rows.Count To 1 Step -2Set myCell = Rng.Cells(i)If Not myUnion Is Nothing ThenSet myUnion = Union(myUnion, myCell)ElseSet myUnion = myCellEnd IfNext imyUnion.SelectEnd 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_cells2and 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 2with the number of cells you want to skip. So if you want every 5throw selected, you change line 6 to:

Fori = Rng.Rows.Count To1Step-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:

Fori = Rng.Columns.Count To1Step-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.Selectto:

myUnion.Style = "Note"

So your code will now be:

Subselect_alt_cells2()DimRng As rangeDimmyCell As rangeDimmyUnion As rangeSetRng = SelectionFori = Rng.Rows.Count To1Step-2 SetmyCell = Rng.Cells(i) IfNotmyUnion IsNothingThen SetmyUnion = Union(myUnion, myCell) Else SetmyUnion = myCell EndIfNextimyUnion.Style="Note"EndSub

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 A51with 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 rowscorresponding 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:

• How to Filter as You Type in Excel (With and Without VBA)
• How to Make all Cells the Same Size in Excel (AutoFit Rows/Columns)
• How to Unhide All Rows in Excel with VBA
• How to Clear Contents in Excel without Deleting Formulas
• How to Delete a Sheet in Excel Using VBA
• How to Select Alternate Columns in Excel (or every Nth Column)
• How to Select Rows with Specific Text in Excel
• How to Highlight Blank Cells in Excel?