Display Trends using AutoFill
by Avantix Learning Team | Updated February 18, 2021
Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019 and 365 (Windows)
This article is the third in a series of simple ways to show trends in your Excel data.
Recommended articles: Simple Strategies to Show Trends in Excel (Part 1) and (Part 2)
In Microsoft Excel, you have the ability to use AutoFill to automatically generate future values as a simple linear trend or an exponential growth trend.
Using AutoFill
The AutoFill (or fill) handle appears as a rectangle on the bottom right corner of your selection. When you point to the AutoFill handle, the pointer changes to a black cross.
AutoFill a series as a linear trend
In order to AutoFill a series as a linear trend, you will need to enter values in at least two cells. The difference between the two cells is called the step value and may be positive or negative. For example, if the values were 1 in A1 and 2 in B1, the step value would be 1. If the values were $5,000 and $7,000, the step value would be $2,000.
When you generate a linear series, the step value is added to the first value and then added to each subsequent value so the values in the resulting series are differentiated by a fixed amount.
To AutoFill a linear series (also called a best fit trend):
- Select at least two cells that contain the starting values. You may select more than two cells for greater accuracy.
- Drag the AutoFill handle on the bottom right corner of the second or last selected cell in the direction you want to fill.
AutoFill a series as a growth trend
When you generate a series as a growth trend, Excel calculates an exponential growth trend using the values that are selected. Excel will calculate the average change between the initial values and uses that information to generate the series.
Example 1
If the two initial values are:
1, 2
The next values would be:
4, 8, 16, 32
For the first two cells, Excel divides 2 by 1 with the result of 2. The subsequent values in the series are therefore generated by multiplying the previous value by 2.
Example 2
If the two initial values are:
5, 20
The next values would be:
80, 320, 1280
For the first two cells, Excel divides 20 by 5 with the result of 4. The subsequent values in the series are therefore generated by multiplying the previous value by 4.
To AutoFill a series as a growth trend:
- Select at least two cells that contain the starting values. You may select more than two cells for greater accuracy.
- Press the right mouse button and drag the AutoFill handle on the bottom right corner of the second or last selected cell in the direction you want to fill.
- Release the mouse button. A smart tag appears.
- Click the Smart Tag and from the menu, choose Growth Trend.
Note: if you prefer to use the Ribbon, you can select the cells where you wish to generate a series (including at least two starting cells with values) and use the Fill drop-down menu which appears in the Editing Group on the Home tab.
Subscribe to get more articles like this one
Did you find this article helpful? If you would like to receive new articles, join our email list.
To request this page in an alternate format, contact us.
More resources
How to Use Flash Fill in Excel (4 Ways with Shortcuts)
How to Quickly Fill in Missing Values from the Cell Above in Excel
10 Great Excel Navigation Shortcuts
Related courses
Microsoft Excel: Intermediate / Advanced
Microsoft Excel: Data Analysis using Functions, Dashboards and What-If Analysis Tools
Microsoft Excel: Visual Basic for Applications (VBA) | Introduction
Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.
Copyright 2023 Avantix® Learning
You may also like
Excel XLOOKUP Function (Overview, Syntax + Formula Examples)
The XLOOKUP function is a replacement for Excel's traditional VLOOKUP function (as well as HLOOKUP and INDEX / MATCH functions). It has a new set of arguments and is available in Excel 2021 and 365. It allows you to look up a value from an array in a range or table and return one or more results. One of the primary benefits of XLOOKUP is that it can look up from columns to the left in a data set and return a range.
How to Use Go to Special in Excel to Find, Select, Replace and Format Data
You can use Go To Special in Excel to quickly find and select cells of a specific type within your worksheet. It only selects cells in the current worksheet, not the entire workbook. Go To Special also searches within the selected range if a range has been selected. You can find many useful things using the Go To Special dialog box including formulas, constants, blanks, visible cells and conditional formatting. After you select multiple cells with Go To Special, you can enter data in the selected cells by pressing Ctrl + Enter or by applying formatting.
How to Lock Cells in Excel (3 Ways)
It's easy to lock and protect cells in Microsoft Excel to prevent users from changing data or formulas. This involves a two-step process …
Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.
Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca
