INDIRECT<\/strong> function.<\/p>\n\n\n\nMethod 1: ADDRESS with ROW, COLUMN, and MIN and Go To\u2026<\/h2>\n\n\n\n We will use the following data range named Country_GDP<\/strong> (A2:C6) to show how this method can be used to select the first cell in the range:<\/p>\n\n\n\n <\/figure>\n\n\n\nThe first cell in this data range can be selected by using the following steps:<\/p>\n\n\n\n
\nSelect Cell F2<\/strong> and type in the formula:<\/li>\n<\/ol>\n\n\n\n=ADDRESS(MIN(ROW(Country_GDP)),MIN(COLUMN(Country_GDP))) <\/code><\/pre>\n\n\n\n <\/figure>\n\n\n\n\nPress the Enter<\/strong> key and the reference for the first cell in the named range will be displayed:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nOpen the Go To<\/strong> dialog box by going to Home >> Editing >> Find & Select >> Go To\u2026 <\/strong>on the Excel Ribbon:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\nAlternatively, we can press the keyboard shortcut Ctrl + G<\/strong> to launch the Go To<\/strong> dialog box.<\/p>\n\n\n\n\nIn the Go To<\/strong> dialog box that pops up, type in the cell reference that was displayed in step 2 above and press OK:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\nThe first cell in the named range is selected:<\/p>\n\n\n\n <\/figure>\n\n\n\nThe Go To\u2026<\/strong> feature enables jumping to the first cell in the data range.<\/p>\n\n\n\nExplanation of the Formula<\/h3>\n\n\n\n=ADDRESS(MIN(ROW(Country_GDP)),MIN(COLUMN(Country_GDP)))<\/code><\/pre>\n\n\n\nThis formula uses the combination of ADDRESS, MIN, ROW, and COLUMN functions<\/strong>.<\/p>\n\n\n\nThe ADDRESS function<\/strong> creates a cell reference as text, given specified row and column numbers.<\/p>\n\n\n\nThe MIN function<\/strong> returns the smallest number in a set of values.<\/p>\n\n\n\nThe ROW function<\/strong> returns the row number of a reference.<\/p>\n\n\n\nThe COLUMN function<\/strong> returns the column number of a reference.<\/p>\n\n\n\nIn this instance, we want to get the first row and the first column used by the named data range Country_GDP<\/strong> (A2:C6).<\/p>\n\n\n\nTo get the first row used we use the ROW function <\/strong>and the MIN function<\/strong> as follows:<\/p>\n\n\n\nMIN(ROW(Country_GDP))<\/code><\/pre>\n\n\n\nBecause the data range Country_GDP has more than one row the ROW function returns an array of row numbers:<\/p>\n\n\n\n
{2;3;4;5;6}<\/strong><\/p>\n\n\n\nThis array is passed to the MIN function which returns 2 as the smallest value:<\/p>\n\n\n\n
MIN({2;3;4;5;6})<\/code><\/pre>\n\n\n\nTo get the first column we use the COLUMN function and the MIN function as follows:<\/p>\n\n\n\n
MIN(COLUMN(Country_GDP))<\/code><\/pre>\n\n\n\nBecause the data range Country_GDP has more than one column the COLUMN function returns an array of column numbers:<\/p>\n\n\n\n
{1;2;3}<\/strong><\/p>\n\n\n\nThis array is passed to the MIN function which returns 1 as the smallest value:<\/p>\n\n\n\n
MIN({1;2;3})<\/code><\/pre>\n\n\n\nThe values returned by the ROW and COLUMN functions are then passed to the ADDRESS function which creates a reference to the cell at row 2 and column 1:<\/p>\n\n\n\n
=ADDRESS(2,1)<\/code><\/pre>\n\n\n\nIt returns an absolute reference of $A$2<\/strong>. If we want to get a relative reference, we need to input value 4 as the third argument as follows:<\/p>\n\n\n\n=ADDRESS(MIN(ROW(Country_GDP)),MIN(COLUMN(Country_GDP)),4)<\/code><\/pre>\n\n\n\nThis will return a relative cell reference of A2<\/strong>.<\/p>\n\n\n\nMethod 2: Use CELL together with the INDEX and Go To…<\/h2>\n\n\n\n We will use the following data range named Country<\/strong> to show how this method can be used to select the first cell in the range:<\/p>\n\n\n\n <\/figure>\n\n\n\nThe first cell in the named range can be selected using the following steps:<\/p>\n\n\n\n
\nSelect Cell D2<\/strong> and type in the formula:<\/li>\n<\/ol>\n\n\n\n=CELL(\"address\",INDEX(Country,1,1))<\/code><\/pre>\n\n\n\n <\/figure>\n\n\n\n\nPress the Enter key to display the reference of the first cell in the range:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nPress the keyboard shortcut Ctrl + G<\/strong> to launch the Go To<\/strong> dialog box. Alternatively, you can go to Home >> Editing >> Find & Select >> Go To \u2026 <\/strong>on the Excel Ribbon.<\/li>\n\n\n\nIn the Go To<\/strong> dialog box that pops up type in the cell reference that was displayed in step 2 above and press OK:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\nThe first cell in the range is selected:<\/p>\n\n\n\n <\/figure>\n\n\n\nThe Go To<\/strong> feature enables jumping to the first cell in the data range.<\/p>\n\n\n\nExplanation of the Formula<\/h3>\n\n\n\n=CELL(\"address\",INDEX(Country,1,1))<\/code><\/pre>\n\n\n\nThis formula uses the combination of the CELL <\/strong>and INDEX <\/strong>functions.<\/p>\n\n\n\nThe CELL <\/strong>function returns information about the formatting, location, or contents of the first cell, according to the sheet’s reading order, in a reference.<\/p>\n\n\n\nThe INDEX <\/strong>function returns a value or reference of the cell at the intersection of a particular row and column, in a given range.<\/p>\n\n\n\nIn this instance, we want to get the first row and the first column used by the named data range Country<\/strong> (A2:B6).<\/p>\n\n\n\nWe give the INDEX function the arguments 1 for row number, 1 for column number, and Country for array:<\/p>\n\n\n\n
INDEX(Country,1,1)<\/code><\/pre>\n\n\n\nIt returns a reference to A2<\/strong>.<\/p>\n\n\n\nWe then use the CELL <\/strong>function together with ”address’<\/strong>‘, to show the address of the first cell in the range.<\/p>\n\n\n\nMethod 3: Use the Excel VBA<\/h2>\n\n\n\n We will use the following data range named Country_GDP2<\/strong> to show how this method can be used to select the first cell in a range:<\/p>\n\n\n\n <\/figure>\n\n\n\nTo apply the Excel VBA to select the first cell in a named range, we use the steps below:<\/p>\n\n\n\n
\nPress Alt + F11<\/strong> to open the Visual Basic Editor (VBE). <\/strong>Alternatively, go to Developer >> Code >> Visual Basic<\/strong> on the Excel Ribbon:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nIn the Project Window<\/strong> of the Visual Basic Editor right-click the workbook and insert a new module:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nIn the new module type in the following code:<\/li>\n<\/ol>\n\n\n\nSub SelectFirstCell()\n Dim myRange As Range\n Set myRange = Range(\"Country_GDP2\")\n myRange(1).Select\nEnd Sub\n<\/code><\/pre>\n\n\n\n\nSave the module and the workbook as a macro-enabled workbook<\/strong>.<\/li>\n\n\n\nPress Alt + F11<\/strong> to switch back to the active worksheet. Alternatively, click the View Microsoft Excel<\/strong> button:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nPress Alt + F8<\/strong> to launch the Macro dialog box<\/strong>. Alternatively, go to Developer >> Code >> Macros<\/strong> on the Excel Ribbon:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nIn the Macro dialog box select the SelectFirstCell<\/strong> Macro and click Run<\/strong>:<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\nThe first cell in the data range Country_GDP2 i<\/strong>s selected:<\/p>\n\n\n\n <\/figure>\n\n\n\nExplanation of the code<\/h3>\n\n\n\nSub SelectFirstCell()\n Dim myRange As Range\n Set myRange = Range(\"Country_GDP2\")\n myRange(1).Select\nEnd Sub\n<\/code><\/pre>\n\n\n\nUsing the Dim statement, the myRange<\/strong> variable is declared as a range object.<\/p>\n\n\n\nThe Set statement, is used to assign the range object Country_GDP2<\/strong> to the myRange<\/strong> variable.<\/p>\n\n\n\nThe select method of the Range object is used to select the first cell in the named range.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n In this tutorial, we have explored 3 methods that we can use to select the first cell in a data range in Excel.<\/p>\n\n\n\n
We can use the ADDRESS function <\/strong>together with the ROW, COLUMN, MIN functions,<\/strong> and the Go To\u2026 feature<\/strong>. We can also use the CELL function <\/strong>together with the INDEX function<\/strong> and Go To feature<\/strong>. Lastly, we can use the Excel VBA.<\/p>\n\n\n\nYou can use the method that you are most comfortable with and best fits your work situation.<\/p>\n","protected":false},"excerpt":{"rendered":"
In this tutorial, we will look at the following 3 methods that we can use to select the first cell address in a…<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\n
Select the First Cell in the Range in Excel<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n