Data Analysis using Excel 2013 Power View

7/7/2014

http://blog.lrseducationservices.com/?p=886&preview=true

Description of Power View

The Excel 2013 Power View add-in allows you to display visually attractive summaries of your worksheet data. The data may reside as a table in the workbook, it may be external data from a variety of sources, or it may be a data model connected to the Excel workbook via PowerPivot. Once the data source has been defined, the data can be displayed in table, chart, map or Key Performance Indicator format in the Power View worksheet.  Filtering the Power View data allows the user to focus on any slice of the data and provides interactive visualization for Excels 2013’s Business Intelligence tools. This feature is described in the LRS class Excel 2013 Level 3.

Excel 2013 Setup

The Power View add-in comes installed by default in Professional Plus versions of Office however it needs to be turned on in File/Options/Add-Ins.  Turn on Microsoft Office Power Pivot for Excel 2013 and Power View. The Power View feature is now enabled on the Insert tab.

Install Power View add-in

Install Power View add-in

Data for Power View

For my example, I am using data found in the existing workbook, however, the data could also be linked using the connections on the Data tab or data tied to the workbook via PowerPivot. Data in the workbook should be converted into a table prior to linking it to the Power View. Below is a sample of the raw data.
 
Additional Excel Data for Analysis

Additional Excel Data for Analysis

On the Insert tab, click Reports/Power View to create a new worksheet in the existing workbook. It is a blank sheet similar to a blank PivotTable sheet, ready to link to your data. A contextual Power View ribbon tab becomes active when viewing the new worksheet.

Blank Power View Sheet Inserted into Workbook

Blank Power View Sheet Inserted into Workbook

Setting up the Power View sheet

The animal table data will display both as a table and as a pie chart. Turn on the Category and Total fields in the Power View Field list to see the table display. Format the numbers and adjust font size using the Design contextual tab that becomes active when the data is selected.
 
Take the focus off that table and add another set of Category and Total fields. With the second set selected, click Switch Visualization/Other Chart/Pie to convert it to a pie chart.
Table fand Pie Chart in Power View

Table fand Pie Chart in Power View

While the table and chart are attractive, the analysis is easier when you add filtering. Drag the Category and Total fields to the Filters panel next to the Power View window.  Both the chart and table update when adjusting the filters.  Delete the filters in the Filters panel.

Filtering Power View Data

Filtering Power View Data

Add the County book data by turning on these fields: Title and Books and E-Books totals in Table 4. Add a second instance of Title and the Profit totals for e-books and books.  Convert data to a column chart.
2 Data Sets Viewed as a Table and a Chart

2 Data Sets Viewed as a Table and a Chart

Both data sets can be filtered. If the audience needs to focus on a single part of the analysis, there is a pop-out icon in the upper right corner of every data set. That causes the data to take the full area of the Power View.
Power View Focused on One Data Set

Power View Focused on One Data Set

The Power View worksheet can be viewed by sharing the workbook in which it is contained, or it can be deployed to SharePoint. Power Views developed in SharePoint can be exported to PowerPoint.

SUMMARY

The Power View feature in Excel 2013 is a graphical way of examining data that resides in an Excel workbook or is linked to a workbook. It can display data in chart or table format and allows easy filtering for focusing on a portion of the data set. This feature is described in the LRS class Excel 2013 Level 3.