# 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, 2018 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, 2018 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, 2018 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, 2018 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, 2018 at 19:55
• @BruceWayne yes, it's a common approach in simple linear regression Jun 25, 2018 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, 2018 at 1:36
• Excel documentation confirms that dragging the fill handle will insert values using linear best-fit trend Jun 26, 2018 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, 2018 at 21:40