Is it possible to autofill all cells in a column with a formula in new rows?

enzo_vaire

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 column-wide formula which is automatically copied to all cells in the column, regardless of when the rows are created? Is there such a feature?

Anna PRIE

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

Anna PRIE

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

enzo_vaire

Ah, thank you! That's awesome - exactly what I need, and worked perfectly!