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, types, units, and step values. In this article, we will illustrate how to apply the Fill Series feature in 12 easy ways.
Applications of Excel Fill Series: 12 Easy Examples
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: How to Repeat Number Pattern in Excel
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 the 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 Autofill Dates in Excel Without Dragging
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 in this example:
- First, select cell D5.
- Press the Ctrl Drag the Fill Handle tool to cell D5.
- Next, drag the Fill Handle from cell D6 to D10.
- Finally, we can see the series of ranks in Column D. Thus, you can autofill data in a column.
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 with the same value 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.
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: How to Repeat Formula Pattern in Excel
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.
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 AutoFill Months 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 the 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.
10. Fill Weekdays in Excel
In the previous example, we filled a column for all the days of the 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 a 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.
Read More: How to Autofill Days of Week Based on Date in Excel
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.
Read More: How to Perform Predictive AutoFill in Excel
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 names of cities for the students with a custom list. Let’s see how we can do this by 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 cells 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.
Download Practice Workbook
Download the practice workbook from here.
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.
HI,
hiw do i fill series the middle number
i.e.
Unit-305/001/2023
Unit-305/002/2023
Hello Orly, to fill series with the middle number, you should use a formula. In A1 cell, keep the initial value. Then go to A2 cell >> use this formula–> =”Unit-305/”&TEXT(1+ROWS($A$1:A1),”000″)&”/2023″ >> press Enter. After that, drag the Fill Handle icon up to the last cell.
Formula Breakdown
After Equal Sign, write the first fixed part of your value within an Inverted Comma (“Unit-305/”). Then, use Ampersand Operator to join the formula. Here, inside the TEXT function use summation for the increment of middle number. Also, the TEXT function will consider the mentioned pattern (“001”).
Now, again give the Ampersand Operator, and within another Inverted Comma keep the last part of the value (“/2023”).
Regards
Musiha|Exceldemy