Dynamic Chart Presentation Solutions That Pack a Punch in Microsoft Office Excel 2007

A Lot of Data—Compressed Information

This section presents two solutions (or three, depending on the how you count them). Each has a similar background (large amounts of data) and the same purpose (heavily compressed information), but can have a completely different appearance. This is hardly surprising, because the objective of a presentation or piece of work determines the layout of your model. Even just one body of data and one basic objective can give rise to hundreds of different solutions.

Chart of Key Data Over 10 Years

The Focus 1 worksheet in the file 1003_TenYears.xlsx is designed for the Full screen presentation view and is protected without a password.

You already know this model from the later sections in Chapter 4, “Colors, Areas, and Outlines,” which discussed the model’s format. We will now discuss its overall structure.

The percentage line chart shows the development of eight operational key figures over a period of ten years. The chart basis here is the values from 1999, each of which has been equated with 100 percent. Therefore, the lines that trend upwards and downwards from the base line describe the direction and extent of any changes. Each line has an “on/off switch” to the right of the chart. Therefore, with a mouse click, a user can use a table that also acts as a legend to determine which lines should appear, how many of them, and in which combination. This produces a multifaceted, multivariable analysis: for example, how line A changes in terms of waveforms and intervals in relation to the 100 percent horizontal and in relation to line B, and at the same time to line C, or how B relates to C, or how does it look if—and so on. These simple figures can provide a wealth of information and conclusions. Let’s just answer one of these questions in the context of Figure 10-10. (Since the different line colors can hardy be differentiated in the gray scale print of the book, you should use the file to follow along.) From 2001 to 2005, the company reduced its product range significantly. Around the same time—from 2002 and steadily after that—it witnessed a considerable rise in the number of high-revenue A customers and therefore (in a delayed yet pleasantly synchronous trend that did not seem to be coincidental) a significant jump in sales.

The data contained in the Data 1 worksheet is divided into two blocks (see Figure 10-11):

• The upper block, Data, contains absolute values. Clearly, these numbers are already extremely compressed and are based on several thousand individual values.

• The lower block, Chart Basis, contains percentages. For 1999, the constant 100 was entered in all cells. Each of the subsequent years has formulas whose results (when you use each year’s absolute values) express its relationship with the start year as a percentage (for more information, see the tip provided below). However, the results (and therefore the chart line) are only shown if the relevant switch for this line is “on.”

The formula in cell G16 (in the context of Figure 10-11):

=IF(rL1.GrossSalesSel=TRUE,INT(G6/$E6%),#N/A)) as a statement: “if the value in the cell rL1.GrossSalesSel is TRUE (if the corresponding CheckBox has been activated), the result is an integer that represents the percentage of the G6 value in relation to the E6 value. Otherwise (if the corresponding CheckBox has been deactivated), the result is #N/A.”

The lower right insert in Figure 10-11 originated in the Lists 1 worksheet where each CheckBox also has its own clearly discernible cell link.

The technical setup of the Focus 1 presentation sheet provides only a handful of surprises for you, the most important being:

• The value axis has a fixed scaling and the category axis intersects it at the axis value 100.

• The label position of the category axis is set to Low.

• The chart area is completely transparent. Therefore, its background is the fill color of the table. This makes it easy to perceive the small table on the right as a “legend with switches.”

• To reinforce this last impression: when you deactivate the entries, the color signals of the entries are deleted and the texts are hidden. Of course, this is based on conditional formatting. In this case, these only refer indirectly to the cell links to the CheckBoxes, namely to those values generated as hidden values in column Q. For example, Q8:

  =IF(rL1.GrossSalesSel=TRUE,"x",""). The reason for this detour is that it is much easier to construct the formula rules for conditional formatting if at the same time you can refer to a uniform cell range with the contents "x" or "" instead of having to use eight different range names.

• The CheckBoxes are not labeled and are transparent. However, their width spans the neighboring text in a table cell. As a result, the user can also (therefore indirectly) click the text whose line he wishes to see in the chart. This is both sensible and convenient.

• The logo is stored on the CD-ROM under \Materials\Pictures\LogoDS.tif. Chapter 5, “Graphical Objects,” provides information about how to delete its ambient color, scale the logo, rotate it, and create its reflection.

• What appears to be a column to the left of the chart is simply cell formatting. The same is true of the color gradient below the chart. Access via: Home/Font/click the Dialog Box Launcher. In the Format Cells dialog box, on the Fill tab, click Fill Effects.

Filtering with the Filter—and Filtering with Controls

This section shows how to present the findings of a study. You were introduced to some of this data and its presentation in Chapter 2, which was concerned with evaluating customer numbers as part of a “summer campaign.” The following is a brief recap: a retail business with 100 branches launched a five-week promotional campaign to attract more customers into its branches and compensate for the expected seasonal dip in sales over the summer months. The chart’s purpose can be summarized this way: “we launched a campaign and now want to see if it attracted more customers and, if so, when, and to which branches.”

We will now take a look at the revenue findings from the same study, analyzing whether this summer campaign was successful in terms of revenue and, if necessary, how it succeeded.

This model brings us back to the subject of “filtering,” which was mentioned briefly in Chapter 8, “Chart Types—Conventional and Exceptional.” In this regard, the file 1004_ SummerCampaign.xlsx contains two options that are based on different objectives or requirements:

• The Controlling department of the company, which has proficient Excel users, uses the pivot system (not part of the description here) and a slender filter model to analyze data. This gives rise to versatile analyses that have numerous selections and answer a multitude of questions. If, for example, one of the settings does not make any sense and produces strange results (or none at all), this is not a problem because the setting is quickly identified and corrected using the resources within Excel.

• Even though the management team of a company, which does not necessarily have to be proficient in Excel (that’s why companies have specialists), has exactly the same data, it is housed in a much more “closely knitted” model that acts as a filtering report module rather than a filtering analysis instrument. It is not as slender as the Controlling module and it can’t answer every question on the spur of the moment (that’s why companies have financial controllers). However, it can answer most questions, especially the most important ones, without relying on any external assistance. Furthermore, it can do this in the easiest way possible: just a few mouse clicks in four selection lists containing clear text will retrieve the necessary information, without risk of error, and without any irritating program responses. That’s exactly how it should be.

From a technical perspective, lots of things that have already been the subject of various discussions in this book converge here. However, Excel 2007 also introduces you to something completely new: formulas that virtually produce any differentiable filter result, without you having to filter!

The Focus 1 Filter and Focus 2 Formulas worksheets in the file 1004_SummerCampaign.xlsx are designed for the Full screen presentation view and are protected without a password. To test the model, you should select these settings or leave them unchanged. Naturally, you should also unprotect the sheet if you wish to examine the structures.

Variant 1: The Complex Analysis Module

Activate the Focus 1 Filter worksheet, which is the analysis module for Controlling.

What is available and what is possible?

• Unlike many of the models introduced earlier, this module houses everything in just one sheet: the master data, chart basis, tabular results display, and chart.

• The sales figures for 100 branches and 10 calendar weeks are listed below a “frozen” sheet. The branches are classified into several columns; that is, assigned grouping characteristics. This is extremely important for variable filter analysis:

  • Column Type: A, B, C—three different types of branches (divided according to type of business, foundation type, and localization at the business location)

  • Column RGN: N, E, S, W—regionalization according to the cardinal points of the compass

  • Column CS: 1, 2, 3—differentiation according to the number of inhabitants where the business is located

  • Column since: years 1999 to 2006 (inclusive)—the year in which the branch was founded or acquired

  • Column User: This stands for user-defined (custom) and is a very important column in filter models of this type. In practice, however, this kind of column is frequently missing, which is most unfortunate. Here, the user can specify his own classifications. In addition to existing and standardized characteristics, he can also define whatever he wants, wherever he wants. Therefore, he can also filter whatever he wants, however he wants.

Now let’s take a look at the views that are currently possible. If you are working in a protected worksheet, take a look at the row numbers shown in Figure 10-12:

• If the sheet does not have any filters, only the total results in row 6 are shown.

• If you have set a filter, the absolute and relative filter results are also shown in rows 7 and 8.

• If you have activated the CheckBox with the text Show Chart, all of the data (the “whole”) is represented by an orange line. If a filter result also exists, it is represented by a blue line. Otherwise, the chart is hidden. (In many cases, the percentage results in row 8 are considerably more important than the trend waveform shown in the chart.)

  

  Figure 10-12 Such structures provide a large number of analysis options.

Now let’s take a look at the current settings made in Figure 10-12, where the user has set some filters. In the Type column, he has selected types A and C (respectively excluded type B). He has also activated the CheckBox. As a result, all of the aforementioned displays are shown.

In this sheet, cell F8 (rF1.FilterID) is particularly important. Its value expresses the percentage of branches contained in the filter. Therefore, if this number is 100, the sheet is not filtered. This specification is used by several formulas. For example:

K7 =IF(rF1.FilterID=100,"",SUBTOTAL(9,K$11:K$106))
K8 =IF(rF1.FilterID=100,"",K$7/K$6%)

The chart basis (see Figure 10-13) is housed in the cell range X6:AG7 and made invisible through the font color chosen.

There are different formulas for creating these two data series. For example:

X6 =IF(rL1.CheckChartSel=TRUE,K6,#N/A)

If the CheckBox is activated, the value is read from cell K6. Otherwise, it remains as #N/A. Consequently, the line for the total data from row 6 is shown or hidden.

X7 =IF(AND(rL1.CheckChartSel=TRUE,rF1.FilterID<>100),K7,#N/A)

If the CheckBox is activated and if the value in cell rF1.FilterID is not 100, the value is read from cell K7. Otherwise, it remains as #N/A. The line for the filter data from row 7 is shown or hidden.

Experiment with different filters and take advantage of the clearly improved and more intuitively manageable options provided in Excel 2007. There are numerous analytical options. You will be spoiled by having so much choice, which is another reason why I highly recommend this type of data analysis.

Variant 2: The Easy-to-Use Report Module

Activate the Focus 2 Formulas worksheet, which is the report module for the Management team. This sheet in the file 1004_SummerCampaign.xlsx is designed for the Full screen presentation view and is protected without a password. The filter options are suppressed, but can be activated without great difficulty. To keep this as a viable option in the future, the data structure as of row 24 corresponds to the filter model that I introduced above.

If you have not yet discovered all of the hidden new features of Excel, you will be very interested to learn that you can use simple form controls to group together a combination of criteria that Excel will immediately use to generate a corresponding results table and chart. It is worth noting that this does not involve any programming, has nothing to do with a pivot system, and is not the result of a filtering process.

Behind the scenes lie new functions whose results once could be achieved only with painstaking efforts and even then only in part (through the use of array formulas). I had longed for functions such as SUMIFS and COUNTIFS (note the S at the end; it’s not a typing error) for such a long time and was overjoyed to find them. They make it considerably easier to perform lots of calculation tasks and to create user-friendly models.

Before I explain these in greater detail, let me first provide a brief overview of what can be achieved with the model used here. Note the following points with reference to Figure 10-14:

• If you select All . . . in each of the four controls, a chart that represents the whole is shown. Orange columns have white/blue dots on their upper edge, all of which are supplemented by a linear trend line (colored blue). These are relatively insignificant for the user, who needs to see them only once to know which purpose they serve. Furthermore, the line is a permanent fixture because its main purpose is to enable observers to make comparisons.

• If you use the controls to select any other combination, the chart becomes a comparative presentation: a selected group in relation to the whole. The blue dots are now dropping down and show, as a data series within the Total columns, those values that correspond to the custom query. There are now two linear trend lines, a red trend line for the whole and a blue trend line for the combination selected. This data material, which assesses the success of a campaign, contains a particularly high and immediately intelligible informational value: the upper trend line shows the campaign’s success in all branches. Therefore, if the lower trend line runs in parallel, the data selected essentially corresponds, in a qualitative manner, to the whole. If the lower trend line reflects an “inconsistency”, the results of the selection were better or worse than the whole. Therefore, this is a method of fast, optional group analysis in relation to the overall success of the campaign. And if you wish to discuss the results in greater detail? Or if you wish to compare such groups with each other? This or similar Excel solutions could answer all your “if” questions. (Such a report module existed in this case, but deliberately not for the Management team, but for the working environment of the Controller.)

  

  Figure 10-14 The Report module provides fast results and is child’s play to use.

To be able to describe how this solution works, we’ll need to take a detour via the Lists 1 sheet.

Here, you see the list contents (in an arrangement that is now familiar to you) for the controls in the focus sheet (see Figure 10-15). For a better overview, copies of the controls are also provided in this sheet. Associated search terms are listed to the right of the clear text list contents. These are similar to those used in the filter analysis module to classify the branches. It is exactly those search terms that we require immediately. Whoever uses this module doesn’t have to know which terms these are, where they are, and how they are used. From a functional perspective, the following happens:

1. The user selects a clear text entry (specific information) in a control and generates a number by doing so.

2. The number that is generated is used to determine the position of a search criterion (abstract information).

3. The search criterion found in this way is read in the focus sheet and used there by selective formulas.

4. These formulas write their results to a result line that is used to produce a chart line. Both the numeric values and the chart provide the information requested via the clear text query.

   

   Figure 10-15 The list selection in the control generates a search criterion for a formula.

Therefore, the user only needs to know the importance of clear text information in a control, and he only needs to be able to use the mouse to click this clear text information. A structure that he does not have to be familiar with or even be aware of takes care of everything else. This paraphrases my approach to a useful management information system.

I will now describe the main formulas (the other few are not worth mentioning here). You were already familiar with the use of SUMIF and COUNTIF before reading this book or you were introduced to them in Chapter 7, “Elements of Dynamization,” (if necessary, take another quick look at this chapter). You can understand and use the new functions SUMIFS and COUNTIFS as multiple variants of basic forms that are already very helpful.

But first, the theory.

The practical use of COUNTIFS is evident in our example in Figure 10-16. Structures that would otherwise be hidden by formatting are revealed:

• Cells F19:I19 contain the search criteria that result from clicking with the mouse in the control (see also Figure 10-15 and its accompanying text). They are read here through the use of OFFSET.

• The formula in cell D20 uses all four search criteria to find rows in which all four criteria exist or apply (columns E to H, column by column).

  

  Figure 10-16 All this effort for just one number? It’s an effort that will pay dividends!

The custom number format 0" Branches = " is defined in cell D20 because: a) there was insufficient space outside the cell for this label, and b) text can’t be generated in the cell itself because the number produced in each case is processed further in the neighboring cell for calculation.

The structure of the SUMIFS formulas in row 19 are very similar in terms of how they are created, but they are a bit more complicated. In Figure 10-17, three of these formulas are shown with their reference lines. If the principle—sum there, find a search criterion there, use the criterion stored there—is clear, then a formula such as

=SUMIFS(J$25:J$120,
$E$25:$E$120,$F$19,
$F$25:$F$120,$G$19,
$G$25:$G$120,$H$19,
$H$25:$H$120,$I$19)

won’t frighten you, especially if you know that these are just four of 127 possible segments.

The results of the SUMIFS formulas in row 19 are the data source for the blue chart elements. The results of the sum formulas in row 21 (ludicrous, aren’t they?) are the data source for the orange chart elements.

Even though this concludes my discussion on the topic, it does not cover everything there is to know about analyzing the campaign. In Chapter 2, you analyzed the quantities, and you examined the revenue generated. You still need the following, for example:

• Single comparison of branches at revenue level,

• Group comparison

• Connections between customer numbers and business volumes as key figures: revenue per customer

You now have the campaign data, and this book provides numerous recommendations as well as instructions for displaying data. Therefore, you may want to use all of this material to develop your own model.