Double-click the fill-handle (thing you click on to drag) and Excel will fill-down as far as the next empty row.
If we want to change that formula, we should be able to change it once and not need to remember to redrag.
How would it know whether you want to change it in one place for a special case, or update many places?
You can complain about the UI (I certainly do. For example, try inserting a row in an array formula), but the feature is there: http://office.microsoft.com/en-us/excel-help/introducing-arr...
Agreed. You can do this by creating your own macros, but it's a pain. Considering how often I do this for personal spreadsheets, you'd think that this would have been simplified long ago.
And, this deficiency has been ported to both Numbers and Google spreadsheets.
If we want to enter 99 identical (or relatively identical) formulas into 99 cells, then we should be able to select all 99 cells, and enter the formula once without any dragging. If we want to change that formula, we should be able to change it once and not need to remember to redrag. We should be able to enter a formula into the column heading and know it'll apply whenever relevant to every cell in a column. That we can't means Excel (and Lotus and OpenOffice) is defective.
What QA is to blame for isn't letting unfinished spreadsheets out the door, but rather for letting users in a company use defective tools like Excel and Lotus and OpenOffice in the first place.
Many of us developers learnt the hard way that visual programming tools from the 1990's don't work, which is why scripting languages became popular. Users in corporations (and the IT staff who authorize what tools that can use) need learn the same lesson.