When writing a report in Excel, most of us have this particular list that we regularly add in a specific order (e.g., a list of departments, regions, or stores).
Typing them once is fine. But doing it every single time is a different story. It can become pretty annoying, especially if you have a long list.
To escape the need to type them every time, most of us would probably resort to placing this list in a separate workbook and copying them each time we need them.
But then I discovered a more efficient way: create a custom autofill list in Excel.
Once you have set it up, whenever you need to add the list, you only need to type one item in the list and drag the Fill Handle down. Excel will then fill in the rest of the items for you.
No need to copy the list from another workbook. Pretty interesting, right?
Table of Contents
Steps to Create a Custom Autofill List
1. If you have your list typed on a workbook, open that workbook, and select all the items in that list.
If you don’t have the list typed out yet, open a new workbook and start typing them there. Once done, select all the items.
IMPORTANT: The list of items should only be in one column.
2. Go to the File menu and select More >> Options.
3. The Excel Options menu will appear. Click on Advanced.
Scroll down to the bottom part of the menu. Then, click on the Edit Custom Lists button.
4. Once you click on that button, you will see the Custom Lists menu.
Notice that there are already default lists added beforehand.
I actually didn’t know that these lists exist. Had I known, I wouldn’t have manually added the weekdays (Sunday to Monday) or the months in a year (January to December) in my previous reports.
It would have saved me a lot of time if only I typed one item in the list and let Excel fill in the rest. Well, at least we learn something new here.
Now, going back to our new list…
To create an autofill list based on the cells that we previously selected, click on the Import button.
You’ll then see your list on the Custom Lists. Click OK to continue.
And that’s it! You now have your custom autofill list that you can access anytime.
IMPORTANT: Note that the list is not linked to the cells that you have selected. So, if you need to change some of the items in the list, you can do so by either:
- Opening the Custom Lists menu and updating the items included in the List entries (you can type directly on that list box); or
- Redoing the same steps as above. Don’t forget to remove the list you previously added by clicking on the Delete button so that there won’t be a duplicate list.
Steps to Use a Custom Autofill List
Now that you have successfully added your very own autofill list, it’s time to use it.
To do this:
1. On one cell, type the first item in that list.
Note: You can actually start with any item in the list. However, if you intend to have the same order of items, you need to start with the first one.
2. Next, click on that same cell.
3. Hover your mouse on the right side of that cell until you see your mouse cursor turn into a black cross (or a plus sign).
That is Excel’s Fill Handle. By its name, we would know that it’s a handle we use to control up to where we want the autofill to be applied.
4. Drag the Fill Handle down until you reach the last cell for your list.
If you are not that sure how far down you should drag the fill handle, don’t worry!
As you drag the Fill Handle down, you’ll notice the item to be added to the current cell will appear.
Drag the mouse further until you see the last item on your list.
Once you see that, release your mouse.
5. And that’s it! You should now have your complete list added to the worksheet.
The autofill list is saved in the Excel app. You can access it at any time, even on new workbooks.
If you’re on a different computer though, you’ll have to set up the list again. But as you can see, it’s not much of a big deal as it’s easy to set up.
Sort Records based on the Custom Autofill List
I would assume that your list is specifically arranged based on a particular order.
It could be, for example, sorted by the time when the stores were opened — with the pioneering stores added on top.
When working on our reports, we often sort our records based on the numbers to see which are performing better and which are not (based on particular scales).
After sorting them that way, we then, at times, would want to sort them back based on the custom list we have created.
Now, our custom list is not necessarily in alphabetical order, so you might resort to adding another field where you would specify the order of the items. But you don’t have to do this anymore if you have a custom autofill list.
You only need to:
1. Select the entire dataset.
2. Click on the Data tab and click on the Sort button.
3. In the Sort menu, select the field that contains the custom autofill list.
In the Order dropdown, select Custom List.
In the Custom Lists menu, select the list you have created and click OK.
You will then be redirected back to the Sort menu where you will see the items in your list in the Order field. Once you see it, click OK.
And that’s it! You now have sorted your dataset based on your custom autofill list.
Conclusion
Who knew that having your custom autofill list in Excel can save you much time in preparing reports? It only takes you a few minutes to set up, and you’re good to go. You can readily add it to any workbook that you’re working on.
Also, you can use it to sort your dataset based on the order of items in this list – which is probably my favorite thing about it. I hope this article helps!