Fill Series is a helpful feature in Excel that improves efficiency in creating spreadsheets. It allows instant filling of values in selected cells based on the intervals, type, units and step values specified by the user. This article will assist all levels of Excel users in using the Fill Series in filling in numbers and dates.
Figure 1. Final result: How to Fill Series
Fill Series can be done by using the fill handle or by using the Fill > Series options in Home tab.
What is the fill handle?
The fill handle is located at the lower right corner of any selected cell. When we hover the mouse over the bottom right corner of the cell, the cursor shows a plus sign, which is the fill handle.
Figure 2. Fill Handle tool
The fill handle is a tool that enables us to copy values, dates or formulas down a column or across a row by simply dragging the fill handle, and without the use of any function.
How to use fill handle?
In order to use the fill handle tool, we simply click and drag it up/down or left/right to copy the values or contents of the selected cells to the new cells. When there are values directly to the left of the selected cell, we can double click the fill handle to initiate copying of the values along the entire column.
Fill Series
In order to launch the Fill Series dialog box, we click the Home tab > Fill > Series
Figure 3. Fill Series option in Home tab
The Series dialog box will appear.
Figure 4. Series dialog box
Fill Series examples
Prepare our data
Suppose we have the following data where column C contains the initial values. We want to fill columns D to G by applying the parameters given in column B.
Figure 5. Sample data for Fill Series
Fill Series by 1
We want to create a Fill Series with an initial value of 1, and succeeding values incremented by 1
- Select cells C3 to G3
- Click Home tab > Fill > Series
- Tick Rows, Linear with Step value “1”
As a result, D3:G3 will be filled with numbers 2, 3, 4 and 5, which shows a numeric series of interval 1.
Figure 6. Output: Fill Series by 1
Linear Series by 100
We want to create a linear series with an initial value of 1, and succeeding values incremented by 100
- Select cells C4 to G4
- Click Home tab > Fill > Series
- Tick Rows, Linear with Step value “100”
As a result, D4:G4 will be filled with numbers 101, 201, 301 and 401, which shows a linear series of interval 100.
Figure 7. Output: Linear Series by 100
Growth Series by 100
We want to create a growth series with an initial value of 1, and succeeding values with a power of 100
- Select cells C5 to G5
- Click Home tab > Fill > Series
- Tick Rows, Growth with Step value “100”
As a result, D5:G5 will be filled with numbers 100, 10,000, 1,000,000 and 100,000,000, which shows a growth series raised to the power of 100.
Figure 8. Output: Growth Series by 100
Date Series by Day
We want to create a date series with an initial value of 01/05/2009 and succeeding dates with a step value of 1 day
- Select cells C6 to G6
- Click Home tab > Fill > Series
- Tick Rows, Date, Day with Step value “1”
As a result, D6:G6 will be filled with dates having an interval of one day.
Figure 9. Output: Date Series by Day
Date Series by Weekday
- Select cells C7 to G7
- Click Home tab > Fill > Series
- Tick Rows, Date, Weekday with Step value “1”
As a result, D7:G7 will be filled with dates having an interval of one weekday. Notice that the dates 04/05/2019 and 05/05/2019 are skipped because these dates fall on weekends.
Figure 10. Output: Date Series by Weekday
Date Series by Month and Year
- Follow the same procedure but tick Month or Year in the Date unit.
Below table shows all cells filled up through the Fill Series feature.
Figure 11. Output: Fill Series
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment