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

You may also like

How to Replace Zeros (0) with Blanks in Excel

How to Replace Zeros (0) with Blanks in Excel

There are several strategies to replace zero values (0) with blanks in Excel. If you want to replace zero values in cells with blanks, you can use the Replace command or write a formula to return blanks. However, if you simply want to display blanks instead of zeros, you have two formatting options – create a custom number format or a conditional format.

What is Power Query in Excel?

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)

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.

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