Home / Business & Management / Big Data & Analytics / How to Use Microsoft Excel for Data Analysis / How to Import Data from Different Sources
How to Import Data from Different Sources
In this video step, you will discover how to import data from different sources and how to integrate the imported data with the excel data using data models and functions such as VLOOKUP and query tables.
Hello. Welcome. This will be our last part of this course. And if you remember, I was preparing all the support, dashboard, charts, for our sales manager and actually it seems like this work will going to be used for the duration. There’s a lot of interest both by Lucy and others in the company in what we’re doing here. So one of the things that we’re going to do– and this part is to use data beyond the data that was provided originally by Jack our friendly IT person. Because the request [INAUDIBLE] coming is to go more into the demographics of our customers. So far we had some demographic features of the customers.
We had the age that we actually were able to bucket into buckets of ages. We have the gender. But there is a need for more. People want to see more properties of the customer that they can analyse the data by. So it’s time for me to go back to look for the data. And I actually got the data, in this case, on an Access database. So first of all, I’m going to import it into Excel and then we’re going to use it together with our Excel, with our previous data. Now I haven’t shown you before how is the data actually created the way we used it before.
And the reason is that actually in the course that we are offering after this one, there’s a lot about importing data and preparing the data. And I would rather you learn these new techniques about getting the data. They’re teaching you a lot about the classic techniques of Excel. So that’s why we actually kind of use the data as if it was already ready as one big table. But now we have to use more than that. So we’re going to do it. So back to Excel. And we’re going to use data. We’re going to open a new worksheet. And we’re going to do Data Import from Access. The data is in the Downloads folder. There is a table called demographics.
I click on it. I open. Now it immediately skips some part because there’s only one table in this database, only the table that I need. If there were more than one table, I would see some UI to select the tables and ask me what table do I want. In this case, it just goes directly to this dialog, the input dialog. And it tells me what do you want to do with the data. Do you want to have it as a table, as a pivot table? In this case, I want to get it as a table. So I click and just like that, it imported the data into a new table. See this is a table.
It’s a little different than the table we have now because this is called a query table. This is a table that is connected to this database, this database, Access database. And actually if this Access database would be updated, someone will change and get more customers or change the information about customers, and I click here Refresh, this table will be refreshed with the new data. So I see that I have the customer ID, which is good because I also have the customer ID in the original table. And I guess that by having this common key between them, I will be able to connect these two tables.
And I have the marital status of the customer, the yearly income of the customer, number of children, education level. So there’s quite a few demographic details that I have about this customer which could be interesting to analyse the sales and to understand more the behaviour of our customers. Now the normal way that I will go about connecting those two tables would be to start adding new columns to the big table that we already have and use the VLOOKUP function to bring data from one table to another. This is what have been used with Excel for years and years. Most people, I’m pretty sure that a lot of you are familiar with this technique.
But actually I was intrigued by something before. If I go and create myself yet another pivot like we did so many times in this series before, and I start filling some data. Let’s say I want to see, I don’t know, categories and so on. Notice what I have here. I’ve seen it before all the time– more tables. It just kind of cryptic. I don’t know if many of you have paid attention to this option. Let’s see– and actually I want to use more tables. So maybe this is the way to actually use the new table instead of adding all those columns to the existing table. So let’s click on it and see what happens. I click. I see a dialogue.
The dialogue says, do you want to create a new pivot table? Use multiple tables in your analysis. And new pivot table needs to be created using the Data Model. Data Model so far is something I’m not familiar with. But yes I want to use multiple tables. So go ahead, create a new pivot for me. I click yes and it’s now doing something. And I got a new pivot, very similar to the previous one, having the same numbers, same structure. Only that in the field, the fitness for this pivot looks very different from previous pivot tables I ever saw in my life.
I have– instead of just looking at the sales tables and sales data and the columns for the sales data, I actually see two tables as if they’re both available for me to do my pivot. If I open the second one, I see all the data from this other table that I just imported. Interesting. So what if I want to put marital status on Carlos. Can I put one column from one table in columns and one column from another table and rows? Let’s see.
Let’s look at the pivot. So first of all, I see that there is an area of– this yellow area that we’ll look at in a moment. And secondly, I see that it didn’t really work. If you see the same values come as–
the same values are seen for married people, single people, and for the grand total. Like repeated three times, that’s not exactly what I wanted. I wanted to see the breakdown between married and single. But if we look at this wording here, it says relationships between tables may be needed. And there’s also a button for Auto Detect. I don’t really understand what are relationships between tables but I just trust Excel to lead me to go to get my results. So I click also Autodetect. And it tells me that it was detecting relationships, relationship detection was done, and one new relationship was created. Interesting. Now the numbers are indeed different. So we’re accessories for married people.
This is the number for single people. This is the number. The grand total is the same as before. But it’s not now– it’s not repeated three times. So it seems, indeed, that I’m in kind of a new world that I was unfamiliar with. I’m having a pivot based on more than one table. Now, when I, for example, if I want to repeat, if you remember what I did before about adding calculated fields to calculate, for example, the margin on top of this new data, see what happens. The calculated field area is greyed out. Is [INAUDIBLE]. So there are some things which are working great. I can do now and use multiple tables.
On the other hand, the calculated fields that were my friends before are not available. If I, for any– if I do a right click here, it tells me that I can add a measure. So there’s a lot of new things here which I’m not familiar with. And this is actually, as we can summarise what we saw and what we are going to see in the next part, and so we are actually now in a world of making pivot tables from multiple tables. We had one table originally in the Excel. We imported another one. They somehow got into this thing called the Data Model. A relationship was created between them.
I was able to see the numbers coming from– based on one column, one field in the column from one table, another field on the rows from another table. So it’s a brave new world. I probably will need to learn more about it. And this is exactly the whole purpose of the next course that we already have available for you that will take you from here into this world of multi-table pivots of measures, very sophisticated calculations, and also much more powerful ways to import the data into Excel for multiple sources to clean the data and to do many more things that what we did in this part and what you probably have been doing with Excel for a long time.
So I hope you enjoy the next part. And I hope you enjoyed this course. And good luck with the next course and see you.
In this video step, you will discover how to import data from different sources and how to integrate the imported data with the excel data using data models and functions such as VLOOKUP and query tables.
Importing Data
Sometimes you may need to perform data analyses on related data from multiple sources. To import data from another source, you can simply use the Get External Data functionality in the Data section on the Excel ribbon.
Query Tables
Sources such as Access Database can be used as query tables. Query tables are unique in that the data imported into Excel will update as the original source is updated and the Excel data is refreshed.
You can connect your Excel table to the query table by means of a common key between the two tables.
VLOOKUP
VLOOKUP is a function that makes Excel search for a certain value in a column in order to return a value from a different column in the same row.
In its simplest form, the VLOOKUP function says:=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).
Data Models
A data model allows you to integrate data from multiple tables, effectively building a relational data source inside your Excel workbook. Within Excel, Data Models are used transparently, providing tabular data used in pivot tables and pivot charts.
Read these resources for more information on connecting an Access Database to your workbook, importing data into Excel and creating data models and the VLOOKUP function.
Point of Discussion
While it’s good to explore how to create a multi-table pivot table, let’s take a moment to consider the ‘why’. How does this feature of Excel benefit a business? Take a moment to reflect on key points in this step before answering this question.
This article is from the online course:
How to Use Microsoft Excel for Data Analysis
Created by
This article is from the free online
How to Use Microsoft Excel for Data Analysis
Reach your personal and professional goals
Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.
Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.
