Learn more about this course.

Importing Data from Different Sources

In the previous section, you learned about using treemaps and sunburst charts to report and visualise hierarchical data.

In the final video step of this course, you’ll 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 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).

Don’t forget to take a moment to go through the resources provided. They will give you a broader overview of how to connect an access database to your workbook, how to import data into Excel and create a data model, and how the VLOOKUP function works.

Data Models

A data model allows you to integrate data from multiple tables, building a relational data source inside your Excel workbook. In Excel, data models are used transparently and provide the 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:

Connect an Access database to your workbook

Tutorial: Import Data into Excel, and Create a Data Model

VLOOKUP function

Join the 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.

Use the Discussion section below and let us know your thoughts. Try to respond to at least one other post and once you’re happy with your contribution, click the Mark as Complete button to move on to the next step.