In an earlier tip I explained how to use the Autofill feature in Excel. It is a wonderful time saving tool. Now I will explain the Fill Series feature where numbers and dates can be used in various ways. I often get people asking if there is any way that Excel could deliver the dates for the days of the week and miss out the weekends rather than producing the whole range and deleting the weekends. Yes there is and it is in the Fill Series feature.
Lets begin with a blank sheet and a few items that we will fill down the page as we go through this tip.
Fill Series for Numbers
- Select A1 down to A10.
- On the Home Tab Select Fill tool drop down arrow.
- Select Series… from the list.
You will be presented with a dialog box like this one.
The first area ‘Series in’ shows that you have selected a vertical set of cells.
The centre area ‘Type’ allows you to change what progression you require.
The third is greyed out as we are working with numbers not dates at the moment.
- Keep the setting for Liner in the Type area.
On the left above the OK button you will find the Step value. By default this is set to 1.
- Keep this setting.
- Don’t put anything in the Stop value as you have selected where you want to stop by only selecting down to A10.
- Select the ok Button.
Your result should look like this.
- Select cells B1:B10
- Select Fill Series.
- Now change the Type setting to growth.
- Type a Step value of 2
- Select Ok
The result is this. The Step value of 2 is the multiplier.
- Select C1:C10
Here we have typed in the first cell and the second. So when you select Fill Series and leave the default settings to Linear and no step value the result is this.
- Select D1:D10.
- Select Fill Series.
- Select Growth.
- Type in a step value of 2.
- Select OK.
The result is this.
Fill Series for Dates
- Select E1:E10
- Select Fill Series
- Don’t change the Type Setting as Excel has detected that you are working with a date.
- Keep the step value as 1.
The result is this.
- Select F1:F10
- Select Fill Series.
- Type in a step value of 2.
The result is this.
- Select G1:G10
- Select Fill Series.
- Select Weekday in the Day Unit area on the right.
- Leave the step value as 1.
- Select OK.
The result is this.
I have now given you the basis to using the Fill Series feature. Enjoy creating lists of numbers or dates in a fun and quick way.
