FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
January 21, 2016

Greetings from The Excel Addict
Hi fellow Excel Addict,

Francis Hayes (TheExcelAddict.com)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)
Email: 
fhayes[AT]TheExcelAddict.com





If you missed my last newsletter, you can click here to view it online.

TheExcelAddict.com Quote of the Day

“People are always blaming their circumstances for what they are.
I don't believe in circumstances. The people who get on in this
world are the people who get up and look for the circumstances
they want, and if they can't find them, make them.”

-- George Bernard Shaw --

If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

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 FileOptions and then click the Formulas category in the Excel Options dialog. I
n 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.
 
Show Column Heading Numbers in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

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.

Use Column Function To Determine Column Number in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

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 Numbers In Name Box in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

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
 
Row And Column Numbers In Name Box in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365
 
CTRL+SHIFT+Home
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. 

Press Ctrl+Shift+Home To Get Column Number in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365
If you have Freeze Panes enabled for columns, this will not work.

Ctrl+Shift+Home Doesn'T Work With Freeze Panes in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365




Thanks for supporting this newsletter and website

FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week
"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