Five ways to take advantage of Excel list features
The term list can mean many things in Excel. Typically, a list is a single column of items that are meaningful to the sheet’s purpose. They’re important to Excel—and for that reason, Excel offers several ways to make and manage lists. In this article, we’ll review five built-in tools for working with lists in Excel.
I’m using Excel 2016 (desktop) on a Windows 10 64-bit system, but most of these methods will work in earlier versions. You can download the .xlsx and .xls demonstration files or you can work with your own data. The browser edition supports AutoComplete but not lists on the fly, and advanced filtering isn’t available. The browser edition does support data validation in an existing sheet, but you can’t create new lists.
If you want to copy the value from directly above, you press Ctrl+’, but AutoComplete goes a step further. As you enter new data, Excel anticipates the value you might be entering by comparing your input with previous entries in the same column. This method is the easiest way to create a list because you don’t have to do a thing but take advantage of it. In Figure A, all three columns benefit from this behavior. To demonstrate, select B16 and type l for Louis. The last entry for Louis is several rows up, but AutoComplete recognizes your input and finishes the value. Simply press Enter to complete the task. If you don’t want the AutoComplete value, keep typing the new value. This feature’s only requirement is that the values be contiguous.
AutoComplete finishes your entry for you.
AutoComplete is efficient, and it prevents typos. It works in a normal data range and in a Table. However, you can disable it by clicking the File tab and choosing Advanced in the left pane. In the Editing section, uncheck the Enable AutoComplete For Cell Values option.
SEE: Build your Excel skills with these 10 power tips (TechRepublic PDF)
2: On the fly
Similar to AutoComplete, Excel offers an on-the-fly list based on the previous values in a single column in a normal data range or a Table. Instead of completing the value as AutoComplete does, this feature offers a pick list. You simply select the right item. To demonstrate, let’s enter a new record into our sample data set:
- Select B16 and press Alt+Down Arrow. Excel displays a pick list (Figure B).
- Select an entry and press Tab to move to C16. Press Alt+Down Arrow to see that column’s pick list.
- Select an item and press Tab to continue.
Use Excel’s on-the-fly pick list.
3: Unique list
Using an advanced filter option, you can generate a new list from existing values, and doing so is easier than you might think. To illustrate, let’s create a unique list of names from the values in column B as follows:
- Click anywhere inside the data set.
- Click the Data tab and then click Advanced in the Sort & Filter group.
- In the resulting dialog, click the Copy To Another Location option.
- Make sure the List range is correct. The feature defaults to the contiguous data range, $B$2:$D$15. We want a single-column list, so replace $D with $B, if necessary.
- Click inside the Copy To control and enter a cell reference or click a cell to identify the first cell in the new list range (F3).
- Before clicking OK, be sure to check the Unique Records Only option (Figure C). Then, click OK. Excel will generate the list shown in Figure D.
If you don’t check the Unique Records Only option, Excel will copy the original lists—duplicates and all.
The list in column F contains unique values from column B.
Unlike most tasks, Undo won’t remove the resulting list. Use the Clear All option from the Clear drop-down in the Editing group on the Home tab to remove values and formatting. This technique works with a normal data range and a Table.
SEE: Windows 10 power tips: Secret shortcuts to your favorite settings (Tech Pro Research)
4: Unique data set
You can use the same feature to generate a list of unique records. Repeat the steps in step 2. Reference the entire range, as shown in Figure E, instead of the single column. Figure F shows the resulting data set of unique records. You can use this feature in a normal data range and a Table.
Filter the data set instead of a single column.
If you specify the data range instead of a single column, Excel produces a set of unique records.
5: Validating input
Excel’s Data Validation feature lets you limit entries to a specific set of values. In doing so, you can ease data input and eliminate errors. Validation lists aren’t particularly useful for entering new records as we’ve been doing because the list belongs to a single cell, not a column—data validation doesn’t extend to new rows the same way formulas and formatting do. Use on-the-fly lists or AutoComplete instead.
But validation lists are helpful when you want to provide or limit users to a specific set of values you can use in other formulas and functions. Figure G shows a simple use of a validation list. Choose a person from the validation list in C1, and the SUMIF() function in C2 updates accordingly.
This simple validation list is simple to implement and provides an easy-to-use interface.
To create this solution, add a validation list to C1 as follows:
- Select C1 and then click the Data tab.
- Click Data Validation in the Data Tools group and choose Data Validation from the dropdown list.
- Select List from the Allow dropdown.
- In the Source control, enter =$G$6:$G$9 (this list was in column F until I added the column of totals).
- Click OK.
To finish this simple solution, enter the following function into C2:
Everything’s in place, so choose a name from the validation list and the SUMIF() function updates accordingly. Figure H shows the result of choosing Louis.
Choose an item from the validation list to update the function in C2.
This solution isn’t the only way to get subtotals, but it’s easy to implement and users will have no trouble implementing it. Furthermore, the resulting total is easy to reference in other expressions because it’s stable. It’s not perfect though—the unique personnel list in column G isn’t dynamic. If you want to add a fifth person, you’ll have to modify the validation list source. Or you can take the easy route and convert the unique list in column G into a Table, as shown in Figure I. (The Table object isn’t available in the menu versions of Excel.) After converting the data range to a Table, I simply added a new record and the dropdown list updated automatically.
Make the unique list a Table to create a dynamic list.
Next month, we’ll continue this topic. Specifically, we’ll use validation lists to create a dynamic data entry solution for entering full records.
Send me your question about Office
I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at firstname.lastname@example.org.