A data source is a location or repository that provides data, which can be used for various purposes such as analysis, reporting, or decision-making. Connecting to a data source in Excel refers to establishing a link between an Excel workbook and an external data source, such as a database, text file, or web page, to import and refresh data. To do this, open your Microsoft excel:
1. Click on “Blank workbook”.
- A new blank sheet will open.
- Go to “Data tab” and click on “existing connection”, and a menu will dropdown.
- Click on “All connections” (a dropdown menu will also appear)
- You can either create a connection in the workbook, network, or your computer, but for the sake of this class we will be connecting files on the computer. So, click on “connection files on the computer”.
- Then click on “Browse for more” at the bottom of the displayed box.
- Still, on the displayed box, scroll down and click on your “Downloads” (Note: you can only do this successfully if you have a data set downloaded on computer system).
- Click on “Data set”.
- Click on the data set which you have downloaded on your system . For the sake of the class click on “sampledatafoodsales”.
- Then click on “open” at the bottom of the displayed box.
- A displayed message box showing names of tables from the dataset, will appear.
- Then click on “Foodsales” since that’s the one we need for the class, and then press “ok” button.
- A message box will appear asking you where you want this data in your workbook. Click on “Tables” and click on “Existing worksheet”
- Your data will be loaded. If you want to create a connection from your web, simply go to the “Data” tab .
- Click on “From web” at the top-left hand side of your system (Wait for a message box to appear).
- When the message box appears, you will be requested to enter the URL of the table where you want to create the connection.
- Go to your web browse and search about “Economy of Nigeria” (Note: This is for the purpose of the class ).
- Copy the link by clicking on the written text on search bar and press ctrl c on your key board to copy.
- Go back to your excel, and click on the message box, and press ctrl v to paste the link you copied from your browser and then click “ok” (Make sure you are connected to your internet). Wait for it to load.
- It will load and display all the tables from the website which you have copied it URL.
- You can preview the tables by clicking on them.
- You can also do a web preview by clicking on “web view”.
- But, we want to load just one table from the many tables listed. So, we can go ahead and click the check box beside the “Economic history[edit]” table (For the purpose of the class).
- Then hit the “Load” bottom under the message box.
- And it will create a connection for it.
- Click on “queries” at the right hand side of the excel and it will display all the queries. As you can see there are just two queries displayed
- Click on the second query to select.
- Then, at the bottom of your excel, close to where “sheet 1” is written you will see + sign click on it to create new sheet.
- A new sheet will appear.
- Click on the first query.
- On the “Data” tab click on “Existing connections”.
- A message box will appear.
- Click on the first query “Economic history[edit]” to select. And click “open”.
- A message box will appear asking you how you want to view your data in your workbook.
- As you can see “sheet 1” already has data. So, click on “sheet 2” which you created earlier.
- Check down, beside “sheet 1” you will see “sheet 2” and then click on it.
- Then move your cursor and click on the first empty cell at the left.
- Then press “ok” on the message box displayed.
- And it will be loaded.
- You can also connect to different data by going to the “Data” tab.
- Click on “Get data” at the left hand side of your excel.
- You will see different kind of data source either “From Azure”.
- Or “From Database”.
- Or “Combine queries”.
- Or “From other sources”.
- Or “From online services” then you can manually add it and it will create a connection for you
- Once the data connection is created go to “Existing connection” and click on it, and a message box will appear.
- Choose from the message box you can choose the particular data that you want to load.
- Then repeat the same step in “30” by clicking the plus sign close to “sheet 2” and add new sheet i.e “sheet 3”.
- And then click “open” on the displayed message box.
Leave a Reply