CELL function in Excel

Excel comes with multiple Lookup and Reference functions that let you find matching values. The Lookup and Reference functions help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets

How to get address of first cell in range in Excel

To get the address of the first cell in a named range, you can use the ADDRESS function together with the ROW and COLUMN functions. See example below; Note: The ADDRESS function builds an address based on a row and column number. Formula =ADDRESS(ROW(range),COLUMN(range)) Explanation In the example shown, the formula in G5 is: =ADDRESS(ROW(data),COLUMN(data))… read more »

Get workbook name and path without sheet in Excel

If you want to get the current workbook’s full name and path without a sheet name, you can use a formula that employs several text functions to strip off the sheet name. The final result will be a text string that looks like this: path[workbook.xlsm] Formula =SUBSTITUTE( LEFT(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))-1),”[“,””) Explanation How the formula works The CELL… read more »

How to get sheet name only in Excel

If you want to get the sheet name only (i.e. the sheet name without the file name or path) you can do so with rather long formula that uses the MID function along with the FIND function. The final result will look something like this: Sheet1 Formula =MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,255) Explanation How the formula works The cell… read more »

How to retrieve workbook name only in Excel

If you want to get the workbook name only (i.e. the file name without path or sheet name) you can do so with a rather long formula that uses the MID function along with the FIND function. Formula =MID(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))+1,FIND(“]”, CELL(“filename”,A1))-FIND(“[“,CELL(“filename”,A1))-1) Explanation How the formula works The cell function is used to get the full file… read more »

How to get full workbook name and path in Excel

If you want to get the current workbook’s full path, name, and sheet with a formula, you can use the CELL function and a reference to any cell in the workbook. CELL will return the name in this format: path[workbook.xlsx]sheetname Note that you must save the worksheet in order to get the a result. Formula… read more »

Highlight unprotected cells in Excel

This tutorial shows how to Highlight unprotected cells in Excel using the example below; Formula =CELL(“PROTECT”,A1)=0 Explanation To highlight unprotected cells (cells that are unlocked) with conditional formatting, you can use a formula based on the CELL function. In the example shown, the custom formula used to set up conditional formatting is: =CELL(“PROTECT”,A1)=0 Where A1 represents the… read more »

Get address of lookup result in Excel

This tutorial shows how to Get address of lookup result in Excel using the example below; Formula =CELL(“address”,INDEX(range,row,col)) Explanation To get the address of a lookup result derived with INDEX, you can use the CELL function. In the example shown, the formula in cell G8 is: =CELL(“address”,INDEX(B5:D11,MATCH(G6,B5:B11,0),2)) Which returns an address of $C$8, the address… read more »

Sidebar