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

  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 example in Excel for linear series trend.

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 series in Excel showing 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.

Autofill series dialog box in Excel.

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

VIEW MORE COURSES >

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 2022 Avantix® Learning

You may also like

How to Password Protect Your Excel Worksheets and Workbooks

How to Password Protect Your Excel Worksheets and Workbooks

You can password protect worksheets and workbooks in Excel. If you want to prevent other users from opening or modifying an Excel workbook, you can encrypt the file with a password. You can also protect workbook structure where you can prevent users from deleting, renaming, moving or unhiding worksheets. Password protection can be added to your Excel file in several ways.

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

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

Pin It on Pinterest

Share This