I had a lot of nice comments on my winter photos from Tuesday's newsletter, so I decided to share some more with you.
Today's tip, I think, will help you in some areas that you may not even know you need help. How many times do you find yourself wondering, "How many columns are between here and there?'" or "What column 'number' is this?". If you're like me, you've probably manually counted the number of columns. No more. I'll explain below and give you several options to make this task easier.
I hope you find it useful.
Please feel free to share this with anyone else you think it may help.
Take care and keep on Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
How to Easily Determine Column 'Numbers'
Excel displays letters for column headings. But what if you want to know which column number you are dealing with? ...or you want to count how many columns you have selected.
In the past, I often found myself manually counting the number of columns between two cells. But fortunately I discovered some easier methods.
Here are some of them:
Change R1C1 Style Option
Click File, Options and then click the Formulas category in the Excel Options dialog. In the 'Working with formulas' section, check the 'R1C1 reference style' option, then click OK. You will see that the column letters in Excel have now changed to numbers.
Use the COLUMN Function
In any cell, enter the formula =COLUMN() or =COLUMN(cell_address). The formula result will be the column number. In this example, you can see that column Y is the 25th column.
Row and Column Numbers in Name Box
Click in column A and highlight the cells across to the column you want to find the number of. Note that as you drag, before you get to the edge of the screen, the number of rows and columns you have selected is displayed in the Name Box to the left of the Formula Bar.
Row and Column Number in Popup
When you select a range of cells and drag drag past the edge of the screen, you'll see a small popup box next to the mouse pointer (i.e 1R x 25C) indicating the number of rows and columns you have selected. In this example, you can see that, when starting from column A, column Y is the 25th column
Another option is to select the cell you want to find the column number for and press CTRL+SHIFT+Home. If the range you have selected is less than a screen width, the number of rows and columns will be displayed in the Name Box to the left of the Formula Bar. If more than a screen width, you'll see the row and column numbers pop-up on the left edge of your worksheet. To quickly bring focus back to the active cell, simply press the up arrow once and the down arrow once.
If you have Freeze Panes enabled for columns, this will not work.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630