In any formula, where you have to select a dataset, you would questions:
What if any data is modified?
What if new data is added to the rows below my existing data?
Any modification in the data will not have a problem in the output, as it will rerun the formula everything data is modified anywhere.
But when new data being added in below rows, the outputs don’t change as the formula is still using the same dataset. This can be rectified.
For example, you are using a simple formula wherein you are taking a sum of all salaries of all employees.
The salary column is say the H column, and you have a list of 100 employees.
Your formula would look like this =SUM(H1:H101)
(H101 since H1 would be the header of the column).
Now tomorrow 3 new employees are added in the data, so you will need to change the formula to =SUM(H1:H104). So everytime new employees are added, your formula needs to be updated! Crazy! Maddening! Isn’t it?
Simple solution! 😉
Make your formula like =SUM(H:H)
When you say H:H, you are basically looking up the entire column for values and not fixed number of rows in that column. Thus, whenever you add any new employees, data of that employee is fetched in the formula automatically and our output changes 🙂
Useful in all kinds of formula.