The Excel Fill Series is a helpful feature that increases the efficiency of creating a spreadsheet. It allows quick filling of values in selected cells. The filling of values depends on user-specified intervals, type, units, and step values. In this article, we will illustrate how to apply the Fill Series feature in 12 easy ways.
Download Practice Workbook
Download the practice workbook from here.
12 Applications of Excel Fill Series
The Fill Series feature has a wide range of applications. Depending on the type of work it increases the convenience of a process.
1. Fill a Linear Series in Excel in a Column
First and foremost, we have the dataset of six students and their obtained marks. In this method, we will fill the Rank column using the Fill Series feature. Just follow the steps to perform this action:
- First, select cell D5.
- Next, drag the Fill Handle tool to cell D10.
- Then from the dropdown of the right bottom corner select the option Fill Series.
- Finally, we can see the series value in the Rank.
Read More: [Fix] Excel Fill Series Not Working (8 Causes with Solutions)
2. Use of Fill Command to Fill a Linear Series
Another convenient way to fill a linear series is to use the Fill command. We will continue with the previous dataset for this example. Let’s see how we can do this:
- In the beginning, select cell D5.
- Next, select cell range (D5:D10).
- Then go to the Fill option under the editing section from the ribbon.
- From the dropdown, select the option Series.
- A new dialogue box will open. Select the following options:
Series in: Columns
Type: Linear
Step Value: 1
- Press OK.
- So, we get the series of ranks in the Rank.
Read More: How to Use VBA AutoFill in Excel (11 Examples)
3. Fill Series with Keyboard Shortcut
The use of keyboard shortcuts is always an easy way to do any work. We can use this approach to Fill Series also. We will use the same dataset that we used for the previous example. Just follow the steps to this example:
- First, select cell D5.
- Press the Ctrl Drag the Fill Handle tool to cell D5.
- Next, drag the Fill Handle too from cell D6 to D10.
- Finally, we can see the series of ranks in Column D.
Read More: How to Apply AutoFill Shortcut in Excel (7 Methods)
4. Keep Formatting While Filling a Series in Excel
Sometimes we may need to keep the formatting. In the following dataset, the rank of the first student is color formatted. Here we will fill the rank series as well as keep the formatting. Let’s see step by step how we can do this:
- Firstly, select cell D5.
- Next, drag the Fill Handle tool to cell D10. From the dropdown of the AutoFill Options, select the option Fill Series.
- Finally, we get the series value of ranks in the Rank.
Read more: Automatic Numbering in Excel
5. Filling a Series in Excel without Formatting
We can also fIll a series without keeping formatting. In the following dataset, we can see the formats of the first two values of the column Rank. Let’s see how we can fill the series without keeping formatting:
- Select cells D5 & D6.
- Drag the Fill Handle tool to cell D10.
- Select the option Fill Without Formatting from the dropdown of the AutoFill Options.
- Lastly, we can see the value of the series without formatting.
Read More: Fix: Excel Autofill Not Working (7 Issues)
6. Skip Rows to Fill a Series in Excel
We can Fill a series by skipping rows which means we will get the value after every interval of one row. Let’s see how we can do this with the same dataset:
- In the beginning select cells D5 and the blank cell D6.
- Then drag the Fill Handle tool to cell D10.
- Here, we can see that the series is filled by skipping one row.
Read More: Filling a Certain Number of Rows in Excel Automatically (6 Methods)
Similar Readings
- Drag Number Increase Not Working in Excel (A Solution with Easy Steps)
- Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows
- How to Auto Number or Renumber after Filter in Excel (7 Easy Ways)
- AutoFill Ascending Numbers in Excel (5 Quick Ways)
- How to Fill Down Blanks in Excel (4 Quick Methods)
7. Fill a Series with Formulas
In the case of filling a series, we not only can fill values but also can fill a series with formulas. In the following dataset, we have a column of total marks of students in column E which is the sum of columns C & D. We will fill the column Total keeping the formula of summation. Let’s see how we can do this:
- First, select cell E5. Insert the following formula:
=C5+D5
- Next, drag the Fill Handle tool to cell D10.
- So, we get the values of totals in series with corresponding formulas.
Read More: How to Use Autofill Formula in Excel
8. Use of Double-Click to Fill a Series in Excel
The use of double-click is a very simple way to Fill a series. We will use the dataset of our previous example. We just need to go through the following simple steps:
- Firstly, select cell E5.
- Next, hover the cursor at the bottom right corner of the cell.
- Then double-click on the plus (+).
- Finally, we get filled series in the total column.
9. Excel Filling Days
Until now we have used the Fill Series option only for numeric values. In this example, we will use the Fill Series option to fill days. We will use the following dataset to illustrate this example. Let’s see how we can do this:
- In the beginning, select cell D5.
- Next, drag the Fill Handle tool by using the right-click to cell D12.
- From the available options, select the option Fill Days.
- In the end, we can see the days filled in Column D.
Read More: How to Autofill Days of Week Based on Date in Excel
10. Fill Weekdays in Excel
In the previous example, we filled a column for all the days of a week. Now in this example, we will do the same process with the same dataset only for weekdays. Just follow the below steps to perform this action:
- First, select cell D5.
- Next, drag the FIll Handle tool by using right-click to cell D12.
- Select the option Fill Weekdays.
- Finally, we get only the weekdays in Column D. We can see that Saturday and Sunday are not present in the series.
11. Use of Growth Series Option to Fill a Series
In Excel, Growth Series finds the next number in the series multiplying the previous number by a constant or step value. We will go through two methods in this section to use the Growth Series option.
11.1 Input Initial Two Numbers in the Growth Series
In this case, we have the following dataset with Unit Price and Unit Sales. We have the first two values of unit sales. Here we will use the Growth Series option to fill the series in the following steps:
- Firstly, select the given two values.
- Next, drag the Fill Handle to the bottom by using right-click.
- Then, select the Growth Trend.
- Lastly, we can see that all the values in the Unit Sales column are twice their previous value.
11.2 Specify the Step Value After Inserting First Number in Growth Series
For this example, we will use only one value for the Growth Series whereas in the previous example we used two. We will use a step value for this method. Let’s see the necessary steps related to this example:
- First, select cell D5.
- Next, drag the Fill Handle tool to cell D10.
- Go to the Fill option in the editing section of the ribbon.
- From the dropdown, select the option Series.
- A new dialogue box will open. Check the following options:
Type: Growth
Step value: 2
- Press OK.
- Finally, we get the value of Unit Sales in a series.
12. Use of Custom Items
We can also fill a series with our own custom items. While working in Excel, sometimes we may need to Fill a series with custom items. For this purpose, we will demonstrate the following two methods.
12.1 By Using New List of Items
In this example, we will input a new list of items. After that, we will be able to use the list in any place of the datasheet. In the following dataset, we will fill the name of cities for the students with a custom list. Let’s see how we can do this following the below steps:
- In the beginning, go to the File.
- Select Options.
- Next, go to the Advanced option. Select the option Edit Custom Lists.
- A new dialogue box named Custom Lists will open.
- Enter the name of the cities in List Entries.
- Click on the option Add.
- We get a new list in the Custom List
- Press OK.
- Now, insert the first value of the list in cell D5.
- Then drag the Fill Handle tool to cell D10.
- Finally, we can see the whole list of cities is copied in Column C.
12.2 Based on Existing Items
In the previous example, we created a custom list. On the other hand in this example, we will use our existing list to input the values in series. Just do the following steps to perform this action:
- Go to the Custom Lists dialogue box.
- Select the import list option.
- A new box of commands will open.
- Go to the previous sheet.
- Select cell C5 to C10.
- Click on the insert option.
- Now, hit on the Import.
- We can see our previous list in the Custom lists.
- After that, enter the first value of the list manually in cell C5.
- Then, drag the Fill Handle tool to cell C10.
- Finally, we get our previous custom list of cities in our new column of cities.
Conclusion
In this article, we have tried to cover almost everything about the Excel Fill Series. For greater efficiency download our practice workbook added to this article and practice yourself. If you have any queries or have any kind of confusion just leave a comment below. We will try to reply to you as soon as possible.