Microsoft Excel – showing column letters in a row of a worksheet

I was asked on a training course recently whether they could show column letters in a row in their Excel spreadsheet. There is probably more than one way to do this, but I did it with a combination of three functions, ADDRESS, LEN and LEFT. The ADDRESS function gives the cell reference itself but you have to decide whether you want that returned as an absolute or relative reference i.e $A$1 or A1 or a mixed reference.

The syntax is =ADDRESS(row number, column number, absnum). Absnum should be 1 for absolute, 2 for absolute row and relative column, 3 for absolute column and relative row and 4 for relative.

In this case it makes most sense to use 4, so =ADDRESS(1,1,4) will return A1, etc. To be able to get cell B1, we can combine this with the COLUMN function which will make the second argument become 2, 3, etc.

=ADDRESS(1,COLUMN(A1),4)

Column1

We now need to get rid of the number 1 from each of these. That would be easy if we were only interested in the first 26 columns as we could just take the leftmost character, but after that we need AA, AB, etc.

So we need to find the length of the address using the function LEN and then keep the number of characters one less than the length. The formula is then =LEFT(A1,LEN(A1)-1) which can then be copied across the remaining columns.

Column2

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

Advertisements

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s