MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Custom List Limits


May 28, 2017 - by Bill Jelen

Custom List Limits

I love custom lists in Excel. They are great for the fill handle and for sorting data into a different sequence. Custom Lists should allow 254 items. But for some reason, a reader is encountering a situation where Excel is only keeping the first 38 items! We will get to the bottom of this mystery.

Watch Video

  • Don wants to sort by a custom list that is numeric!
  • Will this work? It seems to work!
  • But you can not import numeric cells to the custom list dialog box.
  • So, try to type numbers into the Custom List dialog box…. You are struck with a foolish 255 character limit when typing.
  • WTH is the limit? 254 items? Aha - 254 items, but less than 2000 characters when you add the invisible comma between each item
  • Did some text math with =SUM(LEN()) and Ctrl + Shift + Enter and LEN(TEXTJOIN(",",True,Range))
  • Workaround with ABS for sorting in this particular case for Don
  • But the best workaround… the thing that Don needs to do:

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2098 custom list limits this is just
  • a bizarre one Don F using mac 2011 right
  • so we're not even using the real version
  • of Excel we're using the fake version of
  • Excel trying to sort by a custom list
  • but it's only accepting the first 38
  • items in the list and I know that's
  • wrong because Excel can handle up to 254
  • items in the list or at least that's
  • what I thought all right and Dawn has
  • player name number of wins and then
  • margin like how far off from the score
  • were they so the proper sequence is the
  • perfect score is zero and then over by
  • one under by one and then over by two
  • under by two over by three under by
  • three and so on and Don is trying to
  • sort the margin column by this custom
  • list now I've never tried that but it
  • should be easy to do so over here proper
  • sequence zero 1 and then the formulas
  • going to be minus the value just before
  • us and then equal the number two above
  • plus 1 right now that I have those two
  • formulas I should be able to cruise down
  • throw 201 too far there but that's okay
  • and we should have the exact sequence
  • that we need down to 99 and minus 99 so
  • there's our perfect set of answers I'm
  • going to copy that so ctrl C to copy and
  • then paste as values paste those values
  • like that all right so I should be able
  • to set up a custom list that will handle
  • this right no problem so we go to file
  • options advanced scroll 83% of the way
  • down choose edit custom lists and we
  • will import that list what cells without
  • simple text were ignored you're not
  • allowed to have a custom list full of
  • numbers but Don says this is working for
  • the first 38 what's up with that well
  • everything about this for awhile I
  • realized that Don must not have been
  • trying to import he must have just been
  • typing those numbers into the dialog box
  • so here's what I do I'm going to do ctrl
  • C to copy all of them I'm gonna go to
  • notepad and paste in notepad like this
  • control V and then select everything
  • edit
  • select all and control-c come back to
  • excel file options advanced 83% of the
  • way down edit custom lists and I'm going
  • to type that list in here like it's
  • control V alright and that all works but
  • we click Add the maximum length for
  • custom list has been exceeded only the
  • first 255 characters will be saved and
  • when you look at this sure house they
  • are creating a custom list that only
  • goes down into 38 - 38 39 and then BAM
  • the last three right so this is so
  • bizarre they're actually letting me
  • create a custom list with numbers but
  • they're not allowing me to get 255 I
  • mean it works it works and then if we
  • actually try and sort over here so we'll
  • say data sort and sort on this custom
  • list the one that only goes to 39 click
  • OK click ok well if it's in the list it
  • sorts correctly alright so the positive
  • sixes show before the minus 6 but then
  • once we get down to anything beyond 39
  • it's just going to sort in the sequences
  • that's not in the list so it's going
  • from smallest to largest here so
  • somebody missed by 67 points is better
  • than somebody missed by positive 42
  • points is it is just completely screwing
  • alright and what's up with this only
  • going to 38 now sure there's every other
  • number so you know I guess it's going to
  • down into the 30s we're getting down to
  • like there right are there one of those
  • too so what is that that is a total of
  • 78 items and hey I know that you're
  • allowed to to have 250 forums because I
  • talk about customers all the time of my
  • seminars all right you can have you know
  • 254 mzk let me show you
  • so item space 1 of course we can use the
  • filling idle for that I'll drag down to
  • 254 like that now those aren't formulas
  • so we should be able to be able to do
  • file options
  • advance going on to edit custom lists
  • and we will import that list all right
  • there it is BAM no problems no error
  • message everything is great everything
  • is good
  • it's not great only goes to item 234 we
  • I know you can have 254 why is it
  • stopping at 234 that's bizarre that's
  • bizarre what's up with that so here we
  • know that it's only going down to item
  • 234 right there okay now when we were
  • typing the items in the list there's
  • some number of characters there was the
  • limit so I wonder if there's some number
  • of characters that's the limit here
  • equals some of the length of that whole
  • bunch of stuff press control shift enter
  • and it's 1764 characters 234 items and I
  • know that you can have 254 I've done
  • this before and let's try something
  • crazier all right let's try this let's
  • try instead of item let's try something
  • longer so 10 characters of space and the
  • number 1 will go down 254 rows and we'll
  • try and import this list so file options
  • advanced edit custom lists we'll import
  • this list no error message seems like it
  • worked but it only goes down to 140 what
  • the heck is up with that what is the
  • limit I thought it could be 254 so let's
  • see how many characters we have if we
  • get down to 140 all right so let's leave
  • everything else after this and in fact
  • I'll come over here to this formula and
  • copy the exact same formula where all
  • right no at this point I'm pretty
  • exasperated with the excel team what's
  • up here 1764 and here 1852 hey Microsoft
  • what is the limit exactly what is the
  • limit ah but here's the thing
  • they must be storing this as a series of
  • delimited strings
  • all right so they're taking all the
  • items and then they're they're adding a
  • comma after each one alright so here
  • since we have office 365 office 365 we
  • can use the new text joint so equal text
  • join of all of those with a comment
  • between I don't know if it's really a
  • comma or not ignore emptying true comma
  • and these items so we get that and
  • actually I just want to know the link to
  • that whole thing so the length is 1997
  • and when I do the same thing over here
  • 1991 oh so clearly the limit must be
  • 2000 characters including a invisible
  • comma between each item this is all
  • pretty bizarre alright so I always
  • thought it was 254 times it's not 254
  • times is 254 items provided is less than
  • 2,000 characters provided the items
  • aren't too long all right so just to
  • test my theory let's just use a space
  • one like that and we'll grab the fill
  • handle and drag these should be really
  • nice and short because and we'll go down
  • to 255 254 let's let's go to 255 - to
  • test it alright so now with this if I
  • ask for the length of the text join
  • 14:21 there should be no Pramod also
  • select the whole thing and file options
  • advanced scroll all the way down to the
  • bottom edit custom lists click import
  • all right and turn off all the way down
  • to 254 all right so it's it's 254 items
  • provided is less than 2,000 characters
  • including a invisible comma after each
  • item is how this works you know so but
  • back to down from there
  • it sure is annoying that the dialog box
  • if we just go in and start typing things
  • in the dialog box instead of having a
  • 2,000 character element it has a 255
  • character all right so don has no way to
  • type this thing and and when we try and
  • import numbers it refuses to import the
  • numbers it says no deal anything that's
  • not plain text is not going to work
  • right so the one thing I suggested to
  • Don is an alternate solution
  • I say hey let's just come out here and
  • add a helper column and this helper
  • column is going to be the absolute value
  • of that number all right and we'll
  • double click to shoot that down and then
  • we're going to do is you're just going
  • to sort descending by the absolute value
  • a setting by the absolute value all
  • right and then the 4 6 and then minus 6
  • all right these are all just get sorted
  • together you know so it's not bad I
  • guess what you could really do is you
  • could sort by the helper and then add a
  • level and then sort by the margin
  • descending largest to smallest click
  • okay and that'll get what Don is looking
  • for so all of the positive sixes show up
  • before the minus six and then eight and
  • then minus eleven all right you know but
  • this is a hassle like hey Microsoft why
  • do we have to go to all this hassle why
  • would you let us type 2,000 characters
  • into the dialog box or even better yet
  • since it apparently works to have
  • numbers in the custom list I mean it is
  • working here why won't you let us import
  • it alright now here is the workaround
  • and I'm sorry that took this freaking
  • long to get to the end but this is what
  • I had to go through
  • all this out so I'm going to add this
  • workaround column all right and this
  • workaround column is going to be the
  • text the text of that number in quotes
  • comma our quote zero quote close paren
  • like that and we're going to copy that
  • down all right so now we're taking these
  • numbers here see the numbers are right
  • justified and we are left justifying it
  • by using the tax function unfortunately
  • though you can't use formulas in the
  • custom list if I would come here to file
  • options advanced go down to edit custom
  • lists and we'll import that no house is
  • that simple texts were ignored okay fine
  • getting really annoyed now right now
  • what I'm going to do is I want to select
  • the workaround column I'm going to use
  • the Home tab I'm going to copy it and
  • then I'm going to paste and paste values
  • if you don't do this step it's not going
  • to work and here's how to prove to
  • yourself that it works all right if
  • those are really numbers when you do the
  • sum function it's going to give you an
  • answer like this that's going to be five
  • all right but if I do the sum of the
  • work around column I'm going to get zero
  • all right and the reason is is the sum
  • can't some text all right so these are
  • real numbers these are real texts you
  • have to get it to be real text once you
  • have it as real text select the whole
  • thing go to file options advanced edit
  • custom lists we'll get rid of this old
  • custom list here that we don't need
  • we'll get rid of this list that we don't
  • need we'll get rid of this list and we
  • don't need we'll even get rid of this
  • list that only goes down to 38 and 39
  • and then finally we'll import the whole
  • list and this whole list goes from 0 all
  • the way down to 99 and minus 99 and
  • presumably store it as text
  • all right but here's the cool awesome
  • amazing thing let's just come back up
  • and we'll sort this data by name data a
  • disease now everything is just
  • completely random there and then we will
  • sort by margin and the order is going to
  • be a custom list the custom list is
  • going to be this sequence click OK and
  • click OK and it works the four sixes the
  • minus 68 minus eleven and so on now the
  • beautiful thing about this is once you
  • get it set up all right it's a hassle to
  • put these numbers over here in the off
  • to the side change them to text copy
  • paste special values and then import
  • that list but the beautiful thing is
  • once that's imported then forevermore on
  • this computer we're going to be able to
  • sort correctly by margin so Don jeez I
  • think that it's worth it to go through
  • that whole thing 617 Excel mystery
  • solved now this is a new mystery why
  • this custom list wouldn't work but this
  • book is full of stuff like this video
  • just the bizarre they arcane and so on
  • click that on the top right hand corner
  • to buy the book alright done what's the
  • sort by custom list of numeric will work
  • it seems to work well it seems to work
  • for the first 38 numbers but you can't
  • import numeric cells into the custom
  • list dialog box ah
  • so try and type the custom numbers or
  • actually I guess I pasted the notepad
  • and then from notepad I pasted but then
  • there's this crazy 255 character limit
  • when typing bet that must be left over
  • from Excel 2003 right because the limit
  • is more than that now you can have 254
  • items is it 254 times 254 times but less
  • than 2000 characters when you have a
  • visible column comma between each item
  • now we did some math here text math with
  • equals sum of the length of neutral
  • shift-enter and then the length of the
  • text join if you have office 365 and are
  • on the latest version even came up with
  • this workaround with the absolute value
  • for sorting which will help in this
  • particular case for done but I think
  • what Don needs to do the best work
  • around this is what I tell them to do on
  • the YouTube video put those numbers 0 1
  • minus 1 and a 1 to a 200 and then in the
  • column next to that equal text of a one
  • quote 0 quote close paren that's going
  • to convert it to text copy that formula
  • all the way down and then select that
  • range of formulas copy and
  • and after you copy go to the paste
  • drop-down so copy paste and paste as
  • values and if you're successful when you
  • do the sum have that whole thing is
  • going to sum to a number that's not ours
  • going to sum to zero it's going to sum
  • to zero be careful don't choose all of
  • your numbers because your numbers do
  • happen to sum to zero now the B contains
  • sex numbers you can import because of
  • custom lists well there you go Don one
  • of the crazier questions I've ever heard
  • in Excel and we've uncovered at least
  • two well definitely one bug and excelled
  • at the custom list dialog box won't
  • allow more than 255 characters and then
  • this bizarre thing that the apparently
  • will sort a custom list by numbers but
  • they won't let you import numbers
  • alright so I'm going to call that bug
  • number two and then this work around
  • here all right so hey I want to thank
  • Don for sending the question in and if
  • you hum around this long I wanna thank
  • you for stuffing bottles you next time
  • for another net cast from MrExcel

Download File

Download the sample file here: Podcast2098.xlsm

Title Photo: getbrett / Pixabay


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.