5 Great Tips for Using Excel Autofill

Excel Autofill provides users with a quick way to copy data, duplicate formatting, or create a fill series for a range of cells. To use Autofill, a user would position his or her mouse pointer over the small square in the lower right-hand corner of the active cell, hold down the left mouse button, and drag the fill handle down or to the right.

Autofill 1

Here are 5 tips for using Excel Autofill guaranteed to help you work more efficiently!

Tip #1 – Copy Text or Numbers Using Excel Autofill

You can copy text or numbers with the Excel Autofill feature. In the example (below), I used the fill handle to copy the number 10 from cell B2 to C2:E2. Similarly, I copied my name from B4 to C4:E4. I use Autofill to quickly copy text and numbers to adjacent cells all the time. It’s so much faster than copy and paste!

Autofill 2

Tip #2 – Create a Fill Series

Think of a fill series as a pattern. You can use the fill handle to implement several types of patterns in Excel. Dates, numbers, or headings for financial reports can be entered quickly using Autofill. In the example (below), I have entered days of the week, a series of months, and some typical headings that might be used in a financial report.

Autofill 3

The other type of fill series that people ask about involves creating a series of numbers. Patterns like these:

  • 1, 2, 3, 4…
  • 5, 10, 15, 20…
  • 10, 20, 30, 40…

In each case, you want to enter 2 values into adjacent cells so that Excel can determine the relationship between them, then you drag the fill handle to continue the sequence. In the next screen capture, I have highlighted cells B6:C6 and, when I drag the fill handle to the right, Excel will fill in the next cells with 30, 40, 50, and so on…

Autofill 4

Tip #3 – Use Autofill to Copy Formatting

In this example, I changed the font in cell B2 to Arial Black, 12pt, italic, with a yellow fill colour. Then I dragged the fill handle to cell E2 and let go of my mouse button. I clicked the Auto Fill Options button and selected Fill Formatting Only.

Autofill 5

Tip #4 – Know How to Override Autofill

Sometimes Autofill works a little too well. If I type Excel 2016 into a cell and drag the fill handle down, Excel wants to increment the ‘2016’ part of the cell and I get Excel 2017, Excel 2018, etc. If you don’t want Autofill to do this, click the Auto Fill Options button and select Copy Cells.

Autofill 6

Tip #5 – Know What to Do if Autofill Isn’t Working

If you’re trying to drag the fill handle but Excel just isn’t responding, click the File tab on the Ribbon and select Options. In the Excel Options dialog box, navigate to the Advanced tab and make sure that the Enable fill handle and cell drag-and-drop checkbox is selected.

Do you use autofill in Excel? Which technique do you like best? Answer in the comments below!