50

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
  • 2
    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.). – Máté Juhász Jun 25 '18 at 11:54
  • 3
    @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. – robinCTS 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. – Thomas Padron-McCarthy 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. – robinCTS Jun 25 '18 at 18:03
67

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:

Worksheet Screenshot

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.

Chart Screenshot

  • 12
    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?) – BruceWayne Jun 25 '18 at 19:55
  • 7
    @BruceWayne yes, it's a common approach in simple linear regression – 0xFEE1DEAD Jun 25 '18 at 20:03
  • 10
    @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. – robinCTS Jun 26 '18 at 1:36
  • 7
    Excel documentation confirms that dragging the fill handle will insert values using linear best-fit trend – phuclv Jun 26 '18 at 6:36
  • 1
    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 – Bindelstif Jun 26 '18 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.