Excel & SQL Server – Ready Set Connect!

At some point in your business you are going to grow out of your spreadsheets and need to start playing with some database data. Many people who are comfortable with Microsoft Excel get scared when considering making the leap to databases because it seems so ‘techie’.

Fact is, for most tasks the gap between understanding spreadsheets and using a database is not so great, and people who have a good handle on Excel are more than capable of working with data from SQL-Server.

Harnessing these two technologies together can be very useful, so it is something well worth at least looking at. 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:

1. The name of the Database Server (The machine that runs the database software)
2. The name of the database your data is stored in
3. 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 on 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.

P.S.

And, as usual, don’t forget to check out our PDF to Excel Converter. It can help you free up a lot of time. Time you can use to get a lot more done at work or … to do something nice for yourself. Go walk in the park, watch a movie, talk to a friend – you deserve that.