INDEX and ADDRESS aren't bad functions, INDEX will not fetch a value outside of the range you specify so it doesn't mess up with the calculation model (you are

INDEX and ADDRESS aren't bad functions, INDEX will not fetch a value outside of the range you specify so it doesn't mess up with the calculation model (you are explicit about your dependency). INDIRECT, yes, that's the nasty one. For advanced excel users, this website is worth a read: https://www.decisionmodels.com/calcsecrets.htm https://www.decisionmodels.com/calcsecrets.htm It's also worth going through the function list in the documentation once, just so you know what is possible. 5) learn how to use array formula. They can be used for two use cases, either a function returns an array, though this is now deprecated with the SPILL feature in more recent version of excel. But more useful: you can do array based calculations (more powerful than SUMIF). For instance give me the number of time the value changed in a timeseries: {=SUM(1*(A1:A100<>A2:A101))}. 6) of the rare useful new features of excel, there is PowerQuery, which allows you to load data from a csv file or database. Very useful when you need to refresh that data. You can parametrize power query so that for instance the filepath of the csv file is defined in an excel range. It avoids this repeated pattern of manually opening a csv file and copy pasting the data when you need to refresh the report.