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.
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.
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.
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.
Filling defined series
There are many more series which you can fill. There are not only numbers that you can fill in your workbook.
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
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
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.
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.
Go to Ribbon. In the Home tab, find the Editing section. Click “Fill” and choose Series.
Now you can choose the Type of series. In the example below, I chose Linear Type.
In column A, values ranging from 1 to 10000 are entered.
