5 Time-Saving Ways to Use Excel’s Fill Handle

Excel’s fill handle is one of those features that, once you find out about it, you wish you had been using it all along. Use it with numbers, dates or text to instantly create series of data — from basic to complex. Or, use it to copy and paste formulas to hundreds of cells at once. Less dragging, more productivity. For this post, I have gathered a few top tips on using the fill handle in Excel for data creation.

1) Filling Cells: Across, Up and Down

Often the process of Data Entry can be repetitive and tedious. To help with this, the fill feature of Excel can quickly repeat data from nearby cells.

Video Link

1. Highlight the cell near the cell to be replicated.
2. If the cell you want to repeat is to the left of the current cell, press CTRL+R. Press CTRL+D if the cell you want to repeat is above the current cell. If the cell you want to repeat is to the right or below the current cell, go to the Home tab, click on the Fill drop-down in the Editing group, and select either Left or Up, as appropriate.

NOTE: If you would rather not use shortcut keys the Fill drop-down has Right and Down options, use them like Left or Up.

2) Filling a Series of Data

Do you often find yourself manually entering a series of data, such as dates or a sequence of numbers? Excel’s Fill Series feature may save you some time. This feature takes a starting value, such as 1 or 1/1/2016, and fills in a range you specify with the next values that it expects in the series. (For example, a linear fill starting with 1 would fill in the next 5 cells with 2, 3, 4, 5, and 6.)

Video Link

1. Highlight the range of cells where you wish to perform the fill. Make sure that your starting value is in the first cell that you select.
2. Click on the Home tab, select the Fill button from the Editing group (on the right) and in the drop-down menu, select Series.
3. If you have highlighted a row, set Series in to Rows; if you have highlighted a column, set Series in to Columns.
4. Set Type to the type of fill you wish to perform:
   * Linear and Growth work with numeric values; linear adds the value set in Step value to the contents of the previous cell, while growth multiplies the step value by the contents of the previous cell. (For example, a growth fill, starting with 1, with a step value of 2, would fill: 1, 2, 4, 8, 16…)
   * Date fills work with date values and fill based on the unit set under Date unit.
   * AutoFill fills work with any kind of data that Excel recognizes as a set; in order for this option to work, you must have a recognized set in at least the first two cells you have selected. For example, 1, 2 begins the set 1, 2, 3, 4, 5… This also works with dates, days of the week, etc.

Excel will now automatically populate the series for the range you have selected, leaving you free to move on to the next task at hand.

3) Copying Formulas Using the Fill Handle

There are multiple ways to copy and paste formulas from cell to cell, but the quickest by far is by using the fill handle.

Video Link

1. Single-click on a cell that contains a formula. You should see a black bold line around it, and a black square in the bottom right corner. When you move your mouse cursor over that square, the cursor turns to a black cross or plus sign. This is the fill handle.
2. By clicking and holding the left mouse key, you can drag the fill handle down a column or across a row. You will see the cell(s) you are copying surrounded by a less prominent border. You can also select multiple adjacent cells in the same row or column, and drag down or across in the same way to copy multiple rows or columns of cells at the same time.

Be sure to format your cell references accordingly if you plan to use the fill handle. Absolute references will stay the same when copied, but relative references will adjust as the formula is copied to each cell.

4) Filling Cells with More Options

Sometimes, you need more than the standard fill option that the fill handle gives you.  You are able to select more options by right-clicking and dragging on the black square box in the bottom right corner of the cell you have selected.  This will give you several more options to choose from!

Video Link

5) Creating a Custom Series for Filling Data

Excel is a powerful tool for creating and organizing data, but we do not always want to order items in a standard or recognizable way. When dealing with dates, Excel can put the days of the week or months of the year in order, but sometimes data is much more useful when a special list is used to create and organize it. Fortunately, Excel gives us the ability to create our own custom lists for filling and sorting data. You can even turn existing data into a custom list for future use.

Video Link

1. Click the File tab in the top-left corner of the Excel window. Select Options at the bottom of the left-hand column.
2. In the list on the left of the Excel Options window, click the Advanced tab.
3. On the right, scroll to the bottom and click the Edit Custom Lists button.
4. In the Custom Lists dialog, select NEW LIST from the column on the left labeled Custom Lists.
5. In the right column, type each item in your new list in order, then press ENTER after each addition to move to the next line.
6. When your list is complete, click Add and the list appears in the Custom Lists area on the left.
7. Repeat steps 4 through 6 for any additional lists and click OK to close the dialog.

TIP: You can edit your list by returning to this dialog, selecting your list from the Custom lists column on the left, and editing the listed items on the right.