One thing I use a lot in all my spreadsheets is Header Formulas. This is a trick I learned from my manager a while ago to reduce the weight of the spreadsheet and increase its speed when you are working with larger datasets.
Often 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 who 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(L:L)=1,“Auto Multiplication”,IF(A:A="“,”",E:E*A:A)))
=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.
