How do I make Excel autofill in from AA to ZZ?

  • Thread starter Thread starter Rangemore Ranger
  • Start date Start date
R

Rangemore Ranger

I want to type out double letters in each cell in a single column. Like AA
in the first cell, then AB in the second cell below the first, then AC in
the third cell below the second. The whole single column should have each
cells like AA, AB, AC, AD, and so on until the last which is supposed to be
ZZ.

Is there supposed to be a way for Excel to do all that work for me?

Don’t tell me to type in AA in the first cell then try and use the cross in
the lower right corner to drag downwards, because I have tried it and all it
do is create AA, AA, AA, AA. Ever trying to type AA, then AB, then AC, then
AD, then highlight all of them and then using the drag to try and get it to
fill it in automatically don’t work either, it just fill in AA to AD, then
back to AA to AD, and so on.

Don’t tell me about the ‘Tools’ menu, ‘Option’ command, and use the ‘Custom
list’ tab, because this is only useful for it you want to use it regularly
and I only want to do it just once.

There must be something Excel can do or did the programmers at Microsoft
never thought of the possibility that someone may want to type in stuff like
AA, AB, AC, AD, etc.?

Thanks in advance to anyone who could help me.
 
Hi "Rangemore Ranger"

Thanks, this is fun.

Put A-Z in B1:B26. In A1, enter

=OFFSET($B$1,INT((ROW()-1)/26),0)&OFFSET($B$1,MOD(ROW()-1,26),0)

(one string, no spaces) and drag it down -- to A676, probably.

Copy & paste special (values) to top & tail.

Rgds,
Andy
 
put AA in 1 col AB in next AC in next, select all 2 columns, drag little box on
right corner of selection to right and you should see it filling like you want

Or put AA in col, select and pull like filling before BUT also hold the Control
key down
 
Here's another version inspired by Andy's solution but which doesn't need to
create the B1:B26 values

=CHAR(INT((ROW()-1)/26)+65)&CHAR(MOD(ROW(),26)+64+(--(MOD(ROW(),26)=0)*26))
 
Actually, if you put A1 inside row(), then you can put the formula in
starting in any row and drag fill it down:

=CHAR((ROW(A1)-1)/26+65)&CHAR(MOD(ROW(A1)-1,26)+65)

(my own version of the formula)

as an example.


Regards,
Tom Ogilvy
 
Back
Top