4

Is there an array function to copy an array? I use the workaround =TRANSPOSE(TRANSPOSE(A1:A30)) currently.

An example of when this is useful: you have a worksheet of raw data, and another worksheet with formulas applied to those values.

Doing it this way means that if a row is inserted in the first spreadsheet, it will be inserted in the second.

  • An ARRAYFORMULA would cover that. – Jacob Jan Tuinstra Aug 22 '13 at 5:54
  • =ARRAYFORMULA(SHeet1!A:A-Sheet1!B:B); complete columns are taken in to account, thus if something get inserted on sheet 1, it will be included in sheet 2. – Jacob Jan Tuinstra Aug 22 '13 at 5:58
  • Oh - perfect. :) – Steve Bennett Aug 22 '13 at 6:02
  • For the stats.... – Jacob Jan Tuinstra Apr 14 '14 at 17:46
2

Short answer

Instead of =TRANSPOSE(TRANSPOSE(A1:A30)) consider to use ={A1:A30} as it's shorter.

Long answer

An alternative to ARRAYFORMULA is the use of embedded arrays also named

  • inline arrays
  • literal arrays
  • array parameters
  • "your own arrays"

Example of embedded array of constants

={"A","B";1,2}

Literal Array

={A1:B2;D1:E2;G1:H2}

Example of embedded array of non-adjacent ranges.

Embedded array

It's worth to say that for spreadsheets with regional settings that use:

  • . as the decimal separator should use , as the column delimiter
  • , as the decimal separator should use \ as the column delimiter

In both cases use ; as the row delimiter.

Reference

Using arrays in Google Sheets - Docs editors Help

  • 1
    Oh, that's great to know. So in the simpler cases it would just be: ={A1:A30} – Steve Bennett Jul 22 '15 at 23:46
  • That is right. I just added a that as a "short answer" :) – Rubén Jul 22 '15 at 23:51
3

An ARRAYFORMULA would cover that. You can reference a range of columns like so:

=ARRAYFORMULA(Sheet1!A:H)

Complete columns are taken in to account, thus if something gets inserted on sheet 1, it will be included in sheet 2.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.