Automatically populate cells in Excel?
August 7, 2011 8:20 AM   Subscribe

How do I get Excel to automatically populate the cell in a certain column when I have manually populated the cells in the other columns of the same row?

When using Excel, I often set it up so that (say) cells A1, B1 and C1 are raw values, and then I go over to E1 and make it into a calculation based on them. Then I put raw values into A2, B2, and C2, and either copy E1 to E2 or Fill Down E1 to E2.

But sometimes, Excel somehow puts itself into this state where it will automatically copy/fill down column E when I populate columns A, B and C. So, for example, I enter a value in A3, hit tab to get to B3, enter a value, hit tab to get to C3, hit return to get to A4, and Excel has magically populated E3. Note that I did not fill down to E3, or copy to E3. There was nothing in E3 -- not even a calculation that didn't have all of its components available -- and I never touched E3, but Excel automatically populated it similarly to how I had told it to populate E2.

I like it when Excel is in this state, but I have no idea how it gets into it. And then sometimes it puts itself out of this state, and I have no idea why it does that, either. So I just kind of have to luck into it, and hope I don't unluck out of it.

Note: This feature does not seem to be what people usually mean when referring to any of "autofill", "autoformat", "autocomplete" or autocorrect" within Excel.
posted by Flunkie to Computers & Internet (8 answers total) 1 user marked this as a favorite
 
when click on a cell, you'll see the little anchor bit in the lower right corner, hover over that until you see your cursor change to a plus and then double click and the cells below will auto populate with the formula/value in the highlighted cell.
posted by birdherder at 8:25 AM on August 7, 2011


Response by poster: That doesn't seem to do it for me, birdherder.
posted by Flunkie at 9:01 AM on August 7, 2011


I believe, in 2003, this feature is controlled by Tools>Options>Edit>Extend data range formats and formulas. But I can't always get it to work just by toggling that box. Some answers might be here
posted by Gorgik at 10:04 AM on August 7, 2011


This state is caused by the range being formatted as a table.

I don't have Excel right now but I think this is accessed in 2007 via Insert > Table.
posted by zixyer at 12:17 PM on August 7, 2011


Response by poster: zixyer, maybe I'm just not understanding what you're saying, but I can get it to happen on a brand new spreadsheet without inserting a table.

For example, try the following in a brand new blank spreadsheet:

* Enter "1" in A1
* Tab to B1
* Enter "2" in B1
* Tab to C1
* Tab to D1
* Enter "=a1+b1" in D1
* Hit return to go to A2
* Enter "3" in A2
* Tab to B2
* Enter "4" in B2
* Tab to C2
* Tab to D2
* Enter "=a2+b2" in D2
* Hit return to go to A3
* (... similarly for rows 3 and 4 ...)
* Hit return to go to A5
* Enter "9" in A5
* Tab to B5
* Enter "10" in B5
* Tab to C5

At that point, for me at least, D5 automatically gets populated (as soon as I tab out of B5).

I think that Gorgik is onto the answer - if I turn that checkbox off, then doing what I described above doesn't cause D5 to automatically populate. But I'm not sure how the behavior turns itself off (even with the checkbox on), nor how to get it back on again once it's off.

Maybe I just have to start manually typing the formula in for a few rows... Gorgik's "some answers might be here" link indicates that one of the conditions where it doesn't happen is if you paste data. I have, in the past, pasted the formula rather than typed it in.
posted by Flunkie at 1:03 PM on August 7, 2011


Maybe this is too obvious, but in your example you talked about cols ABC and E -- in my experience I have seen this behavior only with consecutive cols, eg ABCD.
posted by Perplexity at 1:50 PM on August 7, 2011


Response by poster: It definitely works (at least sometimes) while skipping columns, Perplexity.
posted by Flunkie at 2:59 PM on August 7, 2011


Here is a tech forum note that explains it in a little more detail. It may be a feature that was phased out, but it is a little hard to tell. I have XL2010 and the option is not in my menu.

excelforum.com

And here is a MS tech note from 2003

support.microsoft.com
posted by lampshade at 4:24 PM on August 7, 2011


« Older Looking for a game online   |   Can I cancel my AT&T service and use my iPhone... Newer »
This thread is closed to new comments.