Hello, Excellers. Welcome back to another #Excel #FormulaFriday blog post in my Excel 2021 series. Today I will answer a question that was left on one of my YouTube Videos. The question left was the same multiple times. How do I find the last value in an Excel column?. My video actually walks you through how to find the last value in an Excel row using the Index and Count Function. I have put a link below that video.
So, here is how to find the last value in an Excel column.
As well as answering a viewers question, I have used this formula on a regular basis. Especially if I have to return the last value in a column of data. If you add in new values every month to an Excel spreadsheet but always want to return the most recent or latest value like for example the most recent sales then this can help. No more scrolling to the bottom of the page. Get Excel to return the value automatically for you.
I have my monthly sales in my Excel sheet below. We have sales until March 2021. I always want to return the value in Column B to be the most recent months sales. So as my April, May, June etc sales are added the most recent month is returned. To do this the INDEX and COUNT functions are used.
The Index Function.
The solution starts with the INDEX Function. For a quick reminder of the syntax of INDEX which returns a value or the reference to a value from within a table or range.
INDEX(array, row_num, [column_num])
- array – A range of cells, or an array constant.
- row_num – The row position in the reference or array.
- col_num – The column position in the reference or array. This is optional due to the [ ] brackets
The COUNT Function.
The second function used in this solution is the COUNT function. Again, for a quick reminder of the syntax of COUNT. It counts the number of cells that contain a number, as well as the number of arguments that contain numbers. It will also count numbers in any given array.
=COUNT(value1, value2….)
Value1 The first item or cell reference or range for which we wish to count numbers.
Value2…We can add up to 255 additional items, cell references, or ranges within which we wish to count numbers. This is optional due to the [ ].
Ok, let’s get building the solution to find the last value in an Excel column. Starting with the INDEX function. If I start with
=INDEX(C4:C15,3) this in itself with return the value of the cell in position 3. We manually inserted the row number. Number 3. This correctly returns the the value of 26655.
Next, by using the COUNT function to replace the row number, the function can be automated. See below how the COUNT function returns the correct row number of the last value in the column of data.
How cool is that? Every time a new entry of sale is named the latest sales value will automatically update as it counts the number of cells that contain a number. Will we test it and add some sales ahead of time into April 2021?. Let’s test it. Yes, it works.
So, that is how easy it is to return the last value in a column of data.