Setting for auto-populating formulas 'invisibly'?

[Rhodes19]

This is my first time posting a question, because I've always been able to find pre-existing answers in the past - thank you! My issue is hard to describe but I'm sure the solution is maddeningly simple:

I have a spreadsheet with data in Column A, and a formula that references it in Column B. When I enter a new row of data in A, the formula in B automatically calculates and populates that row, too - without copying or dragging the formula down from the above cells. If you click on a cell in column B in a row below the last line of data, it appears to be empty.

My question is: how am I doing this? Is there a setting on the workbook that makes this happen? I want to replicate this action elsewhere, but I began this spreadsheet years ago and have no idea what I did at the time.

Please let me know if more description of this issue is needed.

I'm using Excel 2011 on a Mac.

 

[eduzs]

The only way a blank cell becomes with value without manual entering formula or values is with macro/vba.

 

[Rhodes19]

Thanks for the answer. Is that something I would have been able to set up as a novice? Even now I'm only an intermediate Excel user at best, and have no familiarity with VBA.

 

[Fazza]

Be sure you have a header - so that Excel recognises a table.

Say some text in the first row with a formatted colour different
For the data entries underneath have plain format

If you can manually control it on/off I don't know. This exists in all recent Excel versions AFAIK

Anyway, try formatting a header and see if that starts the auto-filling of formulas in new data rows

cheers

 

[Rhodes19]

Hm. The data in the worksheet mentioned where this is happening is not formatted as a table. That said, I did try formatting data in a different file as a table with a header to see if I could recreate this action, but no luck.

I also tried replicating the effect in different columns within the same worksheet, and sometimes it worked, and sometimes it didn't. I couldn't seem to figure out what the pattern or rule was that governed it.

I did notice that if you skip three lines or more in the same column where the effect is happening, then it will stop happening until you add at least three new rows of data and copy the formula for the first three - after that it will auto-calculate and auto-populate again.

Does that give a bit more clarity? Thanks for the help.