When typing gets hijacked by a previous entry
If you've ever typed a few similar characters and had Excel auto-fill a completely different value you entered earlier, it can get in the way more than it helps. Turning this setting off means whatever you type stays exactly as you typed it.
It's different from Formula AutoComplete and dropdown lists
This setting only affects the suggestion that pops up when you start typing plain text into a cell based on other entries in the same column. It has nothing to do with Formula AutoComplete, which suggests function names as you type a formula, and it doesn't affect dropdown lists created through data validation -- both of those keep working normally even after you turn this off.
Frequently Asked Questions
Does this also affect dropdown lists from data validation?
No -- a dropdown list built with data validation is a completely separate feature from cell-value AutoComplete, so turning this off doesn't affect it.
How do I turn it back on?
Go back to the same File > Options > Advanced menu and check the box again to restore AutoComplete.