Applying an auto-populating formula to column for file size management (increasing row #s)

[astrodude]

Hello!

I am an excel newbie, so apologies if this has been solved/been asked before. Is there a way to apply a formula to a column, without increasing the number of rows in the excel sheet, while auto-populating if the user scrolls downwards or creating new rows? The issue I am running into is when I apply a formula to a column set, the sheet will increase the rows to the maximum allowed (100000+). This causes issues as the excel file will have over 10 sheets that are copies of each other(they represent different locations). This causes performance issues due to the sheer file size. I would need to keep all sheets as one document as I will eventually try too integrate Power BI, which would be in the next phase of development. Is this possible?

In the document I have attached, the formulas in question are in columns H, J, and K. There is a simple macro for auto-filtering tied to a helper column for a clean up functionality.

Thank you!