First of all, I am a Newbie myself.

The answer is .... it depends.

I have set up a table in SQL Server, usually a staging table rather than a direct link to the final table. This allows the data types, the quality checks and other data scrubbing to take place first.

Make the Table sharable. Then in Excel use the external data link. Probably the most common way is ODBC.

Set the start location for data in Excel. The data will come into the Excel workbook.

This is linked data. Yes, it can be added and modified.

A better method:

Use MS Access. Then use DSNLess code to create a link from an Access Table to the SQL Table. My preferred driver is the free SQL Server Native Client 11

If you want to really make sure the Access table field types match SQL Server (including autocounter, index, numeric, binary, ...) then design the table in MS Access. From there use the free download Microsoft SQL Server Migration Assistant for Access.

Between these two, you will have a very high convertibility factor between Access and SQL Server. The Access Table is basically the Linked Clone of SQL server. Professional Access Developers do this all the time.

Now that MS Access has a linked table with all the security connection embedded in code, put the Access DB on a shared network.

It can even be compiled to prevent mid-level users from seeing any menu items.

NOW - use the Excel to link to this Access Table that is Linked to SQL Server.

This removes any UN/PW /Link information from Excel to SQL Server.

Just to take it a little further... I use Excel Object Model Programming to evaluate the tables and create custom Excel workbooks on demand. From Access the user can request something, then the custom Excel appears in their home directory. This way, the custom functionality, formatting, results and the rest can be presented to that individual viewer. Granted, this is more specialized. By using Stored Procedures and SQL Server Views, my preference is to create extremely customized "reports" with customized array formulas that match the data pull and utilize business Class Modules to analyze the data across large data sets into a concise data-mined report with all of the custom settings.

This is just a sample of the options.

When you find the SQL Server Native Client download, there is also a new Excel analysis add-in for SQL Server. My hope is to learn what it is about and write some articles about it in 2014. It sounds very exciting for those of us who use Citrix.

http://www.access-programmers.co.uk/forums

You can find a lot of this at the site above. I have over 1,000 post and the total is over 1,000,000

I visit this site often because writing TSQL is only about 10% of my time. The gracious people here help me often.