How to Use SQL Server Data in Excel, Part 1
This is a first article in a series of articles that shows how Excel users can make good use of the power of SQL-Server.
Step 1: Getting Ready
Before attempting to import data you need to contact your DBA/System administrator and find out the following:
- The name of the Database Server (The machine that runs the database software).
- The name of the database your data is stored in.
- Whether your windows credentials are enough to access that server or, a username/password for your database
Step 2: Connecting to the Database
In the Data tab you will be able to access data from other sources, using (funnily enough) the "From Other Sources" button. Click the button and find the "From SQL Server" option.
A dialog will appear asking for the server name and if necessary the username/password you were supplied earlier. If you don't know if you need a username/password, try without first (Windows Authentication).
If the connection works ok then Excel is going to be able to talk to the database server and retrieve a list of databases that you have permission to access. Select the name of your database from the list of databases on the server.
Next you need to select the specific "table" that contains the information you need from the database. For the sake of this discussion you can think of a table as being like a spreadsheet within the database, rows and columns of information that you need to work with.
There are cases where you will not know which table contains the information you need. In this case, if you are not sure how to navigate the database, you should again ask your DBA.
If you don't have a database administrator, browse the table names and try to figure out which one contains the data. Once you've narrowed the list to a few tables, create a connection to each of the tables and checkout for yourself if it contains the data you need.
In a future article we will discuss what to do when you need to use data that is spread across several tables.
At this point we have told Excel where to find the data, now we need to use the information we supplied to create a Data Connection File. This is a file that you can use again and again in different spreadsheets and projects. This is so you don't have to go through the process of reconnecting to this source of information again. Again, we'll expand on this topic in another article.
Save your data connection file by pressing finish so we can move on to importing the data into your spreadsheet.
You will be asked to select how the data from the database table will be shown in your Excel workbook, and where you want it to go. Press OK when you are happy with your selections.
Step 3: Use the Data
Now you have the data inside your Excel file. And you can do whatever you like with it.
A common use of data imported this way is to use Excel for creating prettier presentation formatting, like creating charts and graphs, or as shown here, using conditional formatting to highlight the items that have low stock levels.
Or calculating values using a macros and formulas, for example to calculate the total value of each item you have in stock.
In the supplied screen shot, we have applied the formula to a new column:
You could even calculate the total value of the stock you have on hand...
Another cool feature of using a data connection is that you can refresh the table and Excel will retrieve fresh, bang up to date data from the database. To do this, select the refresh option from the 'Data' ribbon:
And you'll get the new data into the table.
Note that the conditional formatting and formulas were applied automatically to the new data, as if by magic!
In this article we have only just touched on the power of using Microsoft Excel combined with a database, but I am sure you can easily see how much potential there is, especially in an environment where you collect a lot of fast-changing data stored in complex databases.
Is this something you could find useful in your organization? Perhaps you already do this. Please share your thoughts and experiences with us on Facebook or Twitter.
About the Author
This article was written by Yoav Ezer, the CEO of Cogniview Systems, a company that produces PDF to XLS conversion software.
Prior to that, Yoav was the CEO of Nocturnus, a technology-centered software solution company.
Continue on to Part 2: Using Microsoft Query
