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
What is Power Query in Excel?
Power Query in Excel is a powerful data transformation tool that allows you to import data from many different sources and then extract, clean, and transform the data. You will then be able to load the data into Excel or Power BI and perform further data analysis. With Power Query (also known as Get & Transform), you can set up a query once and then refresh it when new data is added. Power Query can import and clean millions of rows of data.
How to Freeze Rows in Excel (One or Multiple Rows)
You can freeze one or more rows in an Excel worksheet using the Freeze Panes command. If you freeze rows containing headings, the headings will appear when you scroll down. You can freeze columns as well so when you scroll to the right columns will be frozen.
How to Show or Hide Gridlines in Excel
You can remove or hide gridlines in Excel worksheets to simplify worksheet design. By default, gridlines are displayed but do not print. Gridlines are applied to entire worksheets or workbooks, not to specific cells. If you hide gridlines on one worksheet, it doesn't affect other sheets in the same workbook.
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
