Learn these autofill tricks to work more efficiently with recurring data in Excel.
Entering lists of values can consume a lot of time. Sometimes, you just have to input the data yourself, but when you can, let Excel help. Today, I'm going to show you two shortcuts for completing lists. They're related only by their use of recurring data.
When you find yourself repeating a specific list of unique values, you can save time by creating a fill series that Excel recognizes and creates for you. In versions before 2013, do the following:
- Enter the list, including a header - that's the input cue you'll use to reuse the list as needed. I've entered a short list in column A (shown below).
- Select the list range A1:A6 (be sure to include the header text).
- Click the File tab and choose Options. In Excel 2007, click the Office button and click Excel Options. In Excel 2003, choose Options from the Tools menu, click the Custom Lists tab, and skip to step 6.
- In the left pane, choose Advanced.
- Scroll down to the General section and click the Edit Custom Lists button.
- Notice that the Import control already contains your selected list range, A1:A6. Click Import to create the new list (which we'll use as an autofill series).
- Click OK twice.
When you need to reuse this list, do the following:
- Enter Species in the first cell of the destination range.
- Grab the fill handle and select the destination range. Excel will recognize Species as the first item in your new list and use it to fill the destination range.
If you’re using Excel 2013, you’re in luck - reusing a list is as simple as entering a complete value and waiting for Excel to finish the list for you! In this case, enter Species and press [Enter] to move the cursor to the cell below. Enter C to start the next item, cat, but don’t finish it. Instead, Flash Fill will recognize the pattern and automatically finish the list for you. To accept the list press [Enter]. To reject it, press [Esc]. To learn more about this feature, read Excel 2013's Flash Fill anticipates needs on the fly.
The autofill series works great for the above task. If you need a series of repeating values, you can still use the fill handle, but it'll take a bit more work. For instance, let's suppose you need a list of dates, but you want to repeat each date. The first step is to generate a pattern, if necessary. If the items are unique and have no pattern, you'll have to input all those values yourself, leaving the required number of blank cells between each item. If you have a pattern, you only need the first few values - enough to create the pattern.
Then, select A2:A5 (be sure to include an empty cell after the last value) and use the fill handle to create the rest of your list. As I mentioned, if you're working with a list of unique items, such as the Species list we used earlier, you'll have to enter the values yourself. Now you're ready to fill those blanks as follows:
- With the range selected (A2:A11), press [F5] to display the Go To dialog.
- Click Special.
- Select Blanks.
- Click OK. Doing so selects only the blank cells.
- In the anchor or active cell, enter =A2 (be sure to adjust the cell reference accordingly when applying this to your own work).
- Press [Ctrl]+[Enter] to copy the formula in the active cell to all of the selected cells. Doing so will produce the repeated-date series.
At this point, you have literal dates and formulas so you might want to replace those formulas with their resulting values. To do so, select the range. Then, right-click the border and drag it a bit, as if you were going to move the data. But don't, drag it back to its original position. When you do, Excel will display a shortcut menu of options. Choose the Copy Here As Values Only option to replace each formula with a literal value.