Is it possible to autofill all cells in a column with a formula in new rows?
Hi,
I am using helper columns to overcome what I think is a major missing feature  the inability to use formulas for conditional formatting (for instance, if a date in one field is after a date in another field).
The solution proposed elsewhere in the forum is to have a helper column with a formula and then doing the conditional formatting on this helper column instead of the original column.
What I have found, though, is that I need to copy the function to the cells in the column for all rows; this is inconvenient, but not a major issue. What is the issue is that if someone adds more rows, the formula needs to be copied to the new rows  but the person doing this might not be aware of the formula at all! Surely there should be a way to provide a single columnwide formula which is automatically copied to all cells in the column, regardless of when the rows are created? Is there such a feature?
Best Answer

Hi Enzo,
If I understand correctly, you want to have a formula for an entire column. This is possible.
The first step would be to enter the formula you desire in one cell of the column you want it to be in, it doesn't matter which one. Then you right click on the cell with the formula, and the following menu appears:
At the very bottom you can see "Convert to Column Formula". If you click on that, the formula in the cell will be automatically applied to the entire column, even if rows are added at a later date.
I hope I understood your question correctly and I hope this helps!
Kind regards,
Anna
Answers

Hi Enzo,
If I understand correctly, you want to have a formula for an entire column. This is possible.
The first step would be to enter the formula you desire in one cell of the column you want it to be in, it doesn't matter which one. Then you right click on the cell with the formula, and the following menu appears:
At the very bottom you can see "Convert to Column Formula". If you click on that, the formula in the cell will be automatically applied to the entire column, even if rows are added at a later date.
I hope I understood your question correctly and I hope this helps!
Kind regards,
Anna

Ah, thank you! That's awesome  exactly what I need, and worked perfectly!
Help Article Resources
Categories
Check out the Formula Handbook template!