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.
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.
Find out more about our Microsoft Excel training London, UK.
Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.