Formula is automatically being added to every row

Kristina Todoric

Hello!

I have a formula =COUNT([Project Name]:[Project Name]) that I've only added to row 1 of my sheet but every time a new row gets added to the sheet, this formula is copied to that row. I have duplicate sheets where this doesn't happen so I've no idea how to stop this.

Any help will be really appreciated.

Many thanks,

Kristina

Jeff Reisman

@Kristina Todoric

There are a few reasons this can happen.

If the formula is set as a Column Formula, it will automatically be applied to every new row. Right click on the cell, and look at the bottom of the context menu. If these are your choices, then this is a column formula. If that's the case, click on "Convert to Cell Formula".

If your row with the formula is at the very top of the sheet and new rows are automatically added to the top of the sheet, the new rows will contain the formula automatically. Same thing for the very bottom of the sheet, and new rows added to the bottom. Inserting a blank row or two between your formula row and wherever the new rows are being added should prevent a cell formula from being applied to new rows automatically.

Kristina Todoric

Hi Jeff,

Thanks so much for the quick reply. I forgot to mention that I didn't have set the formula as a Column Formula so thank you for sharing a solution your second solution too. I think it did the trick! Would you know why I've had to do it on this sheet but not the others which are duplicates?

Kind regards,

Kristina

Jeff Reisman

Could be any number of reasons. Are you using a different input method, like a form? Or copying/moving rows from another sheet? Could just be the position of the rows.

Kristina Todoric

The input method is the same (copy row automations). Leaving a couple rows blank has fixed it. Thanks again for your help.