Fill Custom List

MrExcel podcast is sponsored by "MrExcel XL", a book with 40+ Excel tips, cartoons, cocktails, tweets and jokes.

Learn Excel with MrExcel podcast, episode 1978 - Fill Handle for Apple, Banana, Cherry!

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Yesterday, in episode 1977, we talked about all of these fill handle basics, and this is great if you have to fill months, or weekdays, or quarters, but you probably have a list at work, that Excel does not know how to fill. A list of cost centers, a list of manufacturing plants, a list of product lines. And you can teach Excel that list.

Here's what to do: you just go to a blank spreadsheet, type the list in the correct order, this will be the last time you ever have to type this list on this computer! File, Options, go to Advanced, Advanced is a horribly long thing. It's not all the way to the bottom, it's 83% of the way down, "Edit Custom Lists", and you'll see, this is where they store: Sunday, Monday, Tuesday, January, February, March, we're going to import a new item to that list! So I've pre-selected the list, click Import! And now it understands Apple, Banana, Cherry, Dates, Elderberry, Fig, Guava, Honeydew, as well as it understands Sunday, Monday, Tuesday. Click Ok, click OK. Alright? And then we can type any item from the list, I'll go all caps they'll follow my lead, and go all caps, and they will fill that list. If I go too far they just start all over again!

Alright, couple things, once you set this up, it works on every workbook on this computer, in this version of Excel! Right so, if you're in Excel 2013, and you later upgrade to Excel 2016, AH, you're going to have to recreate the list. Also, if we need to edit this, I'm going to go back into Excel Options, File, Options, Advanced, 83% the way down, Edit Custom Lists, and I'll choose that list. Now if I need to change something, right? So maybe I need to change Banana to Blueberry, it looks like it worked, right? But unless you click add, the old value is still over there. All right so, don't forget to click Add, or it won't work. And you see that doesn't change the old list, but it will change all future lists, if I start from Apple and copy down, Blueberry, Cherry, Dates...

Alright now, this one is not in the book, this was, I was doing one of my live power Excel seminars, and someone asked me this question, which kind of threw me for a loop, so I ended up writing about it in the July 2016 issue of Strategic Finance Magazine! You can find it at SFmagazine.com . The guy said "Hey look, every time I create a report, I have to create 13 columns of headings, not just 12, I need all the month names, which are there, but then I need the word total!" Hmmm, wonder what's going to happen, so, if we create a custom list from this, File, Options Advanced, 83% the way down, Edit Custom Lists, and we import those, see now, the way it works Jan, Feb, Mar's right there, but Jan, Feb, Mar, with the word Total, is here, and apparently what happens, is Excel starts working from the bottom of the list. So if I type Jan it says: well there's two lists with Jan, I'm going to use the most recent one that was created. By the way, there's no way to delete this one, see the four that are built in, you're not allowed to delete. So you're stuck, but by adding a brand new one, click OK, click OK. Alright. Now every time that this guy has to create a series on his computer, he can just have the word total at the end. AND, what if he needs to do the regular one? The one with just 12? Well this is a cheat, just copy over to December, and you'll be in great shape.

Alright so, Recap: Excel can fill a lot of series, but you can create a custom list, just type the list, select the list, go to File, Options, Advanced, 83% of the way down, Edit Custom List - this is where they store January, February, March, click Import, the Import button, import the list, it'll work in every workbook on this computer in this version of Excel! When they give you a new version of Excel, going to lose all your custom lists, they're stored somewhere in the registry. It'd be easier just to take those custom list to a workbook, and then import them again. If you edit an existing list, don't forget to click the Add button, and then, showed you a trick there, adding Total to the end.

There you go, another trick from the book, "MrExcel XL - The 40 Greatest Excel Tips of All Time", and again, this one is just tip 3.B! Right, it doesn't even count as a tip, a bonus tip, between the other tips, click that "i" up there in the upper-right hand corner, and you can buy the book.

I want to thank you for stopping by, we'll see you next time for another Excel netcast from MrExcel!

Hey, want to see a good joke? If you have a co-worker who does this all the time, types Jan, grabs the fill handle and drags, just wait till he goes to get coffee and sneak over to his computer, and type this list: Jan, Marcia, Cindy, Greg, Peter, Bobby, go into File, Excel options, Advanced, 83% the way down, Edit Custom Lists, and import the Jan, Marcia, Cindy, list, because it's at the bottom, it's going to win the next time that your co-worker sits down, types Jan and drags over 12, he's going to get the Brady Bunch instead of the list! It's a riot, trust me!