Post date: Sep 17, 2018 11:47:58 PM

For the professional modeler Excel's advanced features improve transparency and efficiency while reducing complexity and risk. They also provide greater flexibility, scalability and connectivity. These benefits grow in value as models become more complex and connected to other models, data sources and people.

In the previous lesson we discussed the data model. This lesson uses the data model to provide records to a PivotTable.

Lesson Objectives

At the end of this lesson you will be able to:

• • Create a PivotTable based on the Data Model

What is a PivotTable?

From Overview of PivotTables and Pivot Charts:

A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to analyze numerical data in detail, and answer unanticipated questions about your data. A PivotTable is especially designed for:

• • Querying large amounts of data in many user-friendly ways.

  • Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and creating custom calculations and formulas.

  • Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data for areas of interest to you.

  • Moving rows to columns or columns to rows (or "pivoting") to see different summaries of the source data.

  • Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data enabling you to focus on just the information you want.

  • Presenting concise, attractive, and annotated online or printed reports.

In this model we are using Power Pivot. Power Pivot combines data and data relationships with DAX functions (which are very similar to Excel functions) and PivotTables.

How To Create a Data Model Based PivotTable

Once we have a Data Model (see previous lesson) we can use it to create PivotTables that process data from multiple tables and contain calculations (measures) not possible in standard PivotTables. The process of creating is very similar to normal PivotTables once we get passed the start.

• 1. We start by clicking the Power Pivot then Manage.

• 1. To change the headings simply type over them directly on the PivotTable. To change number formats right click on any number in a column and select the desired number format. Repeat for all numeric columns.

• • 1. Change the column headings to Loan, Payment, and Costs.

    2. Right click any value in the Payment column then select Number Format from the Pop-up menu and change the number format to 2 decimals (with comma if that is appropriate for your region)

    3. Right click any value in the Costs column and format it the same way.

    4. Gray out the Grand Total row (don't delete it).

    5. Use PivotTable Tools > Analyze > PivotTable Name: and name this pvtPC

Assignments

• 1. Read Use Multiple Tables to Create a PivotTable

  2. Read Use the Field List to Arrange Fields in a PivotTable

  3. Read Power Pivot: Powerful Data Analysis and Data Modeling in Excel

  4. Complete the lab

  5. Complete the quiz

Quiz

• 1. A distinct advantage Excel has over printed reports or PDFs is:

     1. Excel can be displayed on an iPhone

     2. Excel can show graphs

     3. Excel can be interactive

     4. All of the above

     5. None of the above

  3. Customers want summaries. They only want detail when summaries look wrong.

• • 1. TRUE

    2. FALSE

• 5. Multiple PivotTables can be placed over a single data table.

• • 1. TRUE

    2. FALSE

• 7. How does segregating Outputs from Process protect model integrity?

• • 1. It lets users play with Outputs while protecting formulas from changes

    2. PivotTables are more secure

    3. Most users don't know how to get to the data model.

    4. All of the above

    5. It doesn't.

• 9. How do PivotTables improve transparency?

• • 1. Their source references and formulas are easier to examine

    2. They provide drilldown

    3. All of the above

    4. They don't

• 11. How do PivotTables speed development?

• • 1. They automatically adjust to changing table sizes

    2. It's quicker to drag and drop fields than to create SUMIFS() or SUMPRODUCT()

    3. Changes are amazingly quick

    4. All of the above

    5. They don't

• 13. How do PivotTables reduce risk?

• • 1. They are more advanced

    2. They replace a sea of formulas

    3. They are compiled C++ code

    4. All of the above

    5. They don't

Summary

PivotTables can present large and complex data sets in concise, easy to understand summaries. They reduce model risk by replacing a sea of formulas, improve transparency with drilldown, and speed development. Power Pivot extends the power of PivotTables by including multiple tables into one PivotTable and adding measures that are not possible with regular PivotTables.