Find last cell with a value in excel
A recent query involved how to identify which cell in a row contains the final value, or after that cell all the numbers are zero. Finding the last cell with a value in Excel is typically required with financial models where, for example, you want to identify when the mine runs out of ore to include on key number on your dashboard.
There are lots of complex ways but this is a fairly simple way.
Below we have some revenue in row 2 and we want to create a formula to find where the revenue stops permanently. In row 5 we have used an IF formula to see if the revenue is equal to zero and then pulled through the month number (more of this later).
The problem with this method is that the zero in F2 would give us a false answer. So we need to check where the revenue stops forever.
A way to do this is to create a reverse cumulative sum. So in row 8 we have a simple sum formula but we have made the last cell in the range an absolute reference and the first cell relative. What this means is that as we copy across the sum will only add from the column we are in onwards. For example in H8 it adds up H2 to N2 only. The benefit of this is that where we previously had a false positive, it now shows that there is still revenue to come (cell F8).
Now in row 9 we can build the same IF function but looking at row 8. Notice now that it only starts showing the months from the correct month.
You may have noticed that in the IF function we used a “-” as the false instead of a zero. This is because in cell C11 we used a MIN function to find the Minimum month number in row 9 and if we had used 0’s it would have resulted in a 0. The “-” is seen as text and ignored by the MIN function.
Now that you have the month at which the revenue stops you can use it in other formula to cause other actions in the financial model.