I've been working on a series of blogs about connecting your data, which may reside in different places. So far I've covered:
Today's topic is linking from Excel to SQL Server.
Why?
You probably own at least one software app, and there's a good chance that app stores its data in SQL Server. That's a great option for storing your data, since it's reliable, secure and scalable. But it lacks a user-friendly interface for reporting and analysis.
That's where Excel comes in. As you probably already know, Excel has outstanding tools for charting, graphic and analyzing your data. By creating a link to your SQL Server data, you'll be able to create a meaningful interface that helps users understand and evaluate key data points.
There are multiple methods of connecting Excel to SQL Server, in addition to many other data sources. I'm going to walk you through just one of them.
Creating the Link
I start by creating a new Excel document. I think go to the Data ribbon, and click the "Get Data" button. From the drop-down list, I choose "From Database", and then "From SQL Server Database":
Here's where you may need some help from your SQL Server administrator. You need to know the name of the SQL server, and the name of the database. Here's what mine looks like, but yours will be different:
The next screen is the database navigator, which shows me the tables and views in my database. I only have one (tblPurchaseOrders), so I've selected that one. A preview of the data is shown on the right, and I click the "Load" button to bring the data into Excel:
And there it is! I've now go the SQL data showing in Excel:
By default, this data will not refresh automatically. I can refresh it manually by clicking the "Refresh All" button on the "Data" ribbon:
You can set up the data to refresh automatically. To do so, click the "Queries & Connections" button on the Data ribbon. A panel will pop up on the right side of your screen, showing all connections in your workbook. You'll probably only have one, and it's name will probably be the same as your SQL table name:
Right-click on that connection, and choose "Properties" from the pop-up menu:
On the Properties window, you'll see several options under "Refresh control". You can check off the "Refresh every..." option to specify that the data will automatically be refreshed at regular intervals. This will come in handy if you're going to open this Excel file, and keep it open for a while. Maybe its something you need to watch all day, so you set it to refresh every 30 minutes.
Another nice option is the "Refresh data when opening the file". As the name implies, every time you open this Excel doc, the data will automatically get refreshed to match the current data that's in SQL server.
One Way
One thing that might confuse (or concern) you is that it appears that you can change the SQL data from here. And you can - sort of. We have established a one-way link between Excel and SQL. Any changes made to SQL will be reflected in Excel the next time you refresh, but the same is not true in reverse. If you make changes to the data on the Excel side, those changes will never be uploaded to the SQL server. They'll simply be overridden the next time you refresh the data.
Now What?
Now that you've got your link set up to SQL server, you can build graphs, charts and pivot tables against it. Consider adding those items on separate worksheets, though, because the size of the dataset will vary, and might overlap your graphics at some point.
My favorite technique is to hide the sheet with the data, and only show the sheets with my graphs, charts, pivot tables and reports. Because I have set the connection to automatically refresh whenever I open the file, I don't ever have to see the data sheet. Once I refresh my charts and pivots, they'll be updated to reflect the current SQL data.
Bringing It All Together
At this point, I've demonstrated techniques for bringing both Access and SQL data into Excel. In my next Excel blog, I'll show you how you can bring together data from both of those sources into a single report, chart or pivot. To be notified when that post goes live, subscribe using the form below.
Want a professionally developed Excel dashboard to present to your team? Set up a free one-on-one zoom with me to go over your project. I'll scope out your project and offer advice, and then present you with a customized proposal for the project. Just like Home Depot... You can do it, I can help!
Kim
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
#northportsolutions#learningneverstops#smallbusiness #excel #microsoftexcel#msexcel #excelformulas #dataanalysis
© North Port Solutions, LLC, 2023. All rights reserved.