Home > Articles > Home & Office Computing > Microsoft Applications

  • Print
  • + Share This
This chapter is from the book

Using Series to Quickly Fill a Range

The fill handle, shown in Figure 3.12, can speed up data entry by completing a series for you. Excel comes with several preconfigured series, such as months, days of the week, and quarters. You can also add your own series, as described in the “Creating Your Own Series” section later in this chapter.

Figure 3.12

Figure 3.12. Use the fill handle to quickly fill in a series.

Extending a Series Containing Text

To extend a series containing text, enter the text you want the series to start with and press Ctrl+Enter to exit the cell, but keep it selected. Place your cursor over the lower-right corner of the cell until a black cross appears. Click the mouse button and as you hold it down, drag the fill handle.

You don’t need to start at the beginning of the series. You can start anywhere in the series and Excel will continue it, starting over if you drag the handle long enough. For example, if you begin a series in A1 with Sunday and drag the fill handle to A8, Sunday will appear again, repeating the series.

Extending a Numerical Series

If you try to fill numerical series based off of a single cell entry, Excel just copies the value instead of filling the series. There are four ways to get around this:

  • Enter at least the first two values of the series before dragging the fill handle.
  • Hold down the Ctrl key while dragging the fill handle.
  • If there is a blank column to the left or right of the numerical column, include that column in your selection when dragging the fill handle down. For example, if you enter 1 in A1 then select A1:B1 (B1 is blank); when you drag the fill handle down, the series in column A is filled down.
  • Hold the right mouse button down while dragging the fill handle and select Fill Series from the context menu that appears when you release the mouse button.

Creating Your Own Series

You can teach Excel the lists that are important to you so that you can take advantage of the series capabilities in Excel. You can take almost any list of items on a sheet and create a custom list for use in filling or sorting.

To create a custom list, follow these steps:

  1. Create your list on the sheet and select the range.
  2. Go to File, Options, Advanced, General, Edit Custom Lists.
  3. The range you selected is already in the Import List from Cells field at the bottom of the Custom Lists dialog box, so click Import.
  4. The list is added to both the Custom Lists and List Entries list boxes, as shown in Figure 3.13. The next time you type an item from your list and drag the fill handle, Excel fills in the rest of the series for you.
    Figure 3.13

    Figure 3.13. Create a custom list for use in filling in a series.

  • + Share This
  • 🔖 Save To Your Account