Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to create and fill new custom lists quickly in Excel?

Normally we can sort data or fill blank cell with custom list in Excel. However, there are only four kinds of built-in custom list: the day of week and the month of year, such as Sun, Mon, Tue, Wed, Thu, Fri, Sat, etc. Sometimes you may use some specific contents for many times in Excel, but without using the custom list you may have to reenter it over and over again. Actually, you can create a custom list of these contents in Excel, and then you can quickly use the custom list at any time in Excel without retyping the same contents again.

Create and fill new custom lists with Excel built-in Custom Lists

Create and fill new custom lists horizentally or vertically

Create and fill new custom lists randomly without duplicates

Quickly create and randomly fill from a new custom list without duplicates in Excel

If you have created a new custom list with Excel’s Edit Custom Lists feature, you can only fill this custom list in its original order vertically or horizontally. But, with Kutools for Excel’s Insert Random Data utility, you can easily add a range of cell data as a custom list, and randomly fill this custom list into another range easily. Full Feature Free Trial 60-day!

ad randomly fill custom list 2


You can apply Excel's Custom List feature to create a new custom list, and then fill the new custom list horizontally or vertically as you need easily. Please do as follows:

1. Click File > Options to open the Excel Options dialog box.

2. In the Excel Options dialog box, click Advanced button at left bar, scroll to the General section and click the Edit Custom List button. See screenshot:

Note: If you are using the Excel 2007, please click the Office Button > Excel Options > Popular, and then you will get the Edit Custom Lists button in the Top options for working with Excel section.

3. In the Options dialog box, please select the NEW LIST item in the Custom lists box; type each item of the custom list into the List entries box, and click the Add button. See left screenshot:

Notes:

(1) You need type the list of values separated by Enter button or comma.

(2) If the values exist in current workbook, you can click the Browse button behind the Import list from cells box to select the cells, and click Import button.

4. Click OK buttons in all dialog boxes. And your custom list has been created, so when you enter the first value of your list, and then drag the fill handle to the cell that you want to fill, your custom list values will be filled into the cells in order. You can fill the values vertically or horizontally.


With the above method, you need to enter the first value and then drag the fill handle to fill the cells, but here, I introduce a quicker tool for you. With Kutools for Excel’s Fill Custom Lists utility, you can create your own custom list and fill the values in a selected range vertically or horizontally without inputting the first value.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Click Kutools > Insert Tools > Fill Custom Lists, and then click the Edit List button in the opening Fill Custom Lists dialog box. See screenshot:

2. Now in the opening Options dialog box, please select the NEW LIST item in the Custom lists box; type each item of the custom list into the List entries box, and then click the Add button and OK button successively. See screenshot:

Notes:

(1) You need type the list of values separated by Enter button or comma.

(2) If the values exist in current workbook, you can click the Browse button behind the Import list from cells box to select the cells, and click Import button.

3. Now you get back to the Fill Custom Lists dialog box, please do as below:

(1) Select the first cell of destination range where you will random fill the new custom list;

(2) In the Fill Custom Lists dialog box, please select the Fill vertically cell after cell option or Fill Horizontally cell after cell option from the Fill order drop down list;

(3) Click to highlight the new custom list;

(4) Click the Fill Range button.

And then the new custom list is filled into the destination range vertically or horizontally as you configured See left screenshot:

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


Apart from creating a custom list and then fill the list ordinarily, Kutools for Excel’s Insert Random Data utility provides Excel users another workaround: create a custom list from excel cell data, and then randomly fill the new custom list in another column easily.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Click Kutools > Insert > Insert Random Data. See left screenshot:

2. In the opening Insert Random Data dialog box, please click the Add button on the Custom list tab; In the next popping up Kutools for Excel dialog box, please click the Browse button; and in the second Kutools for Excel dialog box, please select the list of cells which you will create as a custom list, and click the OK > OK buttons. See below screenshot:

And now you get back to the Insert Random Data dialog box with the new custom list selected.

3. Please select the range where you will randomly fill this new custom list, check the Unique Values option and click the Ok button successively in the Insert Random Data dialog box. See screenshot:

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Belinda · 3 years ago
    This was very helpful. I can see that the menu of options locations will change from computer to computer depending on the version. I'm guessing the "Help" feature can be used as a guide.
  • To post as a guest, your comment is unpublished.
    Benjamin Weir · 5 years ago
    This was great thank you. The only problem was that for me at the very start I needed to go to "POPULAR" not "ADVANCED" to find the "Edit Custom Lists" button.