Share via

What does =A1:A5 return in excel.

Anonymous
2016-11-10T19:43:11+00:00

MS Excel 2013.

Even though it is a very simple expression, I really do not clearly understand what is really doing Excel when I type in a cell the expression:

=A1:A5,  which by definition represents the range of a cells group.

In this case Excel returns #VALUE! unless if I press Ctrl+Shift+Enter after typing the formula outside of the first five rows on the entire spreadsheet.

Suppose that the values stored in the above range (A1:A5) are 1,2,3,4,5, if I use the formula =A1:A5, for example in cell B2, the Excel returns the value of 2. If  I use the formula =A1:A5, for example in cell K3, the Excel returns the value of 3, etc.

However, If I type the formula =A1:A5 in B6 and if I press Ctrl+Shift+Enter, the Excel returns the value of 1.  If I type the formula =A1:A5 in K346 and if I press Ctrl+Shift+Enter, the Excel returns again the value of 1; from this point no matter what cell position I chose, Excel always returns the value of 1, except of course for all first 5 rows on the entire spreadsheet.

Why does Excel work in that way?   Could anyone help on this?

Thanks in advanced.

Marcos Moreno.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-11-10T20:19:13+00:00

    When you enter =A1:A5 into any cell in rows 1 to 5, you will get the value from column A, from the same row where you entered that formula - unless you array enter it, in which case you will get 1, which is the first element of the array, returned to the first cell into which the formula is entered. 

    If you select 5 cells instead of one cells - say B101:B105, and enter using Ctrl-Shift-Enter - the formula   (=A1:A5) will return 1,2,3,4,5.  You can enter that formula into between 1 and n cells - if you used 2 cells, you would get 1 and 2  - if you use more than 5 cells you will get #N/A in the cells 6 and on.

    The formula type  =A1:A5 is really meant for tables, to make sure that you get the value from the same row, and really should be entered as =$A$1:$A$5 so that it does not update to A2:A6, etc.

    5 people found this answer helpful.
    0 comments
  2. Anonymous
    2016-11-11T00:43:31+00:00

    Hi Bernie,

    Thank you.  I see now the reason how Excel works in these cases.

    I understand now.

    Best Regards,

    Marcos

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2016-11-11T00:40:25+00:00

    Hi James,

    Thank you.  I understand now.

    Best Regards,

    Marcos

    2 people found this answer helpful.
    0 comments
  4. Anonymous
    2016-11-10T20:18:02+00:00

    Re:  =A1:A5

    When entered as an array formula (Ctrl + Shift + Enter) it returns an array of 5 elements (the contents of the 5 cells).

    To display all five elements, the formula must be entered into five cells simultaneously.

    If entered into only one cell, the formula still returns the five elements.

    To prove this, select the array formula in the formula bar and tap the F9 key once.

    (Esc key to exit)

    '---

    Jim Cone

    Portland, Oregon USA

    https://goo.gl/IUQUN2 (Dropbox)

    (free & commercial excel add-ins & workbooks)

    1 person found this answer helpful.
    0 comments