Excel Guides

Determining Columns in a Range in Excel

When working with data in Microsoft Excel, it is often necessary to identify which columns fall within a certain range. For example, you may need to know which columns are between column B and column F. There are a few different ways that you can determine this:

  1. You can use the COLUMNS function. This function takes a range as an argument and returns the number of columns in that range. So, if you wanted to know how many columns were between column B and column F, you could use the following formula:
=COLUMNS(B:F)
  1. You can also use the INDEX function. This function allows you to reference a specific cell in a range. So, if you wanted to reference the first cell in column B, you would use the following formula:

=INDEX(B1:F1,1)

If you wanted to reference the last cell in column F, you would use the following formula:

=INDEX(B1:F1,5)
  1. You can also use named ranges.

    • (Note: You can create named ranges by selecting a cell or range of cells, then clicking Formulas > Name Manager > New.)
    • >
    >

    If you have created named ranges for your data, you can use those names in your formulas instead of cell references. For example, if you have created a named range called "MyRange" that refers to cells B1:F1, you could use the following formula:

    >
    =COLUMNS(MyRange)  
    >

    This would return the number of columns in the range "MyRange".

    >
      • (Note: You can also use named ranges in the INDEX function.)
      • >
      >
    >
    1. You can also use the COLUMN function.
    2. >
    >

    The COLUMN function returns the column number of a cell reference. So, if you wanted to know which column contained a certain value, you could use the following formula:

    >
    =COLUMN(B2)
    >

    This would return 2, because B2 is in column 2.

    >

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.