Navigating in uncharted waters of Microsoft Excel charts

Translating the sample chart

3. Translating the legend

In many situations, you will only need to edit the data range of the chart to translate the legend and category names (axis labels).

First, determine the chart's data range:

• In Excel 2003 and earlier:

  Select Chart -> Source Data... and check the Data Range parameter on the Data Range tab.

  

• In Excel 2007 and later:

  Right-click the chart and choose Select Data from the dropdown menu. You will see 'Select Data Source' dialogue. Alternatively, click Select Data button in the Data group on the Design tab.

  

If the Data Range field contains a formula (e.g. =Sheet1!$A$1:$C$10), you can locate the range and check whether it includes the series names displayed in the chart legend. If this is so, you can edit the text, and the legend will be magically translated. See 'A word on formulas' above for more information on formulas.

If the Data Range field is empty (Excel 2003 or earlier) , or if the indicated range does not contain series names, you will have to translate series name the hard way. Follow the guidelines below to do this:

• Excel 2003 and earlier:

  There are two ways to edit a series name:

  1. Select Chart -> Source Data... and click the Series tab. Each series will be shown in the Series list. Select the appropriate series and check the Name field.

     

     • If the field indicates a cell address, it is best to find the cell and translate its value.
     • If the field displays something like ="New York temp.", you will need to modify it. The best way to do this is select all the text between double quotes and then type the translation over it. Be careful when pressing Left or Right keys because this inserts the address of the current cell.

     See 'A word on formulas' for more information on working with formulas.

  2. Select the series with the mouse or from the drop-down list on the Chart toolbar. You will see a formula shown in the formula bar, e.g.:
     
     =SERIES("New York temp.";Sheet1!$A$38:$A$41;Sheet1!$D$38:$D$41;3)

     If the first parameter of the formula is not a cell address, you will need to edit the text between double quotes (shown in dark-red above), and click the green tick () on the left to update the legend. To cancel your action, click the red cross () on the left or press Cancel.

     Note: the word SERIES in the formula may be localized in non-English versions of Microsoft Excel. The symbol that separates parameters in the formula (e.g. ";") may also be different.

  Once you do this, the appropriate legend entry will be translated.

• Excel 2007 and later:

  There are two ways to edit a series name:

  1. 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, select each individual series in the list on the left side, click Edit, and then check the Series Name field in the Edit Series dialogue.

     

     • If the field indicates a cell address, it is best to find the cell and translate its value.
     • If the field displays something like ="New York temp.", you will need to modify it. The best way to do this is select all the text between double quotes and then type the translation over it. Be careful when pressing Left or Right keys because this inserts the address of the current cell.

     See 'A word on formulas' for more information on working with formulas.

  2. Select the series with the mouse or from the dropdown list in the Current Selection group on Layout and Format tabs. You will see a formula shown in the formula bar, e.g.:
     
     =SERIES("New York temp.";Sheet1!$A$38:$A$41;Sheet1!$D$38:$D$41;3)

     If the first parameter of the formula is not a cell address, you will need to edit the text between double quotes (shown in dark-red above), and click the green tick () on the left to update the legend. To cancel your action, click the red cross () on the left or press Cancel.

     Note: the word SERIES in the formula may be localized in non-English versions of Microsoft Excel. The symbol that separates parameters in the formula (e.g. ";") may also be different.

  Once you do this, the appropriate legend entry will be translated.