With the data imported into Excel, and the Data Model automatically created, you’re ready to explore the data.
Explore data using a PivotTable
Exploring imported data is easy using a PivotTable. In a PivotTable, you drag fields (similar to columns in Excel) from tables (like the tables you just imported from the Access database) into different areas of the PivotTable to adjust how it presents your data. A PivotTable has four areas: FILTERS, COLUMNS, ROWS, and VALUES.
It might take some experimenting to determine which area a field should be dragged to. You can drag as many or few fields from your tables as you like, until the PivotTable presents your data how you want to see it. Feel free to explore by dragging fields into different areas of the PivotTable; the underlying data is not affected when you arrange fields in a PivotTable.
Let’s explore the Olympic Medals data in the PivotTable, starting with Olympic medalists organized by discipline, medal type, and the athlete’s country or region.
Your PivotTable looks like the following screen.
With little effort, you now have a basic PivotTable that includes fields from three different tables. What made this task so simple were the pre-existing relationships among the tables. Because table relationships existed in the source database, and because you imported all the tables in a single operation, Excel could recreate those table relationships in its Data Model.
But what if your data originates from different sources, or is imported at a later time? Typically, you can create relationships with new data based on matching columns. In the next step, you import additional tables, and learn how to create new relationships.
Now let’s import data from another source, this time from an existing workbook, then specify the relationships between our existing data and the new data. Relationships let you analyze collections of data in Excel, and create interesting and immersive visualizations from the data you import.
Let’s start by creating a blank worksheet, then import data from an Excel workbook.
Now that we’ve imported data from an Excel workbook, let’s import data from a table we find on a web page, or any other source from which we can copy and paste into Excel. In the following steps, you add the Olympic host cities from a table.
City | NOC_CountryRegion | Alpha-2 Code | Edition | Season |
---|---|---|---|---|
Melbourne / Stockholm | AUS | AS | 1956 | Summer |
Sydney | AUS | AS | 2000 | Summer |
Innsbruck | AUT | AT | 1964 | Winter |
Innsbruck | AUT | AT | 1976 | Winter |
Antwerp | BEL | BE | 1920 | Summer |
Antwerp | BEL | BE | 1920 | Winter |
Montreal | CAN | CA | 1976 | Summer |
Lake Placid | CAN | CA | 1980 | Winter |
Calgary | CAN | CA | 1988 | Winter |
St. Moritz | SUI | SZ | 1928 | Winter |
St. Moritz | SUI | SZ | 1948 | Winter |
Beijing | CHN | CH | 2008 | Summer |
Berlin | GER | GM | 1936 | Summer |
Garmisch-Partenkirchen | GER | GM | 1936 | Winter |
Barcelona | ESP | SP | 1992 | Summer |
Helsinki | FIN | FI | 1952 | Summer |
Paris | FRA | FR | 1900 | Summer |
Paris | FRA | FR | 1924 | Summer |
Chamonix | FRA | FR | 1924 | Winter |
Grenoble | FRA | FR | 1968 | Winter |
Albertville | FRA | FR | 1992 | Winter |
London | GBR | UK | 1908 | Summer |
London | GBR | UK | 1908 | Winter |
London | GBR | UK | 1948 | Summer |
Munich | GER | DE | 1972 | Summer |
Athens | GRC | GR | 2004 | Summer |
Cortina d'Ampezzo | ITA | IT | 1956 | Winter |
Rome | ITA | IT | 1960 | Summer |
Turin | ITA | IT | 2006 | Winter |
Tokyo | JPN | JA | 1964 | Summer |
Sapporo | JPN | JA | 1972 | Winter |
Nagano | JPN | JA | 1998 | Winter |
Seoul | KOR | KS | 1988 | Summer |
Mexico | MEX | MX | 1968 | Summer |
Amsterdam | NED | NL | 1928 | Summer |
Oslo | NOR | NO | 1952 | Winter |
Lillehammer | NOR | NO | 1994 | Winter |
Stockholm | SWE | SW | 1912 | Summer |
St Louis | USA | US | 1904 | Summer |
Los Angeles | USA | US | 1932 | Summer |
Lake Placid | USA | US | 1932 | Winter |
Squaw Valley | USA | US | 1960 | Winter |
Moscow | URS | RU | 1980 | Summer |
Los Angeles | USA | US | 1984 | Summer |
Atlanta | USA | US | 1996 | Summer |
Salt Lake City | USA | US | 2002 | Winter |
Sarajevo | YUG | YU | 1984 | Winter |
Now that you have an Excel workbook with tables, you can create relationships between them. Creating relationships between tables lets you mash up the data from the two tables.
You can immediately begin using fields in your PivotTable from the imported tables. If Excel can’t determine how to incorporate a field into the PivotTable, a relationship must be established with the existing Data Model. In the following steps, you learn how to create a relationship between data you imported from different sources.
The PivotTable changes to reflect the new relationship. But the PivotTable doesn’t look right quite yet, because of the ordering of fields in the ROWS area. Discipline is a subcategory of a given sport, but since we arranged Discipline above Sport in the ROWS area, it’s not organized properly. The following screen shows this unwanted ordering.
Behind the scenes, Excel is building a Data Model that can be used throughout the workbook, in any PivotTable, PivotChart, in Power Pivot, or any Power View report. Table relationships are the basis of a Data Model, and what determine navigation and calculation paths.
In the next tutorial, Extend Data Model relationships using Excel 2013, Power Pivot, and DAX, you build on what you learned here, and step through extending the Data Model using a powerful and visual Excel add-in called Power Pivot. You also learn how to calculate columns in a table, and use that calculated column so that an otherwise unrelated table can be added to your Data Model.
Review What You’ve Learned
You now have an Excel workbook that includes a PivotTable accessing data in multiple tables, several of which you imported separately. You learned to import from a database, from another Excel workbook, and from copying data and pasting it into Excel.
To make the data work together, you had to create a table relationship that Excel used to correlate the rows. You also learned that having columns in one table that correlate to data in another table is essential for creating relationships, and for looking up related rows.
You’re ready for the next tutorial in this series. Here’s a link:
QUIZ
Want to see how well you remember what you learned? Here’s your chance. The following quiz highlights features, capabilities, or requirements you learned about in this tutorial. At the bottom of the page, you’ll find the answers. Good luck!
Question 1: Why is it important to convert imported data into tables?
A: You don’t have to convert them into tables, because all imported data is automatically turned into tables.
B: If you convert imported data into tables, they will be excluded from the Data Model. Only when they’re excluded from the Data Model are they available in PivotTables, Power Pivot, and Power View.
C: If you convert imported data into tables, they can be included in the Data Model, and be made available to PivotTables, Power Pivot, and Power View.
D: You cannot convert imported data into tables.
Question 2: Which of the following data sources can you import into Excel, and include in the Data Model?
A: Access Databases, and many other databases as well.
B: Existing Excel files.
C: Anything you can copy and paste into Excel and format as a table, including data tables in websites, documents, or anything else that can be pasted into Excel.
D: All of the above
Question 3: In a PivotTable, what happens when you reorder fields in the four PivotTable Fields areas?
A: Nothing – you cannot reorder fields once you place them in the PivotTable Fields areas.
B: The PivotTable format is changed to reflect the layout, but underlying data is unaffected.
C: The PivotTable format is changed to reflect the layout, and all underlying data is permanently changed.
D: The underlying data is changed, resulting in new data sets.
Question 4: When creating a relationship between tables, what is required?
A: Neither table can have any column that contains unique, non-repeated values.
B: One table must not be part of the Excel workbook.
C: The columns must not be converted to tables.
D: None of the above is correct.
Quiz Answers