# What exactly is Excel's Auto Fill algorithm?

What is the algorithm used by Auto Fill in Excel?

For example, when I enter the numbers 3,4,5,7,8 into a column and then use Auto Fill, I get the following result:

`````` 3
4
5
7
8
9.3
10.6
11.9
13.2
14.5
15.8
17.1
18.4
``````
• Excel probably checks the average increase and use that for autofill. (In your series you increased by 1 three times and once by 2, average is 1.25, round it to one digit (as I'm not affiliated with MS I've no clue why), that's 1.3, so increase number 1.3 in each row.). Jun 25 '18 at 11:54
• @MátéJuhász That's a good guess, and what I initially thought. However it turns out to be incorrect. See my answer for more details. Jun 25 '18 at 12:28
• An observation is that the difference between the last two given values is 1.3, and then the auto-filled values all increase by 1.3. The simplest (but, I have now learned, wrong) interpretation for this set of data would be that it just repeats the last difference. Jun 25 '18 at 17:35
• @ThomasPadron-McCarthy Actually, the last two given values are 7 and 8, so the difference is 1. The 1.3 is the calculated Linear Trend slope, which, as you correctly ascertained, is then progressively added to obtain the auto-filled values. Jun 25 '18 at 18:03

Excel uses its Linear Trend approach when you auto-fill. This employs the least-squares method algorithm.

This is the same algorithm used by the `TREND()` function as seen demonstrated below: Enter the following formula in `C6` and ctrl-enter/copy-paste/fill-down into the rest of the column:

``````=TREND(\$C\$1:\$C\$5,\$B\$1:\$B\$5,B6)
``````

Below is a chart showing the trend line that the new values fall on.

The least-squares method creates a "best fit" line for the original data points. The new data points are essentially extracted from this line. • Not that I doubt your answer, but just curious how you know they use the least-squares method. (Or, more broadly, did you do some investigating "under the hood"? Or is it common for such applications to use this method?) Jun 25 '18 at 19:55
• @BruceWayne yes, it's a common approach in simple linear regression Jun 25 '18 at 20:03
• @BruceWayne No investigation under the hood was required ;-) It is mentioned in a few places on the web. Plus, Excel's own documentation states that the `TREND()` function uses "the method of least squares", which, as you can see, I confirmed produces the same result as auto-filling. So, provided that you can believe Micro\$oft's own documentation (and we all now how accurate that is) I would say that that makes it fairly conclusive. Plus what 0xFEE1DEAD said. Jun 26 '18 at 1:36
• Excel documentation confirms that dragging the fill handle will insert values using linear best-fit trend Jun 26 '18 at 6:36
• Obligatory Joel Spolsky video about excel. Skip to ~8 minutes of an explanation of how Excel references cells, which is useful for defining your own series. youtube.com/watch?v=0nbkaYsR94c Jun 26 '18 at 21:40