microsoft excel data analysis and business modeling answers

#MICROSOFT EXCEL DATA ANALYSIS AND BUSINESS MODELING ANSWERS HOW TO#

A complete list of available tables will be displayed. On the Medals sheet, in the PivotTable Fields List, click All. Rename the Sheet1 as Medals and Sheet2 as Sports. Step 3 − Now you can create relationship between this new table and the other tables that already exist in the Data Model in Excel.

Step 2 − Create a new table with new data. Relationships let you analyze your collections of the data in Excel, and create interesting and aesthetic reports from the data you import. If you do not import the tables at the same time, or if the data is from different sources or if you add new tables to your Workbook, you have to create the Relationships among the Tables by yourself. As you imported all the tables from the database together at the same time, Excel recreated the relationships in its Data Model. This was possible because of the pre-existing relationships among the tables in the source database. You could analyze your data from the different tables and arrive at the specific report you want in just a few steps. The PivotTable displays only those regions, which has more than total 80 medals. The Value Filters dialog box for the count of Medals is greater than appears. Step 7 − Select Value Filters and then select Greater Than… Step 6 − Click the dropdown list button to the right of the Column labels. Step 5 − From the Medals table, select Medal again and drag it into the FILTERS area. Step 4 − In PivotTable Fields, from the Medals table, drag Medal to the VALUES area. This can be done either in PivotTable Fields area, or from the Row Labels filter in the PivotTable itself. Step 3 − Filter Discipline to display only five sports: Archery, Diving, Fencing, Figure Skating, and Speed Skating. Step 2 − Drag Discipline from the Disciplines table to the ROWS area. Drag the NOC_CountryRegion field in the Medals table to the COLUMNS area. In PivotTable Fields, click on the arrow beside the table - Medals to expand it to show the fields in that table. Even if you are not sure of the final report that you want, you can play with the data and choose the best-suited report.

#MICROSOFT EXCEL DATA ANALYSIS AND BUSINESS MODELING ANSWERS HOW TO#

Step 1 − You know how to add fields to PivotTable and drag fields across areas. Now, you can explore data in the five tables, which have relationships defined among them. You have imported the data into Excel and the Data Model is created automatically. Step 8 − The data is imported, and a PivotTable is created using the imported tables. Notice that the checkbox at the bottom of the window - ‘Add this data to the Data Model’ is selected and disabled. This option imports the tables into Excel and prepares a PivotTable for analyzing the imported tables. Check the ‘Enable selection of multiple tables’ box, and select all the tables.

Step 6 − Tables in a database are similar to the tables in Excel. Step 5 − The Select Table window, displaying all the tables found in the database, appears. Step 4 − Select Events.accdb, Events Access Database file. Step 3 − In the Get External Data group, click on the option From Access. Step 1 − Open a new blank Workbook in Excel. The existing database relationships between those tables is used to create the Data Model in Excel.

A Data Model integrates the tables, enabling extensive analysis using PivotTables, Power Pivot, and Power View.Ī Data Model is created automatically when you import two or more tables simultaneously from a database. You can also make use of the Microsoft Business Intelligence features and capabilities in Excel, PivotTables, Power Pivot, and Power View.ĭata Model is used for building a model where data from various sources can be combined by creating relationships among the data sources. You can build a data model, then create amazing interactive reports using Power View. Excel 2013 has powerful data analysis features.

Microsoft excel data analysis and business modeling answers