• Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us
Excel

Obtaining Relative COLUMN Index in Excel

0 Comments

Obtaining Relative COLUMN Index in Excel. In this article, we will explore how to obtain the relative column index in Excel. Let’s consider a scenario where we have data and need to label the index for each column. However, the data doesn’t start from the first column of the worksheet. To achieve this, we can utilize the COLUMN function along with a simple logic to calculate the column index for the data.

Obtaining Relative COLUMN Index in Excel

First, let’s understand the COLUMN function. It returns the column number of a given cell or cell reference.

Syntax: = COLUMN([cell_reference])

Note: If no argument is provided to the function, it returns the column number for the cell where the formula is used.

Now, we will create a formula using the COLUMN function and a simple logic. We want to start the index with 1, so we need to find the difference of 1 between two numbers. These two numbers will be the results of two COLUMN functions. For the rest of the table, we can extend the same formula until the last column of the data.

Generic formula: = COLUMN() – COLUMN(cell_ref)

Explanation:

  • COLUMN() function returns the column number for the cell where it is used.
  • To achieve the difference of 1, we use the column reference of the left adjacent cell using the COLUMN(cell_ref).
  • This difference will automatically create the column index.
    Obtaining Relative COLUMN Index in Excel

    Obtaining Relative COLUMN Index in Excel

Let’s illustrate this function with an example:

Suppose we have a data table with names and values like this:

A B C D
1 Name Age Sex …
2 John 30 M …
3 Jane 25 F …
… … … … …

We want to mark each column with its index in the given “Index Row.”

Use the following customized formula in cell D2:

Obtaining Relative COLUMN Index in Excel

Obtaining Relative COLUMN Index in Excel

= COLUMN() – COLUMN($C$2)

Explanation:

  • COLUMN() will return 4, which is the corresponding column number for cell D2.
  • COLUMN($C$2) will return 3, the column number for column C.
  • The “$” sign fixes the cell reference to make it relative.

After entering the formula in cell D2 and pressing Enter, the result will be 1.

Now, copy the formula to the rest of the cells in the “Index Row” using the Ctrl + R shortcut or drag-right feature in Excel.Relative COLUMN Index 3

As you can see, the index column is filled with the required values.Relative COLUMN Index 4

Note: In Excel 2016 and later versions, there is an easier method to achieve the same. When you type 1 and 2 in the first and second index cells, you can select the cells and drag them right from the bottom until the column index fills automatically.Relative COLUMN Index 5

That’s it! You now know how to get the relative column index in Excel. Feel free to explore more articles on Excel cell reference functions. If you have any questions or feedback, please don’t hesitate to share them with us.

 

Rate this post
30
228 Views
50+ Common Excel Keyboard Shortcuts for Accountants to RememberPrev50+ Common Excel Keyboard Shortcuts for Accountants to RememberJuly 24, 2023
6 Ways to Fix Excel Worksheet Not Showing for AccountantsJuly 25, 20236 Ways to Fix Excel Worksheet Not Showing for AccountantsNext

Leave a Reply Cancel reply

You must be logged in to post a comment.

Buy Windows 11 Professional MS Products CD Key
Buy Office 2021 Professional Plus Key Global For 5 PC
Top rated products
  • Windows 11 Home Key Global 2021 Windows 11 Home Key Global 2021
    Rated 5.00 out of 5
    $6.00
  • Windows Server 2022 Remote Desktop Services User Connections (50) Cal Key Global Windows Server 2022 Remote Desktop Services User Connections (50) Cal Key Global
    Rated 5.00 out of 5
    $22.00
  • AVG Ultimate 2021 with Antivirus + Cleaner, Secure VPN 10 Devices 2 Years AVG Ultimate 2021 with Antivirus + Cleaner, Secure VPN 10 Devices 2 Years
    Rated 5.00 out of 5
    $47.00
  • Avast Premium Security 2021 10 Devices 1 Year Global Avast Premium Security 2021 10 Devices 1 Year Global
    Rated 5.00 out of 5
    $28.00
  • Avast Ultimate Suite 2021 3 Years 10 Devices Global Avast Ultimate Suite 2021 3 Years 10 Devices Global
    Rated 5.00 out of 5
    $90.00
Products
  • Buy Windows 11 Professional MS Products CD Key Buy Windows 11 Professional MS Products CD Key
    Rated 5.00 out of 5
    $6.00
  • Windows Server2008 R2 Remote Desktop Services device connections (20)cal Windows Server2008 R2 Remote Desktop Services device connections (20)cal $29.00
  • Windows 10 Education Key Global Windows 10 Education Key Global
    Rated 4.84 out of 5
    $9.00
  • Office 2016 Home and Student for PC Key CD Key Global Office 2016 Home and Student for PC Key CD Key Global
    Rated 4.96 out of 5
    $14.00
  • AVG Ultimate 2021 with Antivirus + Cleaner, Secure VPN 10 Devices 2 Years AVG Ultimate 2021 with Antivirus + Cleaner, Secure VPN 10 Devices 2 Years
    Rated 5.00 out of 5
    $47.00
  • Windows 10 Home Key Global Windows 10 Home Key Global
    Rated 4.81 out of 5
    $10.00
  • Project Professional 2024 Project Professional 2024 $33.00
  • Windows 10 Pro Key Global Genuine Key Windows 10 Pro Key Global Genuine Key
    Rated 4.78 out of 5
    $6.00
  • Project Professional 2021 Microsoft Project Professional 2021 - 1 PC $13.00
  • Windows Server 2019 Remote Desktop Services 50 USER Connections Key Global Windows Server 2019 Remote Desktop Services 50 USER Connections Key Global
    Rated 4.75 out of 5
    $20.00
Product categories
  • Anti Virus
  • Microsoft Office
  • Microsoft Project
  • Microsoft Visio
  • Microsoft Visual
  • Microsoft Windows
  • Other Software
  • Uncategorized

Buffcom.net always brings the best digital products and services to you. Specializing in Office Software and online marketing services

BIG SALE 50% IN MAY

Microsoft Office
Microsoft Windows
Anti-Virus
Contact Us

Visit Us:

125 Division St, New York, NY 10002, USA

Mail Us:

buffcom.net@gmail.com

TERMS & CONDITIONS | PAYMENT GUIDE  | SHIPPING POLICY  | REFUND POLICY

Copyright © 2019 buffcom.net  All Rights Reserved.