In Excel, there are 3 different ways to fill a series of numbers or dates
- The fill-handle
- The fill menu
- SEQUENCE function
Fill series of numbers
If you want to create a series of numbers quickly, the trick is simple
- Write the first value
- Write the second value
- Select the 2 cells
- Use the fill handle to extend the selection
Excel will extend the selection according to the step between the 2 numbers.
Look at this video to see how it works with positives, negatives, and decimal numbers.
Fill series for thousands of data
But if you have to create an extensive list of numbers, using the fill handle is useless because it takes too much time 😤
It's better to use the tool Home > Fill > Series
In the dialog box, you fill these parameters
- Select the cell containing the starting value
- Select the orientation (here columns because you want the result in the same column)
- Indicate the step (by default it's 1)
- Indicate the last value to reach
And the result is a list of values starting from 1 to 5000 with a step of 2 😀👍
Fill a series with SEQUENCE
If you work with Excel 365, Excel Online, or Excel 2021, you can use the SEQUENCE function to create your series of number
=SEQUENCE(number of rows, number of columns, starting value, step)
For instance, to create a series from 0 to 50 with a step of 5, you write the following formula
Then, to convert the result of your formula to value, you must perform a copy-paste value
Create a series of dates
If you want to create a series of dates, the 3 same techniques (fill handle, tool Fill series and SEQUENCE) can be used. But also Excel respects the number of days in a month.
For example, you can easily create a series of weeks with a step of 7 days
And the same technique can be used with the months.
- You write the 2 first months
- You select the 2 cells
- Expand the selection
Other Excel tricks
Here is a list of articles that could be also useful:
- Move a cell with the mouse
- Move a worksheet to another workbook
- Increase the font size of the formula bar
11/10/2022 @ 14:18
Thank you so much for providing such an intresting information.