How to be more efficient with these four Google Sheets formulas

Related post: How to use Google Sheets to count advertising inventory easily

Google Sheets is a great, free alternative to Microsoft Excel. It's an ideal tool for small business owners. But if copying and pasting are your most frequently used maneuver, you're wasting your time. Here are four ways you can semi-automate copying and pasting within and between worksheets. 

efficient like nature!

Import a worksheet into another workbook

*workbook: a Google Sheets document that may have more than one worksheets within.
*worksheet: a sheet/tab within a workbook.

We have more than ten freelances we work with. Each has own workbook with assignments and deadlines. And I'd rather open one workbook to check how everyone's doing instead of opening individual workbooks.

To import record from one worksheet into another workbook, first, find out the unique "key" of your Google Sheets document with the records. In the URL bar of your document, you should see something like this: 

https://docs.google.com/spreadsheets/d/1cSvegi4Hiw5TymigMjsnEY373ROJq6OB5uEx8gmdzXs/edit#gid=1324183973

The bolded part is the document's key. 

Now, open a new workbook and paste the following formula in A1 (or anywhere you choose).

=IMPORTRANGE("1cSvegi4Hiw5TymigMjsnEY373ROJq6OB5uEx8gmdzXs","Form responses!A:Z")

1cSvegi4Hiw5TymigMjsnEY373ROJq6OB5uEx8gmdzXs - this is the key

Form responses - this is the name of the sheet you want to pull records from

A:Z - this is the range of cells.  You can choose whatever the range you want. E:E will import everything under column E and A1:A20 will import everything in the cells A1 through A20. 

Here is one caution. The association is one-way, meaning that changes made in the individual worksheet will be reflected in the workbook with imported data. But you can't edit records in the latter to change the records in the original worksheet. 

Import a range from a worksheet into another workbook based on conditions

Building upon the previous example, sometimes all you want to import is records with certain values or conditions. 

=Query(ImportRange("174S3Jc2ke2zGkizXw6XUGRi8Zzpco2kbRtcnwdm9Q_Y","Form responses!A1:Z200"),"select* where Col5 ='1/2 page'", 1)

174S3Jc2ke2zGkizXw6XUGRi8Zzpco2kbRtcnwdm9Q_Y - this is the key

Form responses - this is the name of the sheet you want to pull records from

A1:Z200 - this is the range of cells that you want.  

Col5 - the 5th column from the left (=Column E). You're telling Google Sheets that you want all the records with value "1/2 page" under column E. 

Select* - means select all. If you want, say, Col 1 and Col2 only imported, then you can rewrite it as:
select Col1, Col2 

Copy a worksheet within the same workbook

If you want to copy the contents of one worksheet to another within the same workbook, use the formula below:

= ArrayFormula('Form responses'!E:F)

Form responses - this is the name of the sheet you want to pull records from

E:F - range of cells you want. 

Copy a worksheet within the same workbook if cell is not empty

If you want to copy the contents of one worksheet to another within the same workbook provided cells are not empty, use the formula below: 

=FILTER('Form responses'!E:E , LEN(TRIM('Form responses'!E:E))>0)


For further reading, check these websites: