Autofill A B C D...AA AB...

cmendes

Board Regular
Joined
Jan 24, 2011
Messages
66
Hello,

I remember that excel could complete a sequence is we gave it the first elements.
In this case I'm writing in three different cells (same column) A, B C and I want excel to do the rest as I drag it down: D E F and so on. But if do it I get A B C again...
Is there anything that i have to do??
I'm using Excel for mac 2011
Thanks in advance,
C
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,805
According to Help, you can continue a series of numbers, text-and-number combinations (e.g. Item1, Item2, ...) or formulas.
Sequential letters is not in that list. (What comes after Z, AA or [ ?)

You could put =CHAR(64+ROW(A1)) in the "A" cell and drag down, then copy paste values.
 

cmendes

Board Regular
Joined
Jan 24, 2011
Messages
66
According to Help, you can continue a series of numbers, text-and-number combinations (e.g. Item1, Item2, ...) or formulas.
Sequential letters is not in that list. (What comes after Z, AA or [ ?)

You could put =CHAR(64+ROW(A1)) in the "A" cell and drag down, then copy paste values.

Thanks for your response.
It does work!! Although after the Z comes '['...and not AA but it's ok
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
My Questions is...
Why do you want this?
Are you planning on using these in a formula to reference Cells later on?

There are easier ways, can you explain your larger goal?


But anyway, try this

=SUBSTITUTE(ADDRESS(1,ROWS(A$1:A1),4),1,"")
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,805

ADVERTISEMENT

If you want A, B, C, ..., Y, Z, AA, AB, ... you could use the formula

=LEFT(ADDRESS(1, ROW(A1), 4, TRUE), (ROW(A1)>26)+1)

Jonmo1, I like the Substitute approach.
 
  • Like
Reactions: Pdj

cmendes

Board Regular
Joined
Jan 24, 2011
Messages
66
My Questions is...
Why do you want this?
Are you planning on using these in a formula to reference Cells later on?

There are easier ways, can you explain your larger goal?


But anyway, try this

=SUBSTITUTE(ADDRESS(1,ROWS(A$1:A1),4),1,"")

I have an excel sheet with more than 70 parameters, each in a column.
I want to have a reference (or index) so that I can print out where each parameter is. What I did was to copy the legends from each column, paste (transpose) and next to each parameter have a Letter. Now i can print and know where each thing is.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

I have an excel sheet with more than 70 parameters, each in a column.
I want to have a reference (or index) so that I can print out where each parameter is. What I did was to copy the legends from each column, paste (transpose) and next to each parameter have a Letter. Now i can print and know where each thing is.

OK, just for display purposes. :cool:
 

cafeannalisa

New Member
Joined
Apr 29, 2017
Messages
2
Also found this of great help. But I have many more than columns than 702 (ZZ) so I wonder if the formulas could be extend to include AAA, ....
 

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Welcome to MrExcelforum @cafeannlisa

@Jonmo1's formula works without modification.

@mikerickson's formula would require modification to :-
Code:
=LEFT(ADDRESS(1, ROW(A1), 4, TRUE), (ROW(A1)>26)+(ROW(A1)>702)+1)

hth
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,114,534
Messages
5,548,617
Members
410,857
Latest member
MISJMK
Top