Excel Tip - Creating your own Custom List

The Fill Handle tool provides you with a quick way to enter a series of data into cells, such as months of the year and days of the week. But, did you know that you can create your own custom lists to use with the Fill Handle?

For example, maybe you want to create a list of staff members or product numbers - the fill handle will allow you to just enter the first item in the list and then click and drag to populate the other cells with the rest of the data.

  • Select the cells containing the list of data you would like to make into a custom autofill (alternatively, you can type your list directly into the Custom List dialog box, but it may be easier to type the list first into Excel so that you can edit it, or sort it etc.)
  • Go to the File tab, Options and select the Advanced category.  Scroll down towards the end of the list to find the Custom Lists button.  Clicking the button takes you to the following screen

 

The left side of the box shows the current Custom Lists.  At the bottom of the screen, it will show the cells that are selected ready to import.

  • If required, amend the cell references and then click the Import button (note: if you do not have a list selected, you are able to type the list directly into the box on the right side of the screen, and then click Add )

When the list has been imported, it will show in both sides of the dialog box:

  • Select the OK button.

Try it!  You can add as many lists as you would like - it can save an enormous amount of time when creating new spreadsheets in the future.

Pin it