I usually create my query in SSMS or Visual Studio and then just paste the final query in this window. However, if you select the “Advanced” dropdown arrow, you can create your own custom Excel SQL query. If you do not know how to write SQL queries yet, this is one approach you can take. You can remove columns and filter tables before importing. This will allow you to browse available tables from that database to import. You can enter this information and then select “OK”. At this point it will pop-up a prompt to enter your server name and the target database you’re wanting to query (you can get this information from SSMS). To get started, select “Get Data” à “From Database” à “From SQL Server Database” as shown in the screen grab. However, it is important to note that this approach is only available in Excel 2013 and later and will not currently work on Mac OSX. This option is the most straight forward approach to creating an Excel SQL query. Now, on to why you’re all here… Excel SQL Query Using Get Data If you’re new to SQL, I highly recommend the SQL Essential Training courses on. Otherwise, I recommend downloading these tools before getting started. If you are familiar enough with SQL and have access to your own data, you can skip these steps and use your data.

If you have issues accomplishing this, let me know in the comments and I’ll elaborate on how this is done.

You must ensure your SQL Server User is running as the “Local Client” and then you can create a blank database, and restore that database from the backup file. Once you open SSMS, it should automatically detect your local server instance. The easiest way to install this is using SQL Server Management Studio (SSMS). I’m also using a sample database that you can download here.

I’ll be working with the free developer version in this article. If you don’t, you can download the trial version, developer version, or free express version here. To follow along with my below demos, you’ll need to have an instance of SQL server installed on your desktop. If you have access to the database where the data is housed, you can circumvent these steps and create your own custom Excel SQL query. You’re also at the mercy of how a disparate system exports data, and may need an additional step between exporting and getting the data into the format you need. However, sometimes getting the data we need into Excel can be cumbersome and take a lot of time when going through other systems. Howdee! Excel is a great tool for performing data analysis.

Excel for mac refresh