Applications of Excel Fill Series (12 Easy Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Fill a Linear Series in Excel in a Column

  • Next, drag the Fill Handle tool to cell D10.
  • Then from the dropdown of the right bottom corner select the option Fill Series.

Fill a Linear Series in Excel in a Column

  • 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.

Use of Fill Command  to Fill a linear series in Excel

  • Next, select the cell range (D5:D10).

Use of Fill Command  to Fill a linear series in Excel

  • Then go to the Fill option under the editing section from the ribbon.
  • From the dropdown, select the option Series.

Use of Fill Command  to Fill a linear series in Excel

  • 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.

Fill Series with Keyboard Shortcut

  • Press the Ctrl Drag the Fill Handle tool to cell D5.

Fill Series with Keyboard Shortcut

  • Next, drag the Fill Handle from cell D6 to D10.

Fill Series with Keyboard Shortcut


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.

Keep Formatting While Filling a Series in Excel

  • Next, drag the Fill Handle tool to cell D10. From the dropdown of the AutoFill Options, select the option Fill Series.

Keep Formatting While Filling a Series in Excel

  • 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.

Filling a Series in Excel Without Formatting

    • Drag the Fill Handle tool to cell D10.
    • Select the option Fill Without Formatting from the dropdown of the AutoFill Options.

Filling a Series in Excel Without Formatting

  • 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:

Skip Rows to Fill a Series in Excel

  • In the beginning, select cells D5 and the blank cell D6.
  • Then drag the Fill Handle tool to cell D10.

Skip Rows to Fill a Series in Excel


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

Fill a Series with Formulas

  • 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 (+).

Use of Double-Click to Fill a Series in Excel

  • 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.

Excel Filling Days

  • Next, drag the Fill Handle tool by using the right-click to cell D12.
  • From the available options, select the option Fill Days.

Excel Filling 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.

Fill Weekdays in Excel

  • Next, drag the Fill Handle tool by using a right-click to cell D12.
  • Select the option Fill Weekdays.

Fill Weekdays in Excel

  • 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.

Input Initial Two Numbers in the Growth Series

  • 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.

Specify the Step Value After Inserting First Number in Growth Series

  • Next, drag the Fill Handle tool to cell D10.

Specify the Step Value After Inserting First Number in Growth Series

  • Go to the Fill option in the editing section of the ribbon.
  • From the dropdown, select the option Series.

Specify the Step Value After Inserting First Number in Growth 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:

By Using New List of Items

  • In the beginning, go to the File.

By Using New List of Items

  • Select Options.

By Using New List of Items

  • Next, go to the Advanced option. Select the option Edit Custom Lists.

By Using New List of Items

  • A new dialogue box named Custom Lists will open.
  • Enter the name of the cities in List Entries.
  • Click on the option Add.

By Using New List of Items

  • 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.

Based on Existing Items

  • A new box of commands will open.

Based on Existing Items

  • Go to the previous sheet.
  • Select cells C5 to C10.
  • Click on the insert option.

Based on Existing Items

  • Now, hit on the Import.
  • We can see our previous list in the Custom lists.

Based on Existing Items

  • 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.


Related Articles


<< Go Back to Excel Autofill | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mukesh Dipto
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

2 Comments
  1. HI,

    hiw do i fill series the middle number

    i.e.
    Unit-305/001/2023
    Unit-305/002/2023

    • Reply Avatar photo
      Musiha Mahfuza Mukta Oct 3, 2023 at 11:46 AM

      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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo