# Excel: Last Entry

I was challenged to work out a little problem someone was having with Excel today.  The expectation was that I could write some VBA to resolve it, but once I found out what the task was I quickly managed to put together a purely Excel function based solution.

The task was to pick the last entry from a vertical list of cells.  The list is sequential so there are no blank lines which makes the choice of algorithm to find the last entry quite open.   The algorithm I chose was based on a list length and vector approach.

So to start with I needed to know how many entries were in the list.  The `COUNTA` function will provide this.  This in effect tells us how many rows we have to step down to get to the last entry.  The `OFFSET` function allows us to carry out these steps based on the first entry in the list.  Because we’re starting at the first entry then we don’t need to move if there is only one entry, so we need to subtract one from the count to give us the number of “steps” to take from the first cell.

The screen shot illustrates how this is built up for a list in column A starting at cell A1. The function we would use is simply

``````=OFFSET(A1,COUNTA(A:A)-1,0,1,1)
``````

However this does give us a `#REF!` if the list has no entries - which looks like an error if you give someone a blank list (essentially because it is).  So if you need to account for this, just include an `IF`.

``````=IF(COUNT(A:A)=0,"",OFFSET(A1,COUNTA(A:A)-1,0,1,1))
``````
Author: Stephen Millard
Tags: | excel |

• ## Excel - Failed Tabbed Data Paste

02 May 2019

In my day job I work with a lot of raw data, and I make judicious use of Microsoft Excel to help me in the builds and transforms I need to accomplish. As many people are no doubt aware, Excel can be quite useful with the use of tab separated text as it treats each tab as a way to separate data out into cells and each new line as a new row in a spreadsheet. At least that’s what I thought. Recently however, I fell foul of finding tab separated data on my Windows clipboard not pasting into Excel in this way. But I did track down why this occurred.

• ## Excel: Count Lines in a Cell

30 Nov 2017

Recently I was using an Excel spreadsheet to prepare some data and I had a constraint to work to in the number of lines of data the target system could display. Unfortunately rather than rows of data in Excel this was to match against rows of data in a single Excel cell. So the question is, how can you count the number of rows in an Excel cell?

• ## Batch Generating Worksheet Hyperlinks in Excel

29 Apr 2017

In my work I often find myself building spreadsheets to carry out calculations, manage sophisticated processes and capture information. These spreadsheets can often extend to covering many worksheets and as a result it can be awkwardly slow to navigate to particular worksheets just by paging back and forth with the navigation buttons.

In this post I’m going to illustrate a couple of useful techniques to help you navigate around.

• ## Extracting Excel File Path Information Using Excel Formulas

29 Jun 2014

In several of the template Excel spreadsheets I use in my work there are references to clients and version numbers that are included. This is for reference purposes, particularly if the spreadsheet might be printed or exported as a soft copy (e.g. PDF). Whilst it is possible to maintain this information manually I decided that this should be something that could be improved by virtue of the file name containing such information (as these are the very basis of file management on projects). I’d referenced the file path and elements of it in the past and I thought that this would be something that should be relatively straight forward to achieve; and here’s how I got on ….