search
HomeTopicsexcelHow to select every other or every nth column in Excel

This article will guide you step-by-step through the process of selecting specific columns in Excel, such as every other or every nth column.

Microsoft Excel, the go-to spreadsheet software for data enthusiasts and professionals, offers several methods to select columns based on specific criteria. While many are familiar with the standard techniques, there exist lesser known but more efficient variations to accomplish this task.

Select alternate columns in Excel

The simplest way to select alternate columns in Excel is by utilizing the Ctrl key in combination with the mouse. Here's how you can do it:

  1. Press and hold the Ctrl key on your keyboard.
  2. While holding the Ctrl key, click on the header of every other column.
  3. Repeat steps 2 and 3 until you have selected all the desired columns.
  4. Release the Ctrl key.

As a result, you have the alternate columns selected:

How to select every other or every nth column in Excel

By using the Ctrl key, you can handpick every 3rd, every 4th or every nth column, so you can apply formatting or perform calculations on specific sections of your data.

This method of selecting every other or every nth column is a straightforward and effective approach, particularly for small datasets. However, it may become cumbersome and time-consuming when dealing with large sets of data. Manually clicking on each column header can be prone to errors and can become tiresome. In such cases, alternative methods come in handy to streamline the process and save valuable time.

Selecting every other or nth column with formula

If you prefer a more precise method to select every other or nth column in Excel, you can achieve this by using the CHOOSECOLS function. Here's how you can use it:

  1. In an empty cell, enter the CHOOSECOLS formula. The first argument should be the source range that contains the columns you want to select.
  2. In the subsequent arguments, provide the column numbers you want to return.
  3. Press Enter to apply the formula.

For example, to select every even column in the range A2:F20, the formula takes this form:

=CHOOSECOLS(A2:F20, 2, 4, 6)

This formula specifies that you want to return columns 2, 4, and 6 from the range A2:F20.

How to select every other or every nth column in Excel

The CHOOSECOLS formula returns the specified columns as a dynamic array, which you to easily select and copy to another part of your worksheet.

Excel VBA to select every other column

Using VBA macros in Excel, you can automate the process of selecting every other column with precision and efficiency.

Macro to select every odd column in Excel

This VBA code enables the selection of every odd column in Excel such as 1, 3, 5, etc. It achieves this by iterating through the columns within the user-selected range and creating a new range that includes only the odd columns. The resulting range is then selected, allowing for easy identification and manipulation of the desired columns.

VBA code to select every odd column
Sub SelectOddColumns() Dim selectedRange As Range Dim i As Integer Dim newRange As Range ' define selected range Set selectedRange = Selection ' for each column in the selected range For i = 1 To selectedRange.Columns.Count Step 2 ' add every even column to a new range If newRange Is Nothing Then Set newRange = selectedRange.Columns(i) Else Set newRange = Union(newRange, selectedRange.Columns(i)) End If Next i ' select new Range If Not newRange Is Nothing Then newRange.Select End If End Sub

Macro to select every even column in Excel

The following VBA code allows selecting every even column such as 2, 4, 6, etc. It automates the process by iterating through the columns within the pre-selected range and creating a new range that includes only the even columns.

VBA code to select every even column
Sub SelectEvenColumns() Dim selectedRange As Range Dim i As Integer Dim newRange As Range ' define selected range Set selectedRange = Selection ' for each column in the selected range For i = 2 To selectedRange.Columns.Count Step 2 ' add every even column to a new range If newRange Is Nothing Then Set newRange = selectedRange.Columns(i) Else Set newRange = Union(newRange, selectedRange.Columns(i)) End If Next i ' select new Range If Not newRange Is Nothing Then newRange.Select End If End Sub

To add the codes to your workbook, follow the steps described in How to insert VBA code in Excel.

To select every other column using a macro, follow these steps:

  1. Highlight the range in which you want to select columns.
  2. Press Alt F8 to open the macro dialog box.
  3. Choose the desired macro - SelectOddColumns or SelectEvenColumns.
  4. Click Run to execute the code.

How to select every other or every nth column in Excel

Excel macro to select every Nth column

If your task is to select every Nth column within a range, you can accomplish it using a VBA macro and a UserForm.

First, create the UserForm:

  1. In the VBA Editor, click Insert > UserForm.
  2. Design the UserForm with an input field such as a TextBox to enter the value for N.
  3. Add an OK button to trigger the macro.

How to select every other or every nth column in Excel

After completing the form's design, add the code for the OK button. For this, double-click the OK button on the UserForm and paste the following code in the Code window:

VBA code to select every Nth column in Excel
Dim selectedRange As Range Dim i As Integer Dim newRange As Range Dim inputNum Set selectedRange = Selection ' if a decimal number is entered, only the integer part is used inputNum = Int(Val(TextBox1.Text)) ' loop through each column in the selected range If inputNum > selectedRange.Columns.Count Then MsgBox "Incorrect N value. Please enter a valid value for N and try again.", vbExclamation, "Error" Exit Sub End If For i = inputNum To selectedRange.Columns.Count Step inputNum ' add each column in a certain step to a new range If newRange Is Nothing Then Set newRange = selectedRange.Columns(i) Else Set newRange = Union(newRange, selectedRange.Columns(i)) End If Next i ' select new range If Not newRange Is Nothing Then newRange.Select End If Me.Hide End Sub Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case 48 To 57 'digits 'do nothing, allow input Case 8, 9, 13, 27 'backspace, tab, enter, escape 'do nothing, enable these keys Case Else 'disable all other keys KeyAscii = 0 End Select End Sub Private Sub UserForm_Click() End Sub

Finally, create the SelectEveryNColumn macro to incorporate the UserForm and complete the functionality. For this, add the following code to the module in your workbook:

VBA macro to select every Nth column
Sub SelectEveryNColumn() ' displays a popup window for entering the N value UserForm1.Show End Sub

Tip. To save time, you can download our sample workbook at the end of this post, which includes the pre-designed UserForm and the macro. And then, just copy the code and UserForm1 from the sample workbook into your own one.

Once implemented, running the macro will display a pop-up dialog box asking for the value of N. Enter the desired number, and the macro will select every Nth column within the selected range.

How to select every other or every nth column in Excel

As you see, Excel offers a diverse range of methods to select alternate columns or every other column. Whether you prefer the commonly known Ctrl key technique, where you manually handpick the desired columns, or the ingenious use of the CHOOSECOLS formula, or the automated solutions with VBA macros, Excel has you covered. The choice ultimately depends on your preferences, the complexity of your dataset, and the level of automation you seek. So, explore these options, experiment with different techniques, and find the approach that best fits your needs. Happy column selecting!

Practice workbook for download

VBA to select every other or every Nth column in Excel (.xlsm file)

The above is the detailed content of How to select every other or every nth column in Excel. For more information, please follow other related articles on the PHP Chinese website!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
MEDIAN formula in Excel - practical examplesMEDIAN formula in Excel - practical examplesApr 11, 2025 pm 12:08 PM

This tutorial explains how to calculate the median of numerical data in Excel using the MEDIAN function. The median, a key measure of central tendency, identifies the middle value in a dataset, offering a more robust representation of central tenden

Google Spreadsheet COUNTIF function with formula examplesGoogle Spreadsheet COUNTIF function with formula examplesApr 11, 2025 pm 12:03 PM

Master Google Sheets COUNTIF: A Comprehensive Guide This guide explores the versatile COUNTIF function in Google Sheets, demonstrating its applications beyond simple cell counting. We'll cover various scenarios, from exact and partial matches to han

Excel shared workbook: How to share Excel file for multiple usersExcel shared workbook: How to share Excel file for multiple usersApr 11, 2025 am 11:58 AM

This tutorial provides a comprehensive guide to sharing Excel workbooks, covering various methods, access control, and conflict resolution. Modern Excel versions (2010, 2013, 2016, and later) simplify collaborative editing, eliminating the need to m

How to convert Excel to JPG - save .xls or .xlsx as image fileHow to convert Excel to JPG - save .xls or .xlsx as image fileApr 11, 2025 am 11:31 AM

This tutorial explores various methods for converting .xls files to .jpg images, encompassing both built-in Windows tools and free online converters. Need to create a presentation, share spreadsheet data securely, or design a document? Converting yo

Excel names and named ranges: how to define and use in formulasExcel names and named ranges: how to define and use in formulasApr 11, 2025 am 11:13 AM

This tutorial clarifies the function of Excel names and demonstrates how to define names for cells, ranges, constants, or formulas. It also covers editing, filtering, and deleting defined names. Excel names, while incredibly useful, are often overlo

Standard deviation Excel: functions and formula examplesStandard deviation Excel: functions and formula examplesApr 11, 2025 am 11:01 AM

This tutorial clarifies the distinction between standard deviation and standard error of the mean, guiding you on the optimal Excel functions for standard deviation calculations. In descriptive statistics, the mean and standard deviation are intrinsi

Square root in Excel: SQRT function and other waysSquare root in Excel: SQRT function and other waysApr 11, 2025 am 10:34 AM

This Excel tutorial demonstrates how to calculate square roots and nth roots. Finding the square root is a common mathematical operation, and Excel offers several methods. Methods for Calculating Square Roots in Excel: Using the SQRT Function: The

Google Sheets basics: Learn how to work with Google SpreadsheetsGoogle Sheets basics: Learn how to work with Google SpreadsheetsApr 11, 2025 am 10:23 AM

Unlock the Power of Google Sheets: A Beginner's Guide This tutorial introduces the fundamentals of Google Sheets, a powerful and versatile alternative to MS Excel. Learn how to effortlessly manage spreadsheets, leverage key features, and collaborate

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)