Visualizing Spreadsheets
Add Exponential Value to Excel for Monitoring, Analysis and Modeling
InfoManagement Direct, January 2006
Microsoft Excel has been widely adopted as an easy-to-use and powerful tool for free-form data manipulation. Excel has evolved beyond a simple data calculation tool to the point where it is now used as a sophisticated and flexible repository for collecting, analyzing and summarizing data from multiple sources. People use Excel to track travel expenses, devise budgets and forecasts and create reports. Some users are adept enough to create advanced pivot tables and macros that rival the work of IT programmers.
The power of Excel can be leveraged with visualization in many different ways: enhancing effectiveness, focusing communications, helping make anomalies pop out, facilitating comprehension and empowering collaboration.
Effective Reports
Advertisement
Over the years, Excel has added a host of advanced charting and cell formatting features to facilitate reporting. However, providing more interactive forms of data representation can provide much more utility to rapidly assess data. Consider the following simple example of a risk report:
Figure 1: Before and After Visualization Reporting. The spreadsheets on the left represent two pages out of a risk report with approximately 100 pages of risk scenarios (left image) and resultant impact (center image, generated by standard Excel charts). The same report presented as an animated and interactive visualization (right) consolidates all 100 pages of the information into a single screen.
Using visualizations with Excel for reporting provides:
- Visual structure to help bring together many different types of metrics with potentially different scales and frequencies;
- Multiple representations so that summaries, intermediate aggregations and specific details can be presented in a single interface; and
- Interactions for easily navigating through the various data levels with features such as drill-through tooltips and overlay charts as well as playback animation.
- Visual correlation between the drivers and inputs to the report, to help give an indication of causal effects.
This visualization of the report enables the viewer to instantly see relationships between metrics. By visually grouping related information and adding the ability to see visual patterns across a field of metrics, productivity is increased. As one user said, "With the visualization, in 10 minutes I knew more about the current situation than the subject expert beside me."
Focus Communications
Excel charting combined with PowerPoint has achieved near-ubiquity in presentation - can visualization add more value? Consider the familiar time series data presentation. A traditional line chart displays the entire time series equally in two dimensions but does not draw attention to recent data:
Figure 2: Before and After Visualization Communication. The traditional line chart, left, displays all data equally without any emphasis on recency. What is the key message the author intended to communicate? McDonald's dominates awareness? Wendy's is narrowing the gap? There is a big difference between the top four and the rest? The time series on the right displays the overall context (upper left), a focus on recent top performers (center) and calls out the message explicitly (McDonald's up 6 percent, Wendy's down 3 percent after a key marketing campaign launch).
Visualization can provide detail and focus attention in ways that standard charts cannot by using:
- Visual design, such as overall organization, perspective and color cues; and
- Visual interactions, such as animation, highlights and callouts.
An Excel chart can consolidate a lot of data and tell many stories. Unfortunately, the intended audience may be left to find and decode the message on his own. Alternatively, visualization can be designed and presented to help draw the viewer's attention to the key point while retaining the relevant context. The message is communicated more effectively. As a market researcher said: "We are in the business of communication, and visualization gives us a competitive advantage."
Pop-Out Analysis
Excel is often used as a general purpose data analysis tool. Powerful sorting and filtering functions as well as conditional formatting can help users to try to understand their data. Consider the following example of pricing data:
Figure 3: Before and After Visual Analysis. The data set is 522 days by 60 months of currency pricing data. The spreadsheet on the left can view only 80 rows x 32 columns showing only 2560 values. The visualization on the right shows the full data set: 31320 values, more than 12 times the amount of data visible in the spreadsheet. Additionally, the visualization makes patterns visible that are otherwise impossible to see in the spreadsheet, such as twists and dips over time as well as isolated anomalies lasting only a day or two.
When working with larger data sets, visualization can:
- Clearly show more data than can be seen in the same screen with either a spreadsheet or chart, and
- Make patterns visible that are otherwise difficult to perceive within the spreadsheet.
Using visualization with Excel in a data cleansing application can increase the team's efficiency and quality. "With visualization, the anomalies jump right out," said one particular data warehouse manager. Since the warehouse data was used in downstream risk-forecasting algorithms, improved data quality led to the reduction of risk thresholds, consequently freeing up millions of dollars in capital.
Understand Models
When building spreadsheets with many formulas, it can be a challenge to try to understand the impact of a change and explore various what-if scenarios. Consider the following credit-modeling application:
Page 1 of 3.
