Do you frequently have to fill in the same data sets in spreadsheets? Just create a custom list in Excel. The next time you need one of your lists, let AutoComplete do the heavy lifting How to use Excel Flash Fill and Auto Fill to automate data entry How to use Excel Flash Fill and Auto Fill to automate data entry data With data, you have to know about Auto Fill and Flash Fill. Let us show you what these features do and how they can help you. Read more.
Once you've set up this Excel feature, you can easily fill a range of cells with your own lists, such as department names, clients, age ranges, and any other lists you might need.
Let's see how AutoComplete works and how you can create your own custom lists in Excel.
How to autocomplete in Excel
Excel includes several built-in lists, including months and days of the week. Let's look at a simple example to see how AutoComplete works.
Type Sun (for Sunday) in cell B2 . Then hover over the bottom right corner of cell B2 Until it becomes a plus sign and drag to cell H2 .
When you release the mouse button, AutoComplete automatically fills cells with abbreviations for the days of the week. You can also do this with the full names of the days of the week and the full short names of the months of the year. Autocomplete also works for numbers, dates, and times.
How does Excel know what to put in cells? The days of the week is a predefined list in Excel. You can enter any of the items in this list into a cell and drag to automatically fill in the Data Format in Excel. Spreadsheets with conditional formatting. Format data automatically in Excel. With conditional formatting. Excel's conditional formatting feature allows you to format individual cells in an Excel. spreadsheet based on its value. We show you how to use this for various daily tasks. Read more the rest of the articles. If you drag more than six more cells for the days of the week, Excel wraps to the top of the list.
How to create a custom list in Excel
You can use the custom list feature to create and store lists that you use often, such as a list of department names, employee names, clothing sizes, states, and just about any other list you can think of. These lists can be used as column or row headers or to populate drop-down lists for faster and more accurate data entry.
- To create your own custom list, click on File tongue.
- Then click on Options on the backstage screen.
- About Excel Options dialog, click Advanced . Then scroll down to the General section on the right and click Edit custom lists .
- In the Custom Lists box, you will see predefined lists of days of the week and months of the year.
There are three ways you can create your own custom list.
1. Enter your list directly
The first way to create your own custom list is to enter it directly into the Custom Lists dialog box.
- Type some list entries in the List Entries box, one item per line, and click Add .
- This is the easiest way if you have a short list not included in any of the worksheets in your book.
2. Import cells from a spreadsheet
The second way to create a custom list is to import it from cells in one of your worksheets.
- Click the cell selector button in the Import list from cells cash register.
- the Custom Lists dialog is reduced to just the Import list from cells cash register. Select the worksheet that contains the list you want to import. Next, select the range of cells containing the items in the list and click the button on the right side of Import list from cells cash register.
- the Custom Lists dialog box expands again. You may notice that the title of the dialog changes to Options . However, it is still the same dialog. Click Import to add the worksheet list items to the List Entries cash register.
- This method makes it easy to add a custom list you already have to your workbook cells.
3. Import a list of a named cell range
The third way to create a custom list is to import a list from a named cell range.
- Before opening the Custom Lists dialog box, enter each item in your list in a separate cell, either in a column or in a row on a worksheet in your workbook. Select the items, enter a name for the range of selected cells in the Box Name , and press Enter .
- Then, in the Custom Lists dialog box, enter an equals sign (= ) followed by the name you assigned to the range of cells containing your list (no space between the equals sign and the text) in the Import list from cells cash register. For example, we name our range of cells. Food , so we entered =Food . Click Import .
Note: When you import a custom list from a named cell range in a worksheet, the list in the Custom Lists the dialog is not linked to the original list in the worksheet. If you change the list in the worksheet, the custom list in the Custom Lists dialog will not change, and vice versa.
- If you have created lists on named cell ranges in your workbook, this method makes it easy to add them as custom lists that will be available in any new or existing workbook. 7 Excel Printing Tips:How to Print Your Spreadsheet Step by Step 7 Excel Printing Tips:How to Print Your Spreadsheet Step by Step Having trouble printing from Microsoft Excel? This guide will show you how to print an Excel spreadsheet perfectly. Read more.
3 examples of custom Excel lists
There are many applications for custom lists in Excel, and we can't cover them all here. But, here are some examples of how you can use custom lists to make spreadsheet creation and data entry easier and more efficient in Excel.
- If you create spreadsheets with data for departments in your company, you can create a custom list containing the names of the departments. For example, Accounting, HR, Marketing, Development, Technical Support. You can then use AutoComplete to quickly create headers for columns or rows.
- Maybe you keep track of the clothing inventory. So you may want lists for sizes (S, M, L, XL and XXL), styles (round neck, V-neck, mock neck), sleeve length (short sleeve, long sleeve, three-piece sleeve). quarters, sleeveless) and colors (Black, White, Grey, Blue, Red, Pink, Purple, Green, Yellow). From these lists, you can quickly create consistent dropdown lists containing these options.
- Are you a teacher? You can create a custom list of all your students' names and easily insert that list of names into a row or column in a spreadsheet to keep track of information like grades or attendance.
Create your own custom lists
The custom lists feature in Excel allows you to quickly and easily customize your spreadsheets to fit your needs. Once you add a custom list to Excel, it's available in all new and existing spreadsheets. 5 Excel document settings. You should never forget to review. 5 Excel document settings. You should never forget to check. When you select an Excel template or share a spreadsheet, it contains custom settings that you don't want anyone to see. Always check these settings. Read more.
What kind of custom lists do you create? Which method of adding a custom list do you prefer? Share your thoughts and experiences with us in the comments below.