How to make a data connection between two Excel workbooks

in this video demonstration we're going to take a data source which is an Excel spreadsheet and connected 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 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 dot 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 and 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 good connections here because I've used this for many other projects but in your case you probably may not have any connections or we 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 Super Store dashboard and there it is super store sales dot xlsx is the workbook that contains all of our data so we select that we click open and when we click open the 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 an a.1 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 our 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 to 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 the table arranged and you can see the data ranges already exist here we would 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 data connection again between Excel and the data base and external database or external data sources and that's really important to know for this course

Channel: Management Information Systems

Views: 702,397

Rating: 4.684998 out of 5

Keywords: MIS, management, information, systems, decision, models, Excel, analytics, data connection in excel, data connection

Id: ze7c96Lkrww

Length: 4min 40sec (280 seconds)

Published: Thu Sep 22 2016