Loading

Got a version of Excel that uses the
menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)?
**This site is for you!** If you
use a later version of Excel, visit
our *ExcelTips* site focusing on the ribbon interface.

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.

Learn more about Allen...

ExcelTips FAQ

ExcelTips Resources

Ask an Excel Question

Make a Comment

Free Business Forms

Free Calendars

** Please Note:** This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later),

Patty asked about a common scenario, in which column B contains quite a bit of data, and information can be added to the cells in the column at any time. In a formula in cell C4, Patty wants to see the value at the bottom of those cells in column B that contain values. Thus, if cells B1:B27 contain data, then in cell C4 Patty wants to see the value that is in cell B27. If three more pieces of data are added to column B, then the value in C4 should contain the value in B30.

The solution to this problem depends on whether you can count on the data in column B containing blank cells or not. If the data is contiguous—it doesn't contain any blank cells—then you can use the following formula in C4:

=INDIRECT("B"&COUNTA(B:B))

This constructs an address based on the last cell in the column, and then uses the INDIRECT function to return the value at that address.

If it is possible for there to be blanks in column B, then the following formula will work:

=INDIRECT("B"&MAX(ROW(1:65535)*(B1:B65535<>"")))

Again, the INDIRECT function is used to fetch the actual value, but the address used by INDIRECT is put together differently.

A different approach is to use the VLOOKUP function to return the value. If column B consists of numeric values, then the following formula in C4 will work just fine:

=VLOOKUP(9.99999999999999E+307,B:B,1)

If column B contains text, then the numeric lookup won't work, but the following will:

=VLOOKUP(REPT("z",50),B:B,1)

*ExcelTips* is your source for cost-effective Microsoft Excel training. This tip (2103) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Referencing the Last Cell in a Column.

*Related Tips:*

**Comprehensive VBA Guide** Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out *Mastering VBA for Office 2010* today!

No, it didn't have the word "Available" in it or anywhere in the spreadsheet for that matter. I did enter it using Ctrl-Shift-Enter.

@Steve,

The formula using the MAX(ROW(1:65535)*(B1:B65535<>"") to find the row number of the last cell is an "array" formula and must be entered into the cell using Crtl+Shift+Enter (rather than just pressing Enter). Also if you edit the formula you must re-enter the formula in the cell using Crtl+Shift+Enter otherwise Excel will treat it as a standard formula and give an incorrect result or an error message.

The reason for this is the element of the formula B1:B65535 defines a range of cells with the first cell (B1) being deemed as the activecell for that range, the value of which will be returned in a standard formula (which is why the word "Available" is being returned in your case). In an array formula all values in the defined range are returned.

The formula using COUNTA(B:B) to find the row number of the last cell is just a standard formula, and can be entered in the usual way.

If you do not use the Crtl+Shift+Enter with the first formula then you will just get the value in cell B1 (or D1 if you've changed the references to "D" from "B") which I guess has the word "Available" in it.

The formula using the MAX(ROW(1:65535)*(B1:B65535<>"") to find the row number of the last cell is an "array" formula and must be entered into the cell using Crtl+Shift+Enter (rather than just pressing Enter). Also if you edit the formula you must re-enter the formula in the cell using Crtl+Shift+Enter otherwise Excel will treat it as a standard formula and give an incorrect result or an error message.

The reason for this is the element of the formula B1:B65535 defines a range of cells with the first cell (B1) being deemed as the activecell for that range, the value of which will be returned in a standard formula (which is why the word "Available" is being returned in your case). In an array formula all values in the defined range are returned.

The formula using COUNTA(B:B) to find the row number of the last cell is just a standard formula, and can be entered in the usual way.

If you do not use the Crtl+Shift+Enter with the first formula then you will just get the value in cell B1 (or D1 if you've changed the references to "D" from "B") which I guess has the word "Available" in it.

Can you please provide insight on my first question, posted 29 Dec 2015, 07:13?

@Steve,

As I said just drop the INDIRECT function.

The formula then becomes ="B" & MAX(ROW(1:65535)*(B1:B65535<>"")) or if you want an absolute address use:

="$B$" & MAX(ROW(1:65535)*(B1:B65535<>""))

NB remember this is an array formula and therefore needs the Crtl+Shift+Enter combination to enter it.

As I said just drop the INDIRECT function.

The formula then becomes ="B" & MAX(ROW(1:65535)*(B1:B65535<>"")) or if you want an absolute address use:

="$B$" & MAX(ROW(1:65535)*(B1:B65535<>""))

NB remember this is an array formula and therefore needs the Crtl+Shift+Enter combination to enter it.

Thanks, Barry. I tried all kinds of formulas to return the address, for use in another formula, all without success. Each attempt worked fine, but only when the formula was in a cell by itself. When I tried to use it inside another formula, well, nothing.

But so much for that. Can you provide any insight on my first question, posted 29 Dec 2015, 07:13?

But so much for that. Can you provide any insight on my first question, posted 29 Dec 2015, 07:13?

@Steve

Just drop the INDIRECT function.

NOTE: the formulas using the MAX function are "array" formulas and should be entered using the 'Ctril+Shift+Enter" key combination for it to work.

Just drop the INDIRECT function.

NOTE: the formulas using the MAX function are "array" formulas and should be entered using the 'Ctril+Shift+Enter" key combination for it to work.

What if i want to return the address, not the value, of the last cell?

I entered =INDIRECT("B"&MAX(ROW(1:65535)*(B1:B65535<>"")))

then changed the three "B" to "D"

The last cell was D62 and everything above had entries, but the formula returned the word "Available" - I don't understand why. Using =INDIRECT("B"&COUNTA(B:B)) correctly returned the value 10.

then changed the three "B" to "D"

The last cell was D62 and everything above had entries, but the formula returned the word "Available" - I don't understand why. Using =INDIRECT("B"&COUNTA(B:B)) correctly returned the value 10.

@Kirk,

Check out the linked picture if it is for any help for you.

http://screenpresso.com/=mK4Nc

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Check out the linked picture if it is for any help for you.

http://screenpresso.com/=mK4Nc

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Need a formula to return the value of a cell that begins with specific text "Report Parameters".

I paste raw data to a sheet and "Column A" always has one row near the bottom that starts with "Report Parameters". I want the text of that cell to show in another palce in my template. The problem is that depending the report, that text gets pasted to different rows, but always in Column A.

I paste raw data to a sheet and "Column A" always has one row near the bottom that starts with "Report Parameters". I want the text of that cell to show in another palce in my template. The problem is that depending the report, that text gets pasted to different rows, but always in Column A.

@faizul,

Everything should work fine if you will follow the exact procedure.

Take a look at the example in the uploaded picture: http://screenpresso.com/=QUdW

---------------------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Everything should work fine if you will follow the exact procedure.

Take a look at the example in the uploaded picture: http://screenpresso.com/=QUdW

---------------------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

hello there.

Thank you for your brilliant tips.

For advancing your formula on vlookup, could you suggest on referencing to second last and third last row and so forth?

appreciate your kind help.

Thank you for your brilliant tips.

For advancing your formula on vlookup, could you suggest on referencing to second last and third last row and so forth?

appreciate your kind help.

I was looking for a way to see the last cell in a column of a Table (Listobject), so it would always show the latest version of the workbook in the input sheet. Since the top cell wasn't row 1 and there was data above the column, COUNT(A:A) couldn't work. I came up with =OFFSET(tbVersions[[#Headers],[Version]],COUNTA(tbVersions[[#All],[Version]])-1,0)

Just wanted to share, may be helpful to others.

Just wanted to share, may be helpful to others.

i am struggling from few days how to reference the last cell value in a dynamic column.

thank you Allen Wyatt

now i am really happy

thank you Allen Wyatt

now i am really happy

@ChauKTV,

My suggestion applies to an ALPHANUMERIC string which means a "TEXTUAL" value.

Whwt are you looking for ?

* The last cell's address or the Last cell's value ?

What are the value types in your column "A" ?

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-201)

ISRAEL

My suggestion applies to an ALPHANUMERIC string which means a "TEXTUAL" value.

Whwt are you looking for ?

* The last cell's address or the Last cell's value ?

What are the value types in your column "A" ?

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-201)

ISRAEL

i try:

=MATCH("*",$A:$A,-1) and

=MAX(MATCH(9^15,A:A),MATCH(CHAR(255),A:A,1))

Excel show wrong, please make 1 example for me please, Michael (Micky) Avidan.

Tks.

=MATCH("*",$A:$A,-1) and

=MAX(MATCH(9^15,A:A),MATCH(CHAR(255),A:A,1))

Excel show wrong, please make 1 example for me please, Michael (Micky) Avidan.

Tks.

Thanks this really helped me. I used the VLOOKUP method. Any idea how I would find the second last cell as opposed to finding the last cell?

@anand what you want is called an absolute reference... generally you use the cell designation with a "$" so you wanted B5, you would write your formula

B7=A7/$B$5 when you copy it, the $b$5 reference will stay the same.

B7=A7/$B$5 when you copy it, the $b$5 reference will stay the same.

@Anand,

In cell B7 type:

=A7*B$5

and copy down along column "B".

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

In cell B7 type:

=A7*B$5

and copy down along column "B".

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

I HAVE A QUERRY

SAY I HAVE A NO IN B5

AND NUMERIC VALUES IN A7,A8,A9,.....,A101 AND CONTINUE..

IF I WANT THE VALUES IN B7,B8,B9,......

AS

B7=A7/B5

B8=A8/B5

B9=A9/B5 AND SO ON.

WHAT SHOULD I DO.

PLEASE HELP.

SAY I HAVE A NO IN B5

AND NUMERIC VALUES IN A7,A8,A9,.....,A101 AND CONTINUE..

IF I WANT THE VALUES IN B7,B8,B9,......

AS

B7=A7/B5

B8=A8/B5

B9=A9/B5 AND SO ON.

WHAT SHOULD I DO.

PLEASE HELP.

@Lars,

Your request doesn't come close to Blaine's request.

As far as I recall - you need to have the target file OPEN in order to write to it.

It is usually done with VBA Programming and the target workbook doesn't have to be visible.

The vba code opens it > writes into > closes.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

Your request doesn't come close to Blaine's request.

As far as I recall - you need to have the target file OPEN in order to write to it.

It is usually done with VBA Programming and the target workbook doesn't have to be visible.

The vba code opens it > writes into > closes.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

I have a very similar, if not the same, question as Blaine.

How do I write a value to next empty cell in a column in a workbook that is not opened ?

Sincerely appreciate a swift answer.

How do I write a value to next empty cell in a column in a workbook that is not opened ?

Sincerely appreciate a swift answer.

@Abe,

The principal is shown in the linked picture:

http://postimg.org/image/rh66txmrj/

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

The principal is shown in the linked picture:

http://postimg.org/image/rh66txmrj/

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

Hi, im trying to get the avergae of the last three cells of a data set. they get updated on a monthly basis so i don't want to keep moving the formula.

for example:

Actual 20

Actual 30

Actual 25

Actual 25

Estimate 25

Estimate 25

I want the formula to look at this data find the actuals and then get the average for the last three actuals.

Please help if possible.

for example:

Actual 20

Actual 30

Actual 25

Actual 25

Estimate 25

Estimate 25

I want the formula to look at this data find the actuals and then get the average for the last three actuals.

Please help if possible.

I have a ledger sheet like bellow:

Date Dr. Cr. Balance

1/1/14 5000 --- 5000

2/1/14 ---- 500 4500

3/1/14 ---- 400 4000

4/1/14 1000 ---- 5000

5/1/14 ---- 100 4900

6/1/14 ---- 200 4700

7/1/14 ---- 700 4000

I need to know how long this current balance is carrying. It clear it from 3/1/14 to 7/1/14. How i calculate by one formula in excel.

Date Dr. Cr. Balance

1/1/14 5000 --- 5000

2/1/14 ---- 500 4500

3/1/14 ---- 400 4000

4/1/14 1000 ---- 5000

5/1/14 ---- 100 4900

6/1/14 ---- 200 4700

7/1/14 ---- 700 4000

I need to know how long this current balance is carrying. It clear it from 3/1/14 to 7/1/14. How i calculate by one formula in excel.

@Phil,

Assuming you are entring transaction information (date type/reference, amount) in columns A, B, and C, and that your running balance is in column D then the formula would be:

=INDIRECT("D"&COUNTA(C:C))

You can change the "D" to any column you like and it will return the corresponding value.

NB the caveats re blanks cells is still applicable as noted in the Tip.

Assuming you are entring transaction information (date type/reference, amount) in columns A, B, and C, and that your running balance is in column D then the formula would be:

=INDIRECT("D"&COUNTA(C:C))

You can change the "D" to any column you like and it will return the corresponding value.

NB the caveats re blanks cells is still applicable as noted in the Tip.

This formula worked in finding the returning the value of the last row with number, but what should I do if I want to return the first row with data considering that some rows do not contain numbers.

=VLOOKUP(REPT("z",50),B:B,1)

=VLOOKUP(REPT("z",50),B:B,1)

I have a check book balance sheet sort of thing made for a client.

He wants each month in a different tab.. so I need to carry over the last balance calculated… to the top of the next page automatically. So I want the last value output in the balance column.

Imagine a check book.. per row you have + or - and the calculated current balance.

Each month will obviously have a different # of entries, so I want to reference the last balance value in the series.

Here is the kicker.. and why I can't seem to get any of these suggestions to work.. I think.

The column that holds the values I want to reference, is filled with a formula that calculates that balance.

So I am not referencing a number… I am referencing a column of cells that contain formulas that either spits out a number, or a "" to stay hidden and not spit out those IRRITATING… "#VALUE!"s and such.

Thus.. each cell is a (=IF) that calculates the - & + on that row entry, if there is one.

Example.. in M10

=IF(B10="","",M9-(H10+I10+J10)+K10)

Logically I see it as.. Check if (B10) is blank (""), TRUE display nothing (""), FALSE.. then take the previous value (M9) and subtract possible costs (H10+I10+J10) and add any possible deposits (K10).

This formula repeats per line down the page waiting to be used.. or not.

I need a formula that will look at this column of formulas that either give "" or a # and get the last # in that series.

I am looking to display it on the current page at the bottom first… so getting it to the next page is easy and not my concern.

My concern is that all the suggestions I have found spit out "#VALUE!" or gives an error.

I have made a lot of complicated spreadsheets, but haven't really gotten into combining formulas or adding extra symbols to them to bend excel to my will. So I keep failing here. PLEASE HELP!

He wants each month in a different tab.. so I need to carry over the last balance calculated… to the top of the next page automatically. So I want the last value output in the balance column.

Imagine a check book.. per row you have + or - and the calculated current balance.

Each month will obviously have a different # of entries, so I want to reference the last balance value in the series.

Here is the kicker.. and why I can't seem to get any of these suggestions to work.. I think.

The column that holds the values I want to reference, is filled with a formula that calculates that balance.

So I am not referencing a number… I am referencing a column of cells that contain formulas that either spits out a number, or a "" to stay hidden and not spit out those IRRITATING… "#VALUE!"s and such.

Thus.. each cell is a (=IF) that calculates the - & + on that row entry, if there is one.

Example.. in M10

=IF(B10="","",M9-(H10+I10+J10)+K10)

Logically I see it as.. Check if (B10) is blank (""), TRUE display nothing (""), FALSE.. then take the previous value (M9) and subtract possible costs (H10+I10+J10) and add any possible deposits (K10).

This formula repeats per line down the page waiting to be used.. or not.

I need a formula that will look at this column of formulas that either give "" or a # and get the last # in that series.

I am looking to display it on the current page at the bottom first… so getting it to the next page is easy and not my concern.

My concern is that all the suggestions I have found spit out "#VALUE!" or gives an error.

I have made a lot of complicated spreadsheets, but haven't really gotten into combining formulas or adding extra symbols to them to bend excel to my will. So I keep failing here. PLEASE HELP!

To find the last row that holds an AlphaNumeric value - you can use:

=MATCH("*",$A:$A,-1)

In order to return the last cells row in a Mixed column INCLUDING EMPTY(!) cells - use:

=MAX(MATCH(9^15,A:A),MATCH(CHAR(255),A:A,1))

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

=MATCH("*",$A:$A,-1)

In order to return the last cells row in a Mixed column INCLUDING EMPTY(!) cells - use:

=MAX(MATCH(9^15,A:A),MATCH(CHAR(255),A:A,1))

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

I struggled with this one as well, but came to a better solution using array formula's.

Array formula's allow you to evaluate a formula on each cell in a range, so if you use the array formula:

=MAX(IF(A5:A500="",0,Row(A5:A500)))

then it will tell you the last used row, even if there are blanks in the range A5:A500. Keep in mind that you enter array formula's by pressing Ctrl-Shift-Enter. Excel will present the formula in curly { } braces for you, but you do not enter them yourselves.

You could even use the range A:A, but I do not know what impact that'll have on performance.

Array formula's allow you to evaluate a formula on each cell in a range, so if you use the array formula:

=MAX(IF(A5:A500="",0,Row(A5:A500)))

then it will tell you the last used row, even if there are blanks in the range A5:A500. Keep in mind that you enter array formula's by pressing Ctrl-Shift-Enter. Excel will present the formula in curly { } braces for you, but you do not enter them yourselves.

You could even use the range A:A, but I do not know what impact that'll have on performance.

Hi Jorge,

I have been looking at this for another issue i had.

Place this code in A55

=INDIRECT("a"&COUNTA(A1:A54)-0)

This will look at all the values between A1 and A54 and give you the last used value.

if you want to look at all the values in column A then you will need to move the piece of code to another location to avoid a circular reference error.

the code to look at the whole of column A is

=INDIRECT("a"&COUNTA(A:A)-0)

this will work in any cell which is not in column A.

NB there should be no missing cells of data ie nothing in row 4 but something in row 5. this will stop it working.

Hope this helps.

Andrew

I have been looking at this for another issue i had.

Place this code in A55

=INDIRECT("a"&COUNTA(A1:A54)-0)

This will look at all the values between A1 and A54 and give you the last used value.

if you want to look at all the values in column A then you will need to move the piece of code to another location to avoid a circular reference error.

the code to look at the whole of column A is

=INDIRECT("a"&COUNTA(A:A)-0)

this will work in any cell which is not in column A.

NB there should be no missing cells of data ie nothing in row 4 but something in row 5. this will stop it working.

Hope this helps.

Andrew

Need to get my last cell with data on to another cell.

A

1 $213.00

2 $256.33

3 $100.00

I need to get A3 to A55 cell.

when A4 get updated then A55 will show A4.

When A5 get updated then A55 will show A5.

Hope you can help me here.

A

1 $213.00

2 $256.33

3 $100.00

I need to get A3 to A55 cell.

when A4 get updated then A55 will show A4.

When A5 get updated then A55 will show A5.

Hope you can help me here.

This works - I am able to use the last cell in a particular column in another cell - HOWEVER... what I want to [also] so is add the last cell from rows in other worksheets in the same workbook. How do I do that? What am I missing? Thanks!