Navigating in uncharted waters of Microsoft Excel charts

Translating the sample chart

4. Translating category or value labels

Most probably, you will never have to translate labels along the X or Y axis. Even if such labels are used, they are mostly simple numbers. If you do come across textual axis labels, follow these guidelines to translate category / value labels in different versions of Microsoft Excel:

• Excel 2003 and earlier:

  Select Chart -> Source Data... and click the Series tab. Check the field labelled 'Category (X) axis labels' at the bottom of the dialogue:

  • If the formula in this field refers to a cell range (e.g. =Sheet1!$A$1:$A$10), find the range and translate it;
  • If the formula is an array formula (e.g. ={"Jan", "Feb", "Mar" ,"Apr"}), translate it, replacing the each piece of text between double quotes with translation.

  

• Excel 2007 and later:

  Right-click the chart and choose Select Data from the dropdown menu, or click Select Data button in the Data group on the Design tab. In the Select Data Source dialogue, click Edit button on the right side of the dialogue to show Axis Labels dialogue.

  

  Check the formula in this dialogue:

  • If the formula in this field refers to a cell range (e.g. =Sheet1!$A$1:$A$10), find the range and translate it;
  • If the formula is an array formula (e.g. ={"Jan", "Feb", "Mar" ,"Apr"}), translate it, replacing each piece of text between double quotes with translation.

Formatting the chart

So, you have translated all the text on the chart. The final step is to format the chart so that your translation looks perfect.

To format the chart, you can change the size of the Plot Area, Legend, or Chart Area, or change the font size of some chart components like chart title, axis titles, etc. To select them, click them with the mouse, or select them from the drop-down list on the Chart toolbar [Excel 2003 and earlier] or from the drop-down list in the Current Selection group on Layout and Format tabs [Excel 2007 and later].

Here are some typical problems you may come across:

1. The legend will be too small to fit your translation. In this case, resize the legend. If there is not enough space for the resized legend, resize the plot area.
2. Chart title will be too big and it will overlap the plot area. In this case, just resize the plot area. If necessary, first resize the chart area.

Tip: make sure that you save the Excel spreadsheet before formatting the chart. In most cases, Excel will be able to undo only the most recent action.

Navigation complete

Congratulations on successful completion of this tutorial. I hope you can now translate Excel charts with confidence. You can also apply your knowledge in translation of charts prepared in OpenOffice Calc application.

To reinforce theory with practice, please use our sample spreadsheet which can be found in the Links & Resources section below.