A great timesaver when entering data is to use Excel's AutoFill feature. To use the feature, enter enough cells that Excel can figure out how you want to fill the remaining cells in a series. For instance, enter 1 and 2 into two cells, or 5 and 10 into two others, or Monday and Tuesday. Select the two cells and then click and drag the Fill handle at the bottom-right corner of the selection border.
As cool as AutoFill is, an even cooler timesaving feature is to define your own series of values that AutoFill can use. Follow these steps:
Figure 1. The Custom Lists dialog box.
You can now use the custom list for the AutoFill feature. Simply type whatever entry from the custom list you want to start with, select the cell, and then drag the Fill handle. Excel fills the selected cells with the items from your custom list, in order.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6241) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: AutoFilling from a Custom List.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
AutoFill is a great editing tool that comes in particularly handy when you are first creating a worksheet. You may, at ...
Discover MoreAutoFill is a great tool for filling cells with data. What do you do when it doesn't work like you expect, however? ...
Discover MoreThe AutoFill feature can be used for more than just incrementing information into cells. This tip explains how to access ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-08-21 06:14:37
Richard Curtis
I just tried a different approach, defining a custom number format. Here is a simple example: "xxch"0"yy". In this case, a channel number has a prefix 'xx' and a suffix 'yy'. Apply this format to a range of cells (in my case, 16 cells in a column). Type 1, 2, 3 in the first three cells and autofill to the end. This gives a sequential list starting with xxch1yy and finishing with xxch16yy.
This format is only available in the current workbook, but that's okay for me.
2019-08-21 05:58:11
Richard Curtis
I'm generating some unique text strings to represent multi-channel electronic devices. Suppose I have a 16-channel device. The 16 strings will contain a load of fixed characters and embedded within those are the sequence ch1, ch2, ... , ch16. I can define a custom list for ch1 to 16, but will Excel recognise these list items within a larger string?
2017-02-20 16:49:43
Chris
very Helpful ! Thanks - just wondering if there is a short cut key for the Edit custom List . Thanks
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments