How to Fill Series in Excel

There are several ways to fill series of data in Excel automatically. I am trying to describe to you all of them in the article below.

Drag and drop

Fill series of numbers by drag and drop

The most popular way to fill series in Excel is just to drag and drop the cells as in the picture below.

Fill series by drag and drop

It is enough to select a range of cells and hover the mouse cursor over the right bottom corner of the cell. After that you are able to drag cells and drop to fill series of numbers.

Fill series by double-click

Instead of dragging series down you can just double-click right bottom corner of the cell.

fill series double click

Excel filled your series of numbers automatically. Column B has been aligned to Column A. This is how this double-click works. The series will be filled to reach the end of the column on the left hand side.

Filling series right to left and bottom up

You can fill series from right to left and from bottom to top.

Fill series right to left and bottom up

Filling series from right to left you need to select your cells and drag the right bottom corner of the first cell from your right hand side.

Similarly, filling series bottom up you need to drag the first cell from the bottom side as you can see in the example picture.

See also  VBA MsgBox function

Filling defined series

There are many more series which you can fill. There are not only numbers that you can fill in your workbook.

Filling Series defined lists

As you may notice, you have a possibility to fill days of a week, dates, time, text or months. Excel will help you to fill previously defined lists. And popular ones are already predefined.

Define custom series

Of course there is a possibility to define your own user series in Excel.

To do that click File > Options > Advanced > scroll to the very bottom > Edit custom lists

Filling Series Custom Lists

You may check what kind of list are already defined in your spreadsheet. You may Add your own series or Import them if already provided in the worksheet.

Filling pattern series

Until now, we were filling easy linear series. Let’s try something more difficult.

I have tried to provide a pattern and try to check how Excel will fill series

  • cell values increased by 3
  • date increased by 7 days
  • months entered every 2 cells separated by a single blank cell
  • value doubled

Filling pattern series

The results are good but not perfect:

  • Excel has not recognized every pattern
  • Columns A and B are fine. Cells are properly incremented
  • In column C Excel properly understood months and a one-cell break but for some reason did not put a break between March and April
  • Excel missed the pattern in column D where I doubled the first three cells. I don’t understand how the series has been filled

Filling series with right click

We were using left mouse click for filling series purpose. Try to drag and drop the series using the right mouse click. See what will happen.

See also  How to Open a PRN File in Excel

Fill series right click

When you right-click a cell that is part of a data series, a menu will appear with additional options for filling the series.

Filling series without dropping

It is inconvenient to use drag when entering large amounts of data. It is much easier to use Excel’s functionality.

Click on the cell. Write the first value that you want to start with. For example, 1.

Fill Series Step value

Go to Ribbon. In the Home tab, find the Editing section. Click “Fill” and choose Series.

Fill Series Ribbon Excel

Now you can choose the Type of series. In the example below, I chose Linear Type.

Fill Linear Series

In column A, values ranging from 1 to 10000 are entered.

Series filled