How to use Fill Series in Excel sheet
The Fill Series function in Excel is a powerful tool that allows users to quickly and easily fill in a series of numbers, dates, or other data in a selected range of cells. This can be particularly useful when working with large amounts of data or when creating formulas that rely on a series of sequential values. With version 8.0, ONLYOFFICE Spreadsheet Editor has introduced this new function. Read this article to find out how to use Fill Series in Excel sheets!
What is Fill Series in Excel?
Fill Series function allows users to quickly and easily generate a series of numbers, dates, or other data points based on a pattern or rule. There are many situations where you might need to generate a series of data points in Excel. For example, you might need to create a list of dates, or generate a series of numbers that increase by a specific amount each time. In these cases, manually entering each data point can be time-consuming and error-prone. Fill Series allows you to quickly and accurately generate these series of data points, saving you time and reducing the risk of errors.
Let’s see some cases in which you would use Fill Series:
- Sequential numbers or dates: You need to create a list of sequential numbers or dates, for example, you need to create a list of dates for the next 30 days. You can enter the first date and then this function will fill in the rest of the dates in the series.
- Series with a pattern: You need to create a series of numbers or dates with a specific pattern, for example, a list of numbers that increase by 5 in each cell.
- Series with a stop value: You need to create a series that stops at a specific value, for example, you need to create a list of numbers that increase by 2 each time and stops at 100.
- Series with a specific growth rate: You need to create series of numbers that increases or decreases by a specific percentage each time, for example, if you need to create a list of numbers that increases by 20% in each cell.
How to Fill Series in Excel?
Now that you identified your goal, you can try and use the tool. Let’s do it together by creating a simple series. In order to do it, we will put to the test the Fill Series in ONLYOFFICE Spreadsheet Editor.
We will start with a very easy example: let’s create a series of numbers that increases by 5 in each cell.
Let’s imagine that we want the first number of our series to be 10. Let’s write 10 in the cell where we want the series to start. Then by clicking on the small square at the right bottom of the cell, we select the cell range by dragging the cursor. We will see that Spreadsheet automatically copied the value “10” in all the cells we selected.
Now let’s click on the icon Fill in the toolbar on top and choose the option Series.
As we said before, we want the numbers to increase by 5, so we write the number 5 in Step Value. We can choose to show the series either in rows or in columns. Then we click OK.
The function helped us fill the series automatically and we now have the sequence we needed.
Now let’s imagine that we don’t have a precise cell range in which we want the sequences to be displayed, but we want the series to stop at the value of 100.
For our ease, the first number of the sequence will be 10. We write this value in the cell we need, then we open the Fill tab once again.
Now, as we did before, we will enter the number 5 in the Step value, but we also want the Stop value to be 100. Once we entered the values, we click OK.
Now our sequence is ready.
Shortcut key for Fill Series in Excel
In order to work with Series, you can also use a keyboard shortcut. For example, you want to obtain a simple linear sequence but by using the keyboard only.
To do so, enter the first value in cell A1. Now select the cell, click on Ctrl and drag the fill handle tool to the needed cell.
Access the Series menu
To access the Series menu you can either use the toolbar on top or the fill handle. To do so, select the initial data, drag the cursor over the fill handle in the right lower corner of the cell, click and hold the right mouse button on the fill handle, then drag it wherever you need and release the button: the context menu with the Series option will appear.
By doing so, you will be able to use the following options to fill in the selected empty cells:
- Copy cells to fill cells with the same values;
- Fill series to get a series using the default step value;
- Fill days to enter a series of dates;
- Linear trend to add the default step value to the starting one and then to each subsequent;
- Growth trend is available if more than one starting value is selected, and is used to multiply the starting value by the default step value.
Why is Excel Fill Series not working?
Sometimes the function might not work. This is probably due to some mistakes that can be easily fixed.
The most common mistake is the cell format. For example, if you need to work with percentages, make sure that you chose the right number format. You can check it and change it in the Home tab of the toolbar on top.
Another common mistake is using a filter that you might have set before creating the series, and now you can’t see some of the data you entered. Make sure to clear the filter before using the Fill Series.
Now you know how Fill Series can help you automate your work with sequences, and you might want to give it a try! The latest version of ONLYOFFICE Docs offers this and many other functions.
If you don’t have a ONLYOFFICE Docs account yet, you can create one for free right now and test the capabilities of its Spreadsheet and all the other editors. Try it online or with our desktop applications.
