HTML Preview Exel Data Visualization page number 1.
Dynamically Visualizing Data in Excel
Tom Wilson
1 Introduction
In [Wil10], I mentioned some advantages and disadvantages of Microsoft Excel in a discussion about R:
Excel’s advantages are (1) familiarity to others, (2) an interactive nature, and (3) the ability to see a
lot of numbers quickly. The interactive nature comes from using forms/controls to select data and to
turn features on and off. Excel’s disadvantages are (1) slowness in computing large spreadsheets, (2) the
difficulty in creating some chart types (e.g., creating a boxplot), and (3) the changes that occur across
releases and/or inconsistency across platforms (e.g., PC vs. Macintosh). It is certainly an acceptable
analysis tool, especially considering advantage #1.
Some of these advantages and disadvantages may apply to any spreadsheet program. I thought I would expand on
advantage #2. While my usage of Excel has been reduced due to my increasing usage of R, I did learn a lot about
the tool, and I thought it would be good to share some of that knowledge for those that are frequently using it.
The idea of an interactive nature means that someone (e.g., your boss, co-worker, or customer) can interact with
the worksheets and charts in order to see whatever it is that (s)he wants to see (assuming you provided the ability
to do so). On many occasions, I have given a “presentation” using a spreadsheet rather than or in addition to slides.
Think of this as an interactive demo.
I am assuming that the reader is very proficient at Excel, so I will not be explaining many basic concepts. I have
never learned VBA
1
, so you will not find any of that here. I do believe that several things that I will talk about can
be done in VBA, and such implementations would better facilitate their usage. That is an exercise for the reader.
I have not tried any other spreadsheet tool (e.g., OpenOffice.org Calc) to know how much of this information is
applicable. It probably is not, but you will have to figure that out for yourself if you use an alternative program.
Also, everything in this paper should work in the 2003 and 2007 versions of Excel. There may be minor differences
in the details.
A tremendous amount of information concerning Excel charting exists at [Pel10], and I will not cover any of
that material here. There are probably other helpful sites like the one referenced. In this paper, I will discuss how
formatting cells, drawing objects, controls, multi-chart worksheets, and external worksheets aid in the visualization
process.
2 Formatting Cells
The formatting of cells can be a good visual aid for a user when (s)he is interacting with a spreadsheet. I will
discuss two techniques that are fairly straightforward: conditional formatting and dynamic measurement units.
2.1 Conditional Formatting
Conditional formatting allows the formatting for a cell to change based upon several options, one being the cell’s
contents. Excel 2007 allows a lot more control and possibilities than previous versions.
Figure 1 shows an example of how conditional formatting can provide visualization of the data. In this example,
the figure shows the before (rows 1-4) and after effect (rows 6-9) on the same worksheet. In reality, only rows 1-4
would exist. When cell A2 is changed from 5 to 7, the other cells update their formatting.
Table 1 shows the formulas in some of the cells. The formulas in B2:B4 are copied and pasted into C2:AE4 and
B7:AE9. The focus is not on these formulas and so I will not devote a discussion to their purpose. Besides the
formulas that compute the cells’ contents, conditional formatting formulas exist. In this case, there are 6 formulas.
They all take the form: “=MOD(B2,6)=x” where x=1. . . 6. For each formula, a pattern is assigned: 1=green, 2=blue,
1
VBA = Visual Basic for Applications