Reduce cumbersome data entry and the risk of errors by creating custom lists in Microsoft Excel. Preparing a list ahead of time allows you to use auto-complete or add a drop-down list for the items, saving you time.
Microsoft Excel comes with a number of its own lists. These include things like the days of the week and months of the year. This is what makes a feature like autocomplete so useful. You just enter one word, such as Sunday or January, and drag the fill handle to complete the list.
A custom list you create works the same way. So once you̵
In addition, you can reuse your custom lists in other Excel workbooks.
Create a custom list of items in Excel
A custom list in Microsoft Excel can contain any items you like. For businesses, it’s great for a list of products, product features, employees, or locations. But you can also use a custom list for personal items, such as a beneficiary list for your accounts, food items for meal planning, or products for your grocery list.
Open your Excel workbook and access the settings with your list of items in mind.
In Windows, go to File> Options. Select “Advanced” on the left and scroll down to the “General” section. Click on ‘Edit Custom Lists’.
On Mac, go to Excel> Preferences. In the ‘Formulas and Lists’ section, click ‘Custom Lists’.
Make sure “New List” is selected in the “Custom Lists” box. Then enter your list items in the “List Entries” box. Press the “Enter” or “Return” key after each so that each item appears on a separate line.
Click “Add” and you will see your list items appear in the “Custom Lists” box. When you are done, click “OK” on Windows, or on Mac, just close the Custom Lists window.
Import a list of items from cells
If you already have your list of items in the spreadsheet, you can convert it into a custom list with the Import feature. Then you can reuse the list wherever you want without re-entering the items.
If you closed the Custom Lists window, return to it following the instructions above. Enter the range of cells containing the list items in the “Import list from cells” box. You can also use your cursor to drag through the list of cells so that the range is automatically filled. Click on ‘Import’.
You will then see the imported items appear in the “Custom Lists” box.
Click “OK” on Windows, or click the red “X” on Mac to close the window.
Use your custom list
The nice thing about creating a custom list in Microsoft Excel is that you can use it in any Excel spreadsheet or workbook. Go to a place where you want to add the list items and type one in. Use the fill handle to complete the list.
You can also follow our tutorial on how to add drop down list based on custom list for easier data entry.
Edit or delete a custom list
You may need to change a custom list that you create by adding or removing items to it. On the other hand, you may just want to delete a list that you never use. For both actions, return to the Custom Lists section.
To edit a custom list, select it in the “Custom Lists” box. Make your changes in the ‘List entries’ box and click ‘Add’. This will not create a new custom list; it will simply update your existing one.
To delete a custom list, select it in the ‘Custom Lists’ box and click ‘Delete’. Confirm this action by clicking ‘OK’.
If you find yourself repeating a list of items in your spreadsheets, make your data entry a little easier by creating a custom list in Microsoft Excel. And for more help with lists, see how to create a checklist in Excel.