One thing I use a lot in all my spreadsheets is Header Formulas. This is a trick I’ve learned from my manager Eugenia at Sheetgo to reduce the weight of the spreadsheet and increase its speed when you are working with larger datasets.
Often times I see spreadsheets where people have a formula for each row, this is fine when you have a small number of rows, but will quickly slow down your sheet when working with larger data sets. The solution is elegant, array formulas. These are formulas that automatically expand over an array, for example, K1:K99 or all of column K, K:K.
For those that want to play with the formula straight away, these are example header formulas:
=ArrayFormula(IF(ROW(K:K)=1,“Auto High or Low”,IF(A:A="“,”",IF(E:E>50,“High”,“Low”))))
=ArrayFormula(IF(ROW(M:M)=1,“Auto Concatenate”,IF(A:A="“,”“,C:C&” "&B:B)))
I’ve also built an example sheet here: https://docs.google.com/spreadsheets/d/1uVxKyJcfsFySII18cgJa8MPK2EZ9S6h_/copy
One thing to note, arrayformulas don’t work with the =AND() =OR() operators or formulas, as well as some formulas that group a whole array =SUM(A2:D5) that can be solved easily to change it to =A:A+D:D.