By using the Column formula, miscalculating the exact column reference is bypassed and, therefore, uncertainty in determining the column location is eliminated. The formula is also useful for managing the space in a spreadsheet.
For instance, by calculating the distance from the first column to the end of a known range, you can better plan for placement of data in other cells within the same spreadsheet. Follow a few basic steps to learn how to use this valuable yet simple formula, and you will soon know how to reference the location of any cell anytime and anywhere within any open spreadsheet.
- Open Excel. As shown in the graphic depiction, type in the names of the months in cells B2 through M2. Next, enter the word "Apples" in cell A3. In cells B3 through M9, enter random figures as shown in the illustration. (Note: If you prefer, you can create a similar chart to the one shown.)
- In cell A5, enter the following formula: "=column()." This will return the column reference for the exact place where you have just entered the formula.
- In cell A5, enter the following formula: "=column(F3)." This will return the column number reference for cell F3, which is in the sixth column across within the spreadsheet.
- In cell A5, enter the following formula: "=column(Z4)." This will return the placement number of the column referenced. Cell Z4 is in the 26th column within the spreadsheet. (Note: The referenced column can be empty because it is referencing the column number placement only.)
This formula does not work with finding columns within ranges. It is a specific column number reference type formula only.