If you are not using the excel fill series in your day-to-day tasks you are wasting a lot of time
You can quickly enter sequential numbers or numbers following a pattern using the Autofill options. And since excel stores Dates & Times as numbers, these can also be easily entered.
NB: Autofill is excellent for entering a linear series where you add (or subtract) the same step value.
PS: There are 2 ways of using the fill handle ( the black + sign)…Either hold and drag or if you have data on the adjacent column, just double-click it
Using Fill Series Ribbon Command
If you find working with the Fill handle hard, you can access the Fill Series Dialog as shown below or use the Alt +H + F +I + S.
NB: For a Simple linear Series fill, you need to consider the “Step Value” i.e What value are you adding or Subtracting from the Initial value?
“Stop Value” is Optional…if not typed, the series will be filled to the last highlighted row/column. If it is typed, Excel fills the series to the last row or column of the stop value
Fill Right, Left, Down, Up, and Skip Rows
Exponential Growth Fill in Excel
Unlike the previous linear series fill where the “Step Value” is added to the “Seed Value” (initial typed) to get the “Current Value” in this growth fill type, the “Step Value” multiplies the “Seed Value” to get the “Current Value”
For example, if you start with a salary of $ 450 in January which is doubled every month, forecast your salary in December.
Daily, Weekday, Bi-monthly, Quarterly, and Yearly Fill in Excel
When it comes to Date fill, the only major difference is you can specify the “Step Value” unit in terms of Days, Weekdays, Months, or Years.
See the example below on how to generate a date series for Weekdays, Quarterly e.t.c
You can also use a negative Step Value on dates to fill a series for the past n days, months, weeks e.t.c
NB: When using the “Stop Value”, there is no need to highlight the area to populate the series.
After posting this article on Linkedin, Excel MVP Rick Rothstein pointed out that you do not have to convert the date into a number before using it as a stop value.
You can key in the actual date or time value in the stop value as shown below
How To Autofill Time
To autofill time you have to remember that excel stores time as a fraction of 24 hours.
Since there is no default Time fill series like date series shown above.
So, for Step Value, either decide on 1 hr = 1/24 = 0.04167 or choose 1 minute = (1/60)/24 = 0.0006944
How To Autofill Letters From A-Z In Excel
Unfortunately, you cannot autofill alphabets in excel.
There is a workaround using the CHAR & CODE functions to autofill alphabets
My favorite workaround is the use of custom lists.
Fill a Series Using Unique Custom Items
If you have a list that you often use and would require to autofill, excel gives you this capability.
Follow the same steps shown in the importing alphabet:
Geometric Growth Trend Fill in Excel
Geometric growth refers to an increase in values based on an average compounding rate per period for % change or % growth numbers.
% Change (Growth Rate) = Current amount / Previous amount -1
Future Value (PV) = Present Value (PV) * (1 + Growth Rate)
For excel fill series to populate this geometric growth, tick the trend box and it will ignore the step value. See below video
How to enable Autofill
Go to File ► Options ► Advanced ► Tick enable fill and cell drag
Excel Flash -Fill
Another type of time-saving Fill is FLASH FILL.
Read more on Excel Flash Fill: 15 Things You Can Do In a Flash. Part 1
Read More on Autofill:
Recent Comments