1 When you look back, you get a feel how much we take for granted those liberating baby
steps. During the 1980s, the spreadsheet was the killer app, and because it was so successful, there was little or no need to fundamentally revise the spreadsheet paradigm. The only driv- ers that forced spreadsheet technology to change were the continual improvements in com- puting technology—those allowing for more disk space, larger RAM, and faster speeds. As a result, spreadsheets grew to have a myriad of features, including their own programming language, PivotTables, a wide assortment of esoteric mathematic functions, spell checking, multiple language support, and many others.
The spreadsheet has proven to be a very successful Swiss Army knife. Among the many pur- poses spreadsheets serve, it is well suited for visual analytics.
N O T E
Although there are numerous spreadsheet programs, some of which are web based and some of which are increasingly popular direct competitors, such as the OpenOffice suite, this book specifically addresses Microsoft Excel for two reasons:
■ Excel is the most prevalent spreadsheet program in the world and is regarded as the de facto standard for spreadsheet software.
■ Xcelsius 2008, as you shall soon see, is intimately intertwined with Excel. So unless the text explicitly states otherwise, you can safely infer that the term spread- sheetrefers to an Excel spreadsheet. Further, when the text uses the terms underlying spreadsheetor virtual spreadsheet, it refers to an Excel spreadsheet that is embedded in the Xcelsius 2008 environment.
The Spreadsheet as an Open-Source Dashboard
One of the remarkable features of a spreadsheet is its ability to present numeric data in graphical form. For example, the chart in Figure 1.1 is constructed by using spreadsheet formulas to count the number of cells in the gray shaded region that fall in a range of val- ues. In this example, there are five such ranges and, correspondingly, five vertical columns. Getting this rather tidy-looking histogram to work requires a bit of elbow grease. Let’s peel some of the layers off the onion and get a look at what’s going on behind the scenes. It’s amazing how complex things can be when on the surface they look simple.
As Figure 1.2 shows, behind the chart is a set of hidden computations.
The numbers in column Dare all separated by 6(the value in cell B4). Adding 6to 26results
in 32(the value in cell D9). Adding another 6results in 38(the value in cell D10).
If you carefully examine all the numbers in the gray region, you see that the lowest-value number is 27(which is computed in cell B1). Column Cshows the count of the numbers in
the gray region that are greater than the value in column D. (Interestingly, the numbers in
column Dneed to start from a value of 1fewer than the minimum value.) To get the correct
14 Chapter 1 Motivation for Using Xcelsius 2008
1
Figure 1.1 Histogram chart in a typical spreadsheet.
and the one immediately below it. For instance, the value 4in cell B9 is computed using this
spreadsheet formula: =C8-C9 or this: =30-26 Figure 1.2 Hidden computations needed for the his- togram in Figure 1.1.
15 The Spreadsheet as an Open-Source Dashboard
1 I haven’t even gotten to how the range labels in cells A8through A12are computed. Is your
head spinning?
While many of the steps may seem arbitrary, I can assure you that all the steps are purpose- ful. Unless you are armed for advance knowledge, or happen to know how I think, you may have a hard time figuring out what’s going on, even if the steps are carefully and thoroughly documented.
This spreadsheet with the histogram in some ways qualifies as a dashboard. It may not be a dashboard connected to a practical application, but it is a dashboard nonetheless. If you start changing the numbers in the gray region, the histogram is automatically updated. The num- bers in the gray region don’t even have to be modified by hand. You could have formulas that use some kind of lookup to retrieve values from a table, based on any criteria you care to construct in a spreadsheet formula—even the time of day or whether the current year happens to be a leap year. There’s more complexity here than meets the eye, and there could be more formulas than you care to know about or have any clear idea of what to do with. This is a contrived example of a most rudimentary dashboard in a spreadsheet. By many people’s criteria, it may not even qualify as a dashboard. The key, though, is to understand that if you want to create sophisticated and fancy dashboards in a spreadsheet, you could. Excel has plenty of graphical features. It provides spreadsheet functions and formulas galore. If you’re an ace at VBA programming or have a budget to hire a dedicated programmer, or the luxury of the time to figure it out by yourself, then you may turn up a nice spreadsheet dashboard. Given the choice, however, you would probably opt to have quick turnaround time for dashboard preparation, while simultaneously lowering the skill threshold needed to crank out high-quality dashboards. Unfortunately, although you could use conventional spreadsheets to build dashboards, dashboards created that way will likely fall short of your goals, especially if you want to cost-effectively produce professional-quality dashboards. The problem is that spreadsheets used to build dashboards are effectively open-source dash- boards. The notion of open-source software can connote different things, depending on your perspective:
■ On the positive side, your spreadsheet formulas are exposed for the world to see.
■ On the negative side, your spreadsheet formulas are exposed for the world to see. If you are preparing a dashboard, it may not be in your interest to have all the innards exposed. The chances are that the CEO of your company will not respond well to having all the formulas exposed. And that CEO will certainly not be happy if he or she accidentally clobbers a complicated formula. Even if users of a deployed dashboard respect the spread- sheet enough to avoid tinkering with its structure, there is the nagging issue of protecting intellectual property. A spreadsheet loaded with complicated formulas and conditional logic is just teeming with proprietary knowledge in its purest form. It would surely spell disaster if your dashboard based on an open-source spreadsheet got into the wrong hands. Giving away a sliver of your analyzed data is one thing, but giving away your analytical reasoning is entirely another.
16 Chapter 1 Motivation for Using Xcelsius 2008
1
To be fair, Excel does provide a basis for safeguarding deployed spreadsheets. You could make use of Microsoft’s Information Rights Management, an enterprise-centric framework for establishing policies and roles that you can assign to all the employees of a company. As you might guess, this could entail a fair amount of IT infrastructure, staffing, and budget. Based on your company’s needs, such an initiative might be warranted and cost justified. But it’s a fair bet that this framework is not going to fit every company’s needs. There are other Microsoft options, too, but that is not the focus of this book.
It sure would be nice to have some of the computational features of spreadsheets without the drawbacks cited in the past few pages. The good news is that Xcelsius makes this possible.