In this lesson, you will learn how to fill in a data series.
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.
Are you aware of more advanced possibilities?
Fill series by double-click
Instead of dragging series down you can just double-click right bottom corner of the cell.
This is what happens in such a situation.
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
Until now, I've shown you just the left to right way. Of course filling series is possible the other way around. You can fill series from right to left and from bottom to top.
There is one thing which might be confusing for you.
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. It might be irrational for you but this is how filling series in Excel works.
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.
A new menu appeared. There are a new options available. Let's describe them:
- Copy cells - to copy the content of cells instead of filling series
- Fill series - this is exactly what we are learning here
- Fill formatting only - self explanatory; content will not be copied but formatting will
- Fill without Formatting - the other way around; will fill serie without copying the formatting of highlighted cells
- Linear Trend - just regular incremental filling
- Growth Trend - this would work e.g. when you double cells as I did in the previos example in Column D
- Flash Fill - to fill the data automatically when Excel will spot the pattern
- Series - this is exactly what I am explaining in the example below
As you can see right-click provided additional possibilities which may be quite useful for you.
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.