Microsoft Excel Series allows users to automatically insert a list or series of values. You probably know the easy ‘drag and drop’ method but there’s a lot more possible with linear, growth, dates and autofill. All possible with the fill handle or with more control via the Series dialog.
Whatever the reason, some people may find it hard to navigate through Excel to do so. But by simply using the Fill Handle or Series Command, users can fill in a series of values that will fit a simple linear trend or exponential growth trend.
We’ll show you easy step by step examples below using either the Fill Handle or Series command.
Linear or regular series
A linear series just by adds or subtracts a constant or step value to the current number. So, say we look at the linear series ‘1,4,7,10’… with a step value of 3 because each number in the series is three more than the previous number.
Linear series aren’t limited to just examples like these, it can include decimal numbers, negative numbers or decreasing values, but no matter what case you would add or minus the step value.
Values aren’t limited to just numbers either, as we’ll show in our example.
Linear Series with Fill Handle
By highlighting the first two values, it’s easy to use the Fill handle tool to autofill the remaining cells. Excel looks at the difference between the two values and continues that gap for the extra rows (or columns) you drag down.
Once highlighted, just hover your mouse in the bottom right corner and a plus sign will appear.
From there you can press your left mouse button down and drag the plus sign to select the cells you wish to autofill the linear data. Once you’ve released the mouse, the data will be inputted. It works for months, days of the week, and even other formats such as time increments or percentages.
Linear Series with Series Command
Another way you can use the linear series in Excel is using the Series command tool. With the previous method, you would need to have at least two sets of values for the series to work. But with the Series command, you only need to enter the first number of values of the series within the spreadsheet, at least this applies to numbers only.
Let’s look at another example. Simply highlight and select the area you wish to fill, including your first value.
Then go to Home | Editing | Fill | Series…
The series command box will appear, ensure Linear has been selected (it usually is by default) and input your series value (we’ll choose 5 for our example).
If you need to fill in your series across the worksheet, you need to click on Rows. If you need to fill in your series down the worksheet, you need to click on Columns. Note: this is usually selected by default if you’ve already selected your cells beforehand.
You can also put in a stop value if needed.
Then press OK.
As shown in the results, each unit price has increased by $5.00 (step value of 5).
Growth or multiply series
A growth series is calculated by multiplying (not adding) the starting value by the step value.
The resulting value, and all subsequent values are then multiplied by the step value. So initially you might have values of ‘1 and 3’, and based on the extended growth series, this’ll become ‘9, 27 and 81’.
Growth Series with Series Command
Continuing on from our prior example, we’ll be looking at the growth of unit sales by a step value of 3.
Simply highlight and select the area you wish to fill, including your first value.
Then go to Home | Editing | Fill | Series.
The series command box will appear, ensure Growth has been selected and input your series value. If you need to fill in your series across the worksheet, you need to click on Rows. If you need to fill in your series down the worksheet, you need to click on Columns.
You can also put in a stop value if needed.
Then press OK to receive the filled results.
Date series
You can quickly fill in a list of dates in Excel using the Series command. It’s fairly simple to fill a column or row with dates that increment by one day by purely just using the fill handle. However, for more complicated date units, such as focusing on purely months, years or weekdays, that’s when the series command becomes more useful.
See Better Ways to Add Date Lists in Excel.
Autofill series
Excel also has a feature that assists in automatically filling in data. If you’re entering in predictable data, you can use the autofill series to automatically extend that series of data, saving you time from a tedious, manual process. Basically, Excel interprets what data should be placed in the following cells, based on the given data pattern.
Although Autofill is included in the series command box, it is pretty much the same as dragging the fill handle to fill a series, as both methods will require you to provide a bit more information and values to Excel so they’re able to determine the right patterns.
Let’s go back to our first example using the fill handle tool.
If we simply leave it as the one line of dates, it’s interesting to see how autofill will interpret the data.
Back to Home | Editing | Fill | Series…
As you can see the ability to input any step or stop value has been greyed out.
Once you’re done, select OK.
As the result shows, it treats each day, date, month, and year separately, increasing each value separately by a step value of 1.
But what about if the date was altogether within one cell, like our other example? Well, autofill will just increase each day by 1 step value.
You’ll find the fill handle tool will achieve the same results. That’s why it’s important to provide more data to Excel so it’s able to interpret what exactly you need to autofill, not just it’s simple default of increasing by 1 step value.
So how about a more complicated pattern?
Excel will easily pick up on the pattern above, increasing each day by 2 step values.
However, as soon as the pattern gets a little more complicated, AutoFill will presume you want to duplicate the data.
If you want to take it one step further, check out Excel Flash Fill Magic.
Redacting Excel worksheet
What is Excel Argument Assistance?
Ukraine flag & colors in Word, Excel or PowerPoint
