From the Not Just Numbers blog:
Do you ever have a need to find the last row in a set of data – or the first gap?
I find that I use this tip quite regularly. It might be to help define a range to copy, or set a print area, or to identify where to enter the next row of data, or it might simply be that the last item in a list is highly relevant to the spreadsheet I’m developing.
Whatever the purpose, it is very handy to have a formula that will give me this information.
Say we want to know the first blank row in column A. We can use an array formula to return the row numbers of all the blank rows.
I will not go too much into array formulae here as that would be a significant blog post in itself, but there are three important things to know:
- An array is a set of arguments or results, arranged into rows and columns, operations can be applied to the whole array
- The result of an array formula will usually be an array itself and therefore will need another function to specify how it is to be shown as a single result (without this, the first result of the array will be displayed). Typical functions might be SUM or AVERAGE.
- To enter an array formula, you need to press Ctrl+Shift+Enter, rather than just Enter
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.