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

12/10/2008

Contents

A Little Data; a Lot of Information

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

The sales figures for 10 products and their regional distribution are displayed here. Select a product in the ComboBox at the top of the sheet. You may be surprised to see a two-column list that shows not only the product names but also their rankings. When a user selects a product, he or she can also become familiar with its ranking.

Figure 10-5 Copious information: absolute values, rank, regional success, percentage distribution

The results of your decision are shown in several different ways in this extensively designed presentation chart whose main purpose is to “look good”:

The header bar contains the product, its total sales in thousands of dollars, and its rank.
The columns on the map represent the subtotals of the four regional sales values for the product selected.
Each slice of the pie chart represents the percentages of the regional sales values for the product selected.

The source data and chart ranges are presented together in the Data 1 and Basis worksheet (see Figure 10-6). This is a simple structure with absolutely no surprises or special features to report. Therefore, I’ll keep my comments brief.

Figure 10-6 Some source data and five small chart ranges, that’s all you need here.

Any cells that contain formulas are colored here.
The ranks that are copied to the ComboBox and header row of the Focus 1 sheet are calculated in row 17.
The product-specific data from rows 12 to 16 is read in the rows 19 to 23. The values from the columns that correspond to the control setting are shown here. These values are decreased to the thousands format.
The four-row range K19:L22 provides data for five charts; row by row for the column charts and as a block for the pie chart.

The structure in the Lists 1 worksheet (see Figure 10-7) is even more interesting.

Figure 10-7 The list definition range for the ComboBox has two columns and contains formulas.

You have already seen that the opened control has two columns. The basis for this is a defined two-column ListFillRange with the name rL1.ProductsList.

Formulas of the type ="Rank"&TEXT(INDEX(rD1.Rank,1,G12),"00") generate a rank list by reading the relevant data from the aforementioned Data 1 and Basis sheet.

In this model, the ActiveX control in the Focus 1 sheet is especially interesting. Here, numerous changes that warrant a more detailed description were made to the default settings.

On the left-hand side (A) of Figure 10-8, you see an extract of the default settings for a ComboBox, grouped into categories. On the right-hand side (B), you see a comparative list of properties applied to the object used here. The rows whose settings differ from the default settings have been marked with small circles.

Figure 10-8 Numerous changes can be made using ActiveX controls.

In this case, the user has adjusted the following settings:

Formats
- BackColor: the object’s background color
- BorderColor: the border color
- BorderStyle: the border property (fmBorderStyleSingle: the object has a border)
- ForeColor: the font color
- SpecialEffect: the object’s appearance (fmSpecialEffectFlat: compared with the default setting, the object is not three-dimensional but flat and sets itself apart from its surroundings by nothing more than a border and/or color)
Data-specific properties
- BoundColumn: the setting 0 ensures that the control’s index value is put out to the LinkedCell.
- ColumnCount: the setting 2 assigns two columns to the drop-down list for this ComboBox. This requires a ListFillRange with two or more columns.
- ColumnWidths: the setting 60 pt;80 pt defines the width of both columns. A semicolon separates the values. The font size should be defined before the final setting is made here. Generally, after some trial and error, you will be happy with the appearance that you achieve.
- ListWidth: specifies the width of the expanded drop-down list. This value can also be a value other than the total value for ColumnWidths.
Font: Here, the Arial font was determined as the Font property. Generally, this font is considerably easier to see in such objects than are most other fonts.

I won’t describe the remaining properties here because, of these, only LinkedCell and ListFillRange are important developer settings. At this point, there is no need to say anything more about these settings.

I can therefore focus on providing some details about the design layout of the Focus 1 worksheet.

ON THE COMPANION CD

Several maps, including some for Europe, Ireland, and the United States, are stored on the CD_ROM under \Materials\Pictures\. The image used here is USA_CA_Map.tif.
The chart columns appear to rest on a podium that comprises a rectangle and the aforementioned image, and they have been connected to a group. With regard to the elements in this group, which are tilted backwards, examine the values for 3-D rotation, 3-D format (including material and lighting), line color (this concerns the border, for example), line style, and shadow, among others.

IMPORTANT

The rectangle and image are turned 320° along the Y axis (that is, vertically) and assigned a perspective of 45°. However, as a grouped graphical object it continues to require as much room as if it had not been rotated (as you can see from its markers). Therefore, the object can’t be moved up to its visible upper edge, which is the top of the screen, but only to its own hidden edge.
The pie chart is adjusted to the location and perspective of the graphical podium. The angle of the first slice (North) was set to 320°, so that the distribution and positioning of all 10 possible slices essentially correspond to the cardinal points of the compass (North, South, East, and West).
The pie chart has a slight pie explosion of 3 percent and the data label shows the category name and percentage. Of course, the colors here must correspond to the colors of the four column charts.

The column charts positioned in their regional locations are grouped objects that comprise a chart and a rectangle. In such images, objects of this type should never be used on their own to convey information. Because the columns are not standing directly next to each other, observers cannot clearly distinguish between the column heights. In this case, observers must rely on the data labels when making a comparison. The column heights must differ greatly if they are to be relied upon when making a comparison. Here, it is important to supplement the image with a pie chart, not only for visual reasons, but because it is a key design element that safeguards the information provided with the image.

When creating the column charts, I recommend the following:

Use the North data to create the first chart with a fixed scaling of the value axis.
Make all of the formatting settings and, when you are finished, make sure that you expand the plot area to the maximum chart area. Then, and only then, set the chart area size you require. When the chart area is the same size as the plot area, it is considerably easier to make any necessary exact adjustments to the width and height of the object at a later date. Of course, all four objects here must have the same dimensions, so that the same values result in the same column height.
Insert and format the Round Same Side Corner Rectangle which forms the base of the chart and position it carefully on the lower edge of the chart.
Connect both objects to a group and position these on the map.
Copy the object and place it elsewhere. Within the group object that you have copied, select the chart (to do this, you don’t need to dissolve the group), and assign the East data as the data source.
Position the new object and then select its column to change its color. Naturally, you can also do this as a final step for all four charts, which is better if you have to choose your colors by appearance and comparison.
Repeat steps 5 and 6 for the two remaining tasks.

With regard to the overall page design: when using rotated objects, you should pay particular attention to the accuracy of the perspectives and the proportional correlation of the objects shown. In this case, this applies not only to the map and pie chart, but also to the relationship between the map and the header bars. To illustrate this, Figure 10-9 has two lines to highlight the central perspective.