Visualizing data in Excel
.png "patterns & practices Developer Center")
From: Developing big data solutions on Microsoft Azure HDInsight
After data has been imported using any of the techniques described in this section of the guide, business users can employ the full range of Excel analytical and visualization functionality to explore the data and create reports that include color-coded data values, charts, indicators, and interactive slicers and timelines.
Built-in charts, formatting, PivotTables, and PivotCharts
All editions of Excel support data visualization through charts, data bars, sparklines, and conditional formatting. These can be used to great effect when creating graphical representations of data in a worksheet. PivotTables and PivotCharts are a common way to aggregate data values across multiple dimensions, and enable users to see the relationships between these dimensions. Additionally, you can use slicers and timelines to support interactive filtering of data visualizations.
For example, having imported a table of weather data, you could use a PivotTable or PivotChart to view temperature aggregated by month and geographical region, and data bars to make it easy to compare individual values in the worksheet, as shown in Figure 1.
.png "Figure 1 - Analyzing data with a PivotTable and a PivotChart in Excel")
Figure 1 - Analyzing data with a PivotTable and a PivotChart in Excel
Power View
Power View is a data visualization technology that enables interactive, graphical exploration of data in a data model. Power View is available as a component of SQL Server 2012 Reporting Services when integrated with SharePoint Server, but is also available in Excel 2013 Professional Plus and Office 365 ProPlus. Using Power View you can create interactive visualizations that make it easy to explore relationships and trends in the data. Figure 2 shows how Power View can be used to visualize the weather data in the data model described in the topic PowerPivot.
.png "Figure 2 - Visualizing data with Power View")
Figure 2 - Visualizing data with Power View
Power Map
Power Map is an Excel add-in for Microsoft Office 365 Power BI subscribers that enables you to visualize geographic and temporal analytical data on a map. With Power Map you can display geographically related values on an interactive map, and create a virtual tour that shows the data in 3D. If the data has a temporal dimension you can incorporate time-lapse animation into the tour to illustrate how the values change over time. Figure 3 shows a Power Map visualization in Excel.
.png "Figure 3 - Visualizing geographic data with Power Map")
Figure 3 - Visualizing geographic data with Power Map
Note
At the time this guide was written, Power Map was only available when Excel is installed from an Office 365 site where a Power BI subscription is supported. It is not available for the retail edition of Microsoft Office 2013. For more information about Power BI and how to obtain it, see the Power BI section of the Office website.
Guidelines for visualizing data from HDInsight in Excel
Consider the following guidelines when choosing a visualization tool for HDInsight data:
- Use Power View when you need to explore data using a range of data visualizations. Power View is particularly effective when you want to explore relationships between data in multiple tables in a PowerPivot data model, but can also be used to visualize data in a single worksheet.
- Use Power Map when you want to show changes in geographically-related data values over time. Your data must include at least one geographic field, and must also include a temporal field if you want to visualize changes to data over time.
- Use native PivotCharts and conditional formatting when you want to create data visualizations in workbooks that will be opened in versions of Excel that do not support Power View or Power Map.