Creating and Modifying a Custom List

Creating and Modifying a Custom List

As demonstrated in our video on using AutoFill, Excel can guess, based on a single cell or a selected pair of cells containing a pattern of some kind, what you want to see automatically filled into any contiguous horizontal or vertical range of cells. That’s the whole purpose of AutoFill.

And as handy as it is to be able to ask Excel to enter the days of the week or the months of the year for us, it’s even handier to be able to give Excel one our own completely custom lists, have Excel store it, and then be able to use that list at any time, just by typing one of the words, phrases, or numbers in the list.

To show you what I mean, I’ve entered a list of the names of people involved in some upcoming projects. Because they’re a small team, I can just use first names, but I could use full names, or if these were vendors or customers, company names, even addresses. If it can fit in a cell, it can be in a list.

But with the aforementioned series of names selected, I’ll click the File tab and then the Options command.

In the resulting Excel Options dialog box, I’ll go to the Advanced category, and scroll down to the General section.

At the very bottom of that section, I’ll click the Edit Custom Lists button, and the Custom Lists dialog box appears.

Now, in the dialog box, you see the days of the week and months of the year lists, already stored – fully spelled out and abbreviated. That doesn’t mean those are the only lists Excel knows so far, as our AutoFill video demonstrated, Excel also knows about Quarters of the year, numbered days and weeks, and can recreate numeric patterns. But these two – days of the week and months of the year – are the ones that are officially built-in, because they’re more like proper names, not just words. And note that if I select any of them, the Delete button is dimmed, as is Add, which would otherwise allow me to edit the lists.

We’ll leave them alone and build our own, after clicking NEW LIST in the dialog box.

From that point, you can build and store your list in two ways – as I have, by selecting an existing series, already typed into row or column of cells before opening the dialog box – or you can type the list while you’re in the dialog box. I find the former method easier, plus it allows you to use the list in place, where you probably already typed it before (and know it’s right) and wished you could have Excel do it for you!

If you want to type the list, you simply type it, one cell’s entry per line, in the List Entries box.

If you want to use a selected list, note that Excel has already made note of the coordinates of my selection, and is ready to Import from that group of cells.

Once you click Add if you’re typing your list, or Import if you’re using a selection, the list is built. That’s all there is to it!

You can edit the list in the future by selecting it in the dialog box and then editing it, as shown here – where I’m removing a member of the team.

Once you’ve made your edits, click Add, and the list is stored in its edited state.

After that, just click OK, and you can now use your list in any worksheet, in any workbook you already have or generate in the future. To show you it worked properly, here’s the list of names added using AutoFill, right next to the original list – with the person who found a new job no longer in the list.

There’s also no limit to the number of lists you can make!