Connecting Excel Spreadsheets for Dynamic Data Updates in Dashboards (Full Transcript)

Speaker 1: In this video demonstration, we're going to take a data source, which is an Excel spreadsheet, and connect it to another Excel spreadsheet through a connection. Now the reason we would want to do this is so that you, the student, understands that Excel spreadsheets can be connected to databases, data warehouses, other external data sources. That would be bringing in data, let's say, from your factory, data from satellite connection, or whatever the case. So we've set up a folder called SuperStore Dashboard, and in that folder there are two Excel spreadsheets. One named Dashboard.xlsx, which I have open, and the other one is the SuperStore Excel spreadsheet that contains all the data. In Dashboard.xlsx, we have two worksheets. The Dashboard, which is going to be the worksheet that contains the actual charts and graphics of the interactive dashboard, and the Staging Worksheet. The Staging Worksheet is where we bring the data into so that we can create the pivot tables that will then create the interactive graphics in the dashboard. But right now we want to connect these two Excel workbooks, and how do we do that? Well, we go up under the Data tab, Get External Data, Existing Connections, Browse for More. Now you notice I have a lot of connections here because I've used this for many other projects, but in your case you probably may not have any connections, or you may have one or two. We go down to Browse for More. We then go and we find our data source. And again, as I said, our data source is in a folder called SuperStore Dashboard. And there it is. SuperStore Sales.xlsx is the workbook that contains all of our data. So we select that. We click Open. And when we click Open, each of the worksheets that are in that workbook show us. So we're going to select Order. We're going to click OK. And we also check First Row of Data Contains Column Headers or Field Names. That's correct. We know that. We've inspected that. So I'm going to click Order. And I'm going to put it in the Staging Worksheet. And I'm going to begin putting up here in A1. And watch what happens when I click OK. There it is. So this data connection was made between the two worksheets. And what's really nice is when the data changes in the worksheet that contains all the data, this also changes over here. So in other words, we have dynamic updates, which is also a really good thing. Like say if you want your worksheet to be current, if you're constantly getting new data in so that your executive dashboard is immediately updated. So this came into the staging area. And then what we can do is from here we can go and create our pivot tables by going up and clicking Insert. And then finding our pivot tables. And then just going through the whole process, selecting a table of range. And you can see the data range is already here. We will put it in an existing worksheet at a location and then answer the questions. But that's how you make a connection, a data connection, very easy to do between two Excel files. You can also make a data connection again between Excel and a database, an external database or external data sources. And that's really important to know for this course.