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

Not That It’s Absolutely Essential

I’ll change the subject now in order to discuss those finishing touches that make all the difference. Some seemingly minor things have become more important rather than less important in this age of so called globalization.

The scenario presented in the solution I’ll describe is based on a real-life model: a German company has production facilities and sales offices in England, Ireland, the Netherlands, France, and Italy. The company’s working language is English. Some employees take this completely for granted (the English and Irish, though perhaps with different attitudes), others see some benefit (the Germans), others are indifferent as long as the working language is not German (the Dutch), and the rest are not comfortable with this—and if it was only for historical reasons (the French and Italians).

Management considers itself to be European and uses this identity to pursue worldwide relationships. Those responsible at national level receive periodic business reports as Excel files. These reports are “multilingual,” meaning that each recipient can display and print the report in his own national language, with a simple mouse click. However, he can also display and print the report in the other national languages. This is where the multilingual principle carries a powerful message: “We are one, but we should treat everyone equally, and with due respect. We respect your national pride as well as any national quirks (should there be some). Even though English is our working language, it does not have to be.” Well done!

ON THE COMPANION CD

Open the file \Samples\1005_Multilingual.xlsx on the CD-ROM.

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

In Figure 10-18 you can see that something really “mitico” is happening here, something good and dynamic! You can use a ListBox to determine which of the five languages should be used to label the chart. The sales report shown here concerns sales data from German cities. It is highly commendable that the company also respects that these cities may have different names in other languages, even if they don’t wish to use those names. Such hiccups are still going strong: “Mailand”—scherzi? “Lione”—fâcheux! “Londres?”—you must be kidding! Therefore, such labels are stored as variables here.

Figure 10-18 “Ma guarda te! Mitico!”

Never underestimate the entertainment value of presentation charts. Time and time again, I have witnessed the audience’s acceptance of a solution whose information value may be boring or whose information content may even be problematic suddenly skyrocket when the presentation format shows something appealing.

A word of warning: do not introduce nonsensical gimmicks or special effects to paper over the cracks in the presentation. Out of courtesy, you could introduce something appealing that respects the needs of your target group; for example, the use of company colors and designs. Alternatively, you could introduce an additional unexpected feature, something that’s “clever” and “looks good,” something to be proud of.

Your solution will be accepted if it fulfills its brief. It will excel if it is also pleasing to the eye. If someone smiles, points to the screen, and says something along the lines of “look at that . . .” to his neighbor during your presentation, this is wonderful news for you. It means that you have accomplished something great; you have made someone happy.

In the example shown here, colleagues from France, Italy, and the Netherlands who were not particularly happy about English being the working language in a predominantly German company were “won over” by a setting that enabled them to view the report modules in their own national languages, with a mouse click. This was exactly the intention: the colleagues did not simply receive their own “national solution,” but instead received an international solution that they had the option of localizing. The difference is small, but it counts. I’m sure you can think of some other similar scenarios.

Not that it is absolutely essential but it can still give great pleasure.

NOTE

The character sets and settings options available in Widows and Office set very few boundaries in terms of the languages you use. You can assign almost any language combination to models such as the one presented here. This also includes “foreign fonts;” in other words, non-Latin fonts from the Asian or Arabic-speaking world, for example. This is particularly important if globalization is truly your goal.

It is quite easy to set up such a model:

• The Parameters 1 worksheet, which is fully used here again, contains all of the label elements to be used in the relevant language versions (see Figure 10-19). Depending on the current control setting, they are transferred to the Data 1 and Basis sheet (see Figure 10-20) and shown in the chart.

  

  Figure 10-19 Here you can define as many languages as you want.

  

  Figure 10-20 Something a little different: a two-row axis label from one text list.

• The data source for the chart is the range K11:Q23 in the Data 1 and Basis sheet. The unusual category axis label for the chart can be interpreted from Figure 10-20. It is easy to create this type of “offset” two-row structure, which is always an option if the text is longer than the width of the chart column, if it cannot or must not be wrapped, and if it should remain horizontal for the sake of legibility. The result is a little different—thus it is effective.

  NOTE

  

  The cells that appear to be empty in the range K12:L23 must contain blank characters. Otherwise, the entries in the second row will shift in the chart’s axis label.

• The chart heading is a text field linked with the cell rD1.Heading.

• This map of Europe, in which the nations represented within the company have been highlighted, is available on the CD-ROM as the file \Materials\Pictures\Europe03.tif.