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

  • 12/10/2008

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!

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

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

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

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

    Figure 10-20

    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.

  • 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.