Display Trends using AutoFill

by Avantix Learning Team | Updated January 7, 2020

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.

Related 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):

  1. Select at least two cells that contain the starting values. You may select more than two cells for greater accuracy.
  2. Drag the AutoFill handle on the bottom right corner of the second or last selected cell in the direction you want to fill.

Autofill sample linear series.

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:

  1. Select at least two cells that contain the starting values. You may select more than two cells for greater accuracy.
  2. 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.
  3. Release the mouse button. A smart tag appears.
  4. Click the Smart Tag and from the menu, choose Growth Trend.

Autofill sample growth series.

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.

Fill Series Dialog

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.

Recommended Microsoft Excel 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

VIEW MORE COURSES >

Copyright 2020 Avantix® Learning Inc.

You may also like

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 | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Summary
Simple Strategies to Show Trends in Excel (Part 3: Using AutoFill)
Article Name
Simple Strategies to Show Trends in Excel (Part 3: Using AutoFill)
Description
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.
Author
Publisher Name
Avantix Learning Inc.

Pin It on Pinterest

Share This