Friday, 28 May 2010

Excel extremely auto-fill

I've been using an Excel spreadsheet to track my working time at the office, and I have an interesting problem, which hardly anyone seems to have even mentioned online - even Microsoft themselves. I have the date and time of day, a couple of notes fields, and then some calculations in columns F to I, namely the Duration (time difference between entries), Week number (because I fill out a weekly timesheet), Year and a rounded duration that gives me times rounded to quarter-hours.

When I add the date and time to a new row, the week number and year happily auto-fill on their own, but the duration columns do not. Now, I want to be clear about this: the problem is not that I don't know how to use ordinary auto-fill, nor that the formula columns cannot be auto-filled. The frustration is that there seems to be some very-auto-fill option that doesn't even require ANY user input, and it works for the simple week and year columns, but not for anything else. The most advanced and most informative auto-fill articles online stop at custom series, and not one even mentions this fully automatic formula copying at all.

Mokalus of Borg

PS - One came close to suggesting it was related to auto-correction.
PPS - That also does not seem to be the case.

1 comment:

John said...

Two years after noticing this problem, I finally have an answer! In Excel 2010, highlight a cell in the data import, go to the Data tab, click the Properties button and at the bottom of the dialogue box there is a checkbox labelled "Fill down formulas in columns adjacent to data". Tick it. Formulas should now copy down automatically when imported data grows.