Look Who it Was Random video

Excel - Address of a Cell: Episode 1319

1,664 views · Published 7 January 2011 · 5:13 · Indexed 15 October 2026

Channel: MrExcel.com · 2011 · Education

Watch on YouTube

Microsoft Excel Tutorial: Create a formula that defines the address of a cell containing a total. Using VLOOKUP, MATCH the ADDRESS Function and a few other tricks, getting that address becomes easy!

Welcome back to another exciting episode of Dueling Excel, where I, Bill Jelen from MrExcel, take on the challenge of solving Excel problems. In this episode, we tackle a question that may seem simple at first glance, but upon closer inspection, makes us wonder why someone would want to do this. The question is, how do we create a formula that will give us the address of a specific cell in Excel?

To demonstrate this problem, I have a range of cells with a total at the bottom. The challenge is to create a formula that will give us the address of this cell, which we can then use in other functions like INDIRECT. This becomes especially useful if we insert more rows and the total cell moves. So, let's dive in and see how we can solve this problem.

The first approach I take is using a trick from my book, Excel Gurus Gone Wild. We use either a VLOOKUP or MATCH function with a number that is larger than any possible number in the column. By using "TRUE" instead of "FALSE" at the end, we can find the last numeric value in the column, which in this case is the total cell. We then combine this with the "A" and MATCH functions to get the address of the cell. However, I also explore using the MAX function to find the total cell, but ultimately decide to stick with the original formula to avoid any potential errors.

But, as always, my approach is not the only one. My opponent, Mike, takes a completely different direction by using the ADDRESS function. He cleverly uses the ROW and COLUMN functions to get the address of the total cell, and even shows us how to make it absolute or relative. He also introduces the CELL function, which has some amazing capabilities. Mike's approach is a bit simpler and more straightforward, but it does require the use of absolute references.

In the end, both approaches achieve the same result, and it's up to you to decide which one works best for your specific situation. We hope you enjoyed this Dueling Excel episode and learned something new. Don't forget to subscribe to our channel for more exciting Excel tips and tricks. Thanks for watching and see you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Address of Total Cell
(00:10) The desired formula 
(00:21) Using a trick from Excel Gurus Gone Wild 
(01:00) Alternative approach 
(01:53) Mike's solution using the Address function 
(03:15) Another approach using the CELL function 
(04:01) Mike's solution with SUBSTITUTE function 
(04:27) Discussion and conclusion 
(05:03) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Absolute, mixed, and relative references in Excel
Duelling Excel podcast with MrExcel and Excel is Fun
Excel formula to create address of a cell with total
Inserting rows and updating cell references
Referring to a specific cell with row and column numbers
Testing formulas with right-click and insert
Tricks from Excel Gurus Gone Wild
Updating cell references when inserting rows
Using SUBSTITUTE function in Excel
Using the ADDRESS function in Excel
Using the CELL function in Excel
Using VLOOKUP or MATCH function in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152999/

More from this channel