Naive Questions on using Excel with External Databases

[NigelTufnel]

I have many years of Excel/VBA experience, but little-to-no background using Excel with external databases. Please excuse this rather naive set of questions. If there is an internet resource or books that explain all of this in easy-to-understand terms, please feel free to refer me.

I have an external database on a server that I wish to do analysis with via Excel. I'm trying to understand the ways in which this can be done. Ideally, I would like to use Excel as a front end, i.e., have the calculations done either in Excel (or the database server), and have the results displayed in an Excel Workbook. However, I'd strongly prefer not to have the data itself exported/downloaded to Excel. I'd like to set up the record selection criteria (tables/fields) and calculations ahead of time (or use drop-downs, etc.) and then have the Excel workbook "update" the calculations as requested using the latest contents of the database.

How can Excel be set up to operate like this, or does it requires additional tools, e.g., VBA, PowerPivot, PowerBI, etc.?

I've seen lots of articles on using Excel to query databases with SQL, but in the articles I've seen, the data is always downloaded to the workbook so that it can be operated on later. Is this step required?

Thanks for any assistance you can provide.

 

[NigelTufnel]

Bump. Any help with this question is appreciated.

 

[RoryA]

It depends what you mean by "downloaded to Excel". Unless you can do the calculations in the query, the data has to be transferred to Excel in some form (though it does not need to be put in a worksheet).

 

[NigelTufnel]

Thanks for the response, Rory.

Originally, my thought was that putting the data in a worksheet would add significant overhead to the calculations I wish to do. Perhaps this is not true. Of course, if not, I could always delete the data after an update.

Is it possible for you to enlighten me as the which methods/tools actually put the data in a worksheet and which do not?

 

[RoryA]

Whichever ones you tell to. Power Query can either load to the data model, or to a worksheet. VBA can process all the data in arrays or recordsets. (PowerBI is separate to Excel so not really relevant) Even regular pivot tables can operate off external data without it ever being put in a worksheet.

 

[NigelTufnel]

OK...sounds like I need to get my hands dirty and try some of these out to get a better sense of how they work.

Thanks for the responses.