7

What is the equivalent of google sheets =IMPORTRANGE function in MS Excel?

=QUERY(IMPORTRANGE("1yH4Y1bz4zchsC0TFuWkJsAaL_XXXruNHf56BOiNJIM","Another sheet!A:M"),"select Col1,Col2,Col4,Col8",1)

E.g., I'd like to automatically import some columns from another sheet in the same workbook. How do I do it?

4
  • 1
    I don't use google sheets, but if they're in the same workbook, why not just reference them with =?
    – wysiwyg
    Mar 10 '17 at 20:30
  • In google sheets I write IMPORTRANGE once (and in one - top left - cell) and it imports all the specified data. I do not reference cells individually. It also means that if I add rows in referenced sheet, they are automatically imported as well.
    – Aivars
    Mar 10 '17 at 20:41
  • 1
    No there is nothing like this in Excel. In Excel a formula in one cell cannot effect the value of another cell. So either one formula for each cell or vba in a worksheet changed event that copies the desired columns to the new sheet on each change. Mar 10 '17 at 20:50
  • @Aivars You can use ranges in the target formula. And if you insert rows or cols in the range, the target formula will adapt to that change. Sep 27 '17 at 12:18
2

On the Data menu, there will be an option to get from other sources
enter image description here
From this use Microsoft Query
enter image description here
You can then select Excel File as your source, and pick the file that has the data you wish to import.
You can also add any criteria you need, and sorting on the subsequent screens.
This will leave you with a table that you can refresh when needed.

2
  • That means no auto-refresh? :|
    – misha
    May 28 '19 at 10:48
  • Any chance autorefresh is now available with this now that's it's been two years?
    – Peter Berg
    Aug 27 '19 at 1:39
2

Data ribbon > Get Data > From File > From Workbook

1

Browse to the file, select it, and click Import

2

Select the data you want to import. Excel really wants this data to be in a table format. Wherever possible, I'd recommend importing the entire sheet.

3

If you want to get particular, you can click Transform Data to open up the power query editor. From there, you can pick particular columns or a certain number of rows. It's not straight SQL but you'll figure it out if you're familiar with the QUERY() function in Google Sheets.

4

Excel turns your data into a table and opens the Queries and Connections sidebar. Right-click on the query in that sidebar and then click Properties....

5

Check the Refresh every box and change the number of minutes as desired.

6

0

You can now use: Data/Get Data/From File/From Workbook.. Select what you want. You can delete rows, transform data, etc...

enter image description here

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