Cập nhật nội dung chi tiết về How To Automatically Fill A Growth Series In Excel Using Autofill mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
Excel: How to Auto Fill a Growth Series
Previously we learned how to autofill a linear series by adding or subtracting a step value. In this tutorial, we define a growth series (hint: we multiply by a step value), and provide step-by-step instructions for autofilling a growth series in Excel. For related tutorials, see our Excel Autofill Information Center.
○ This tutorial contains affiliate links. Read our disclosure policy to learn more. ○
What is a Growth Series?
In a growth series, the next number in the series is found by multiplying the previous number by a constant or step value. This creates a geometric sequence (trend, pattern). The step value can be a positive or negative number, an integer or decimal, but it cannot be zero.
The simplest example of a growth series has a step value of 2. If our beginning number is 1, the next five numbers in the series are: 2, 4, 8, 16, and 32. Each subsequent number in our sample growth series is found by multiplying the current number by 2.
A chúng tôi Tutorial
How to Autofill a Growth Series in Excel
There are three different methods for instructing Excel to autofill a growth series. The first two use the Fill Handle and the third uses the Excel ribbon found in Excel 2007 and newer versions. Learn the method which best suits your style.
Method #1: Enter First Two Numbers in the Series
Type the first two numbers in the first two worksheet cells (figure 1).
Select these two cells, and hover the cursor over the lower right corner to display the Fill Handle (+).
With the RIGHT mouse button pressed, drag the Fill Handle up, down, or across to select the cells to be filled. Release the mouse.
From the menu that now displays, select Growth Trend (figure 2).
The autofill results are shown in figure 3. Because two numbers were entered, Excel could determine the step value.
If you want a step value of 3, enter 1 and 3 into the first two cells (1×3=3). What if you want a step value of 2, but you want the series to begin with the number 5? You would enter 5 and 10 (5×2=10) in the first two cells.
In the second method, we only enter the first number and then specify the step value on the Series window. The four images below show our process of creating a growth series starting with the number 1 and having a step value of 3.
Enter the first number in the starting cell. Then hover the mouse over the lower right edge of the cell until the Fill Handle appears.
Press the Fill Handle with the RIGHT mouse button, and drag to select the cells to autofill.
A chúng tôi Tutorial
Method #3: Using the Ribbon to Open the Series Window
The Series window can also be reached via the ribbon in newer versions of Excel.
Type the first two numbers in the first two worksheet cells.
Select these cells and the rest of the column or row that you want autofilled.
A chúng tôi Tutorial
Wrap-up
What is the difference between a growth series and a linear series? The next number in a linear series is found by adding or subtracting the step value. In a growth series, we multiply by the step value to calculate each new number.
A growth series may also be called a growth trend, a geometric pattern, or a geometric sequence.
The Microsoft website has a tutorial on how to autofill data series in Excel.
We hope you have found our Microsoft Excel tutorial on autofilling Growth Series helpful. Cheers!
↑ Return to the top
Create A Date Sequence In Excel And Auto Fill Date Series
The tutorial shows how you can leverage the new SEQUENCE function to quickly generate a list of dates in Excel and use the AutoFill feature to fill a column with dates, workdays, months or years.
Until recently, there has been just one easy way to generate dates in Excel – the AutoFill feature. The introduction of the new dynamic array SEQUENCE function has made it possible to make a series of dates with a formula too. This tutorial takes an in-depth look at both methods so that you can choose the one that works best for you.
When you need to fill a column with dates in Excel, the fastest way is to use the AutoFill feature.
Filling a column or row with dates that increment by one day is very easy:
Type the initial date in the first cell.
Select the cell with the initial date and drag the fill handle (a small green square at the bottom-right corner) down or to the right.
Excel will immediately generate a series of dates in the same format as the first date that you typed manually.
Fill a column with weekdays, months or years
To create a series of workdays, months or years, do one of the following:
To auto generate a series of days, weekdays, months or years with a specific step, this is what you need to do:
Enter the initial date in the first cell.
In the pop-up menu, choose Series (the last item).
In the Series dialog box, select the Date unit of interest and set the Step value.
In one of the previous tutorials, we looked at how to use the new dynamic array SEQUENCE function to generate a number sequence. Because internally in Excel dates are stored as serial numbers, the function can easily produce a date series too. All you have to do is to correctly configure the arguments as explained in the following examples.
Note. All the formulas discussed here only work in the latest versions of Excel 365 that support
All the formulas discussed here only work in the latest versions of Excel 365 that support dynamic arrays . In pre-dynamic Excel 2019, Excel 2016 and Excel 2013, please use the AutoFill feature as shown in the first part of this tutorial.
To generate a sequence of dates in Excel, set up the following arguments of the SEQUENCE function:
SEQUENCE(rows, [columns], [start], [step])
Rows – the number of rows to fill with dates.
Columns – the number of columns to fill with dates.
Start – the starting date in the format that Excel can understand, like “8/1/2020” or “1-Aug-2020”. To avoid mistakes, you can supply the date by using the DATE function such as DATE(2020, 8, 1).
Step – the increment for each subsequent date in a sequence.
For example, to make a list of 10 dates starting with August 1, 2020 and increasing by 1 day, the formula is:
=SEQUENCE(10, 1, "8/1/2020", 1)
or
=SEQUENCE(10, 1, DATE(2020, 8, 1), 1)
Alternatively, you can input the number of dates (B1), start date (B2) and step (B3) in predefined cells and reference those cells in your formula. Since we are generating a list, the columns number (1) is hardcoded:
=SEQUENCE(B1, 1, B2, B3)
Type the below formula in the topmost cell (A6 in our case), press the Enter key, and the results will spill across the specified number of rows and columns automatically.
Note. With the default General format, the results will appear as serial numbers. To have them displayed correctly, be sure to apply the
With the default General format, the results will appear as serial numbers. To have them displayed correctly, be sure to apply the Date format to all the cells in the spill range
Make a series of workdays in Excel
To get a series of working days only, wrap SEQUENCE in the WORKDAY or chúng tôi function this way:
WORKDAY(start_date -1, SEQUENCE(no_of_days))
As the WORKDAY function adds the number of days specified in the second argument to the start date, we subtract 1 from it to have the start date itself included in the results.
For instance, to generate a sequence of workdays starting on the date in B2, the formula is:
=WORKDAY(B2-1, SEQUENCE(B1))
Where B1 is the sequence size.
Tips and notes:
If a start date is Saturday or Sunday, the series will begin on the next working day.
The Excel WORKDAY function assumes Saturday and Sunday to be weekends. To configure custom weekends and holidays, use the chúng tôi function instead.
Generate a month sequence in Excel
To create a series of dates incremented by one month, you can use this generic formula:
DATE(year, SEQUENCE(12), day)
In this case, you put the target year in the 1st argument and day in the 3rd argument. For the 2nd argument, the SEQUENCE function returns sequential numbers from 1 to 12. Based on the above parameters, the DATE function produces a series of dates like shown in the left part of the screenshot below:
=DATE(2020, SEQUENCE(12), 1)
To display only the month names, set one of the below custom date formats for the spill range:
mmm – short form like Jan, Feb, Mar, etc.
mmmm – full form like January, February, March, etc.
As the result, only the month names will appear in cells, but the underlying values will still be full dates. In both series in the screenshot below, please notice the default right alignment typical for numbers and dates in Excel:
To generate a date sequence that increments by one month and starts with a specific date, use the SEQUENCE function together with EDATE:
EDATE(start_date, SEQUENCE(12, 1, 0))
The EDATE function returns a date that is the specified number of months before or after the start date. And the SEQUENCE function produces an array of 12 numbers (or as many as you specify) to force EDATE to move forward in one-month increments. Please notice that the start argument is set to 0, so that the start date gets included in the results.
With the start date in B1, the formula takes this shape:
=EDATE(B1, SEQUENCE(12, 1, 0))
Note. After completing a formula, please remember to apply an appropriate
After completing a formula, please remember to apply an appropriate date format to the results for them to display correctly.
Create a year sequence in Excel
To make a series of dates incremented by year, use this generic formula:
DATE(SEQUENCE(n, 1, YEAR(start_date)), MONTH(start_date), DAY(start_date))
Where n is the number of dates you want to generate.
In this case, the DATE(year, month, day) function constructs a date in this way:
Year is returned by the SEQUENCE function that is configured to generate an n rows by 1 column array of numbers, starting at the year value from start_date.
Month and day values are pulled directly from the start date.
For example, if you input the start date in B1, the following formula will output a series of 10 dates in one-year increments:
=DATE(SEQUENCE(10, 1, YEAR(B1)), MONTH(B1), DAY(B1))
After being formatted as dates, the results will look as follows:
Generate a times sequence in Excel
Because times are stored in Excel as decimals numbers representing a fraction of the day, the SEQUENCE function can work with times directly.
Assuming the start time is in B1, you can use one of the following formulas to produce a series of 10 times. The difference is only in the step argument. As there are 24 hours in a day, use 1/24 to increment by an hour, 1/48 to increment by 30 minutes, and so on.
30 minutes apart:
=SEQUENCE(10, 1, B1, 1/48)
1 hour apart:
=SEQUENCE(10, 1, B1, 1/24)
2 hours apart:
=SEQUENCE(10, 1, B1, 1/12)
The screenshot below shows the results:
If you do not want to bother calculating the step manually, you can define it by using the TIME function:
SEQUENCE(rows, columns, start, TIME(hour, minute, second))
For this example, we’ll input all the variables in separate cells like shown in the screenshot below. And then, you can use the below formula to generate a time series with any increment step size you specify in cells E2 (hours), E3 (minutes) and E4 (seconds):
=SEQUENCE(B2, B3, B4, TIME(E2, E3, E4))
How to create a monthly calendar in Excel
In this final example, we’ll be using the SEQUENCE function together with DATEVALUE and WEEKDAY to create a monthly calendar that will update automatically based on the year and month that you specify.
The formula in A5 is as follows:
=SEQUENCE(6, 7, DATEVALUE("1/"&B2&"/"&B1) - WEEKDAY(DATEVALUE("1/"&B2&"/"&B1)) + 1, 1)
How this formula works:
You use the SEQUENCE function to generate a 6 rows (the max possible number of weeks in a month) by 7 columns (the number of days in a week) array of dates incremented by 1 day. Hence, the rows, columns and step arguments raise no questions.
The trickiest part in the start argument. We cannot start our calendar with the 1st day of the target month because we do not know which day of the week it is. So, we use the following formula to find the first Sunday before the 1st day of the specified month and year:
DATEVALUE("1/"&B2&"/"&B1) - WEEKDAY(DATEVALUE("1/"&B2&"/"&B1)) + 1
The first DATEVALUE function returns a serial number that, in the internal Excel system, represents the 1st day of the month in B2 and the year in B1. In our case, it’s 44044 corresponding to August 1, 2020. At this point, we have:
44044 - WEEKDAY(DATEVALUE("1/"&B2&"/"&B1)) + 1
The WEEKDAY function returns the day of the week corresponding to the 1st day of the target month as a number from 1 (Sunday) to 7 (Saturday). In our case, it’s 7 because August 1, 2020 is Saturday. And our formula reduces to:
44044 - 7 + 1
44044 – 7 is 4403, which corresponds to Saturday, July 25, 2020. As we need Sunday, we add the +1 correction.
This way, we get a simple formula that outputs an array of serial numbers beginning with 4404:
=SEQUENCE(6, 7, 4404, 1)
Format the results as dates, and you’ll get a calendar shown in the screenshot above. For example, you can use one of the following date formats:
d-mmm-yy to display dates like 1-Aug-20
mmm d to display month and day like Aug 20
d to display only the day
Wait, but we aim to create a monthly calendar. Why do some dates of the previous and next month show up? To hide away those irrelevant dates, set up a conditional formatting rule with the below formula and apply the white font color:
Where A5 is the leftmost cell of your calendar and B2 is the target month.
For the detailed steps, please see How to create a formula-based conditional formatting rule in Excel.
That’s how you can generate a sequence of dates in Excel. I thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Date sequence in Excel – formula examples (.xlsx file)
You may also be interested in
How To Use The Excel Counta Function
Random list of names
At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called “names” which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this…
Add row numbers and skip blanks
In the example shown, the goal is to add row numbers in column B only when there is a value in column C. The formula in B5 is:
=IF(ISBLANK(C5),””,COUNTA($C$5:C5))
The IF function first checks if cell C5 has…
Cell contains all of many things
The key is this snippet:
ISNUMBER(SEARCH(things,B5)
This is based on another formula (explained in detail here) that simply checks a cell for a single substring. If the cell contains the substring, the formula…
Count cells that are blank
The COUNTBLANK function counts the number of cells in the range that don’t contain any value and returns this number as the result. Cells that contain text, numbers, dates, errors, etc. are not counted. COUNTBLANK is…
Last row in mixed data with no blanks
This formula uses the COUNTA function to count values in a range. COUNTA counts both numbers and text to so works well with mixed data.
The range B4:B8 contains 5 values, so COUNTA returns 5. The number 5 corresponds…
Count unique values
This example uses the UNIQUE function to extract unique values. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11. These are returned directly to the…
Dynamic named range with OFFSET
This formula uses the OFFSET function to generate a range that expands and contracts by adjusting height and width based on a count of non-empty cells.
The first argument in OFFSET represents the first cell in the…
Running count group by n size
The core of this formula is the COUNTA function, configured with an expanding range like this:
COUNTA($B$5:B5)
As the formula is copied down the column, the range starting with B5 expands to include each new row, and…
Count cells not equal to many things
First, a little context. Normally, if you have just a couple things you don’t want to count, you can use COUNTIFS like this:
But this doesn…
Project complete percentage
In this example if a task is marked “Done”, then it is considered complete. The goal is to calculate the percent complete for the project by showing the ratio of complete tasks to total tasks, expressed as a percentage…
Count sold and remaining
The COUNTA function counts non-blank cells that contain numbers or text. The first COUNTA counts non-blank cells in the range B5:B11 and returns the number 7:
COUNTA(B5:B11)
The second COUNTA function…
Generate random text strings
The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas.
In this example, the goal is to generate a list of random 6-character codes. The randomness is handled by…
Sort by random
The SORTBY function allows sorting based on one or more “sort by” arrays, as long long as they have dimensions that are compatible with the data being sorted. In this example, there are 10 values being sorted, the…
Score quiz answers with key
This formula uses the named range “key” (C4:G4) for convenience only. Without the named range, you’ll want to use an absolute reference so the formula can be copied.
In cell I7, we have this formula:
=SUM(–(C7:G7=…
Reverse a list or range
The heart of this formula is the INDEX function, which is given the list as the array argument:
=INDEX(list
The second part of the formula is an expression that works out the correct row number as the formula is…
How To Use The Excel Roundup Function
The ROUNDUP function works like the ROUND function, except the ROUNDUP function will always round numbers up. The number of places to round to is controlled by the num_digits argument. Positive numbers round to the right of the decimal point, negative numbers round to the left, and zero rounds to the nearest 1. The table below summarizes this behavior:
Digits Behavior
Round up to nearest .1, .01, .001, etc.
Round up to nearest 10, 100, 1000, etc.
=0 Round up to nearest 1
Example #1 – round to right
To round up values to the right of the decimal point, use a positive number for digits:
=
ROUNDUP
(
A1,
1
)
// Round up to 1 decimal place
=
ROUNDUP
(
A1,
2
)
// Round up to 2 decimal places
=
ROUNDUP
(
A1,
3
)
// Round up to 3 decimal places
=
ROUNDUP
(
A1,
4
)
// Round up to 4 decimal places
Example #2 – round to left
To round up values to the left of the decimal point, use zero or a negative number for digits:
=
ROUNDUP
(
A1,
0
)
// Round up to nearest whole number
=
ROUNDUP
(
A1,
-
1
)
// Round up to nearest 10
=
ROUNDUP
(
A1,
-
2
)
// Round up to nearest 100
=
ROUNDUP
(
A1,
-
3
)
// Round up to nearest 1000
=
ROUNDUP
(
A1,
-
4
)
// Round up to nearest 10000
Example #3 – nesting
Other operations and functions can be nested inside the ROUNDUP function. For example, to round the result of A1 divided by B1, you can use a formula like this:
=
ROUNDUP
(
A1/
B1,
0
)
// round up result to nearest whole number
Rounding functions in Excel
To round normally, use the ROUND function.
To round to the nearest multiple, use the MROUND function.
To round down to the nearest specified place, use the ROUNDDOWN function.
To round down to the nearest specified multiple, use the FLOOR function.
To round up to the nearest specified place, use the ROUNDUP function.
To round up to the nearest specified multiple, use the CEILING function.
To round down and return an integer only, use the INT function.
To truncate decimal places, use the TRUNC function.
Bạn đang đọc nội dung bài viết How To Automatically Fill A Growth Series In Excel Using Autofill trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!