Navigating in uncharted waters of Microsoft Excel charts

Quick Navigation

• What charts are made of
• Working with Excel charts
• A word on formulas
• Translating the sample chart
  1. Translating a chart title
  2. Translating axis titles
  3. Translating the legend
  4. Translating category or value labels
• Formatting the chart
• Chart navigation complete
• Links & resources

Translating Excel spreadsheets is pretty easy. All you need to do is translate text in individual cells and then format the spreadsheet for the target language user. However, sometimes your spreadsheet will contain charts, making translation more complicated. This article will help you understand and master charts so you can easily translate even the most advanced varieties.

Figure 1: A sample Excel chart

What charts are made of

Excel charts are quite easy if you understand how they are built. Let us look at the components of our sample chart shown in Figure 1 above.

1. Every chart is built on the basis of some data set, usually taken from a range of cells in the same Excel spreadsheet. Here is the data that was used to create our sample chart (shown above):

   	New York	Tokyo	Moscow	London
   January	8	12	-10	1
   February	13	13	-7	9
   March	17	15	0	18
   April	20	19	10	22

   When Excel builds a chart, it analyzes values and plots them on the chart depending on the type of chart. The most commonly used types include line charts (our sample chart is an example), bar charts (including column charts) and pie charts.

   
   Figure 2: Examples of pie and column charts

   The chart's data set is always located outside the chart in a range on the same or different sheet, but sometimes its copy appears inside the chart for readability.

2. Most Excel charts have two axes – a category axis (usually this is the X axis) and the value axis (usu. the Y axis). On those axes, Excel plots the relationship between two parameters. In our example, Excel shows month names along the X axis and the corresponding temperatures along the Y axis. Labels appearing along the category axis are called category names (category labels) .

   Each axis may have a primary axis title appearing near the axis, and (rarely) a secondary axis title shown on the other side. They are appropriately called Horizontal (Category) Axis Title and Vertical (Value) Axis Title.

3. Each chart shows several values or groups of values in a visual, easy-to-read format. For example, in a pie chart each value is represented by a pie slice, in a column (bar) chart – by bars of the same color, and in a line chart – by a zigzagging or straight line. These values (for a pie chart) or groups of values (for a line or bar chart) are called series. Each series is assigned a unique color to make the chart even easier to understand. Depending on the chart's settings, a series may correspond to a single column or row in the data set. In addition, each series has a specific name which is usually taken from the first cell of a column (or row) in the data set. In our example, you can see 4 series names called 'New York', 'Tokyo', 'Moscow' and 'London'. Series names are also called legend entries because they also appear in the legend if a legend is displayed.
4. A legend is frequently used to provide meaning to a color assigned to a series. It contains names of each series and indicates the color by which you can find a series on the chart. The legend may be positioned on any of the four sides of a chart.
5. At the top or bottom of a chart you usually see a chart title.

Figure 3: Components of a typical Excel chart

From Excel perspective, there are two more chart components which should be mentioned:

• Plot Area is the area where the data set is displayed. In other words, the Plot Area does not include axis titles, chart title, legend. For line charts, the Plot Area contains X and Y axes and the area between them; for pie charts, it is the actual pie, etc.
• Chart Area is the entire area occupied by the chart. It contains the Plot Area, legend, chart title, etc.

Working with Excel charts

Depending on the location in an Excel spreadsheet, charts are subdivided into chart objects and chart sheets. A chart object is a chart which is placed on an existing sheet along with other data. A chart sheet is a standalone sheet occupied by the chart only, with nothing else in it.

When you select the Chart Area (by clicking empty space near chart's edge), or an individual series, Excel automatically outlines the respective cells with different colors (see Figure 3 above).

Different versions of Microsoft Excel provide different user interfaces for working with charts.

• Excel 2007 and later:

  • Click anywhere on the chart. You will see a new group of tabs called 'Chart Tools' appear at the top of the ribbon, with three tabs underneath: Design, Layout and Format.
  • To modify chart components (e.g. resize, move, change text, format), you can click them with the mouse or select them from the drop-down list in the Current Selection group on Layout and Format tabs.
  • You can change the size of the Plot Area, Legend, or Chart Area to make the translation fit.
  • To edit Chart Title, Category Axis Title, Value Axis Title, and secondary axis titles (if used), select them with the mouse, right-click and select Edit Text menu item (you can also left-click inside the title).
  • To edit all the data that goes into the chart, right-click the chart and choose Select Data from the dropdown menu. You will see 'Select Data Source' dialogue. An alternative way is to click Select Data button in the Data group on the Design tab.
  • To edit series names in the chart legend, 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 edit the Series Name field in the Edit Series dialogue.
  • Another way to edit series names is to select the series with the mouse or from the dropdown list in the Current Selection group on Layout and Format tabs, and then edit the formula in the formula bar. See the next section for more detail.
  • To edit labels shown along the category axis (if shown), 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. Use the field on this dialogue to see where the data is taken from or modify the formula (if it is an array formula).

• Excel 2003 and earlier:

  • Click anywhere on the chart. A new menu item, Chart, will appear on the menu bar, and a toolbar called 'Chart' will become visible. You can use these controls to get easy access to various chart components.
  • To modify chart components (e.g. resize, move, change text, format), you can click them with the mouse or select them from the drop-down list on the Chart toolbar.
  • You can change the size of the Plot Area, Legend, or Chart Area to make the translation fit.
  • The quickest way to edit Chart Title, Category Axis Title, Value Axis Title, and secondary axis titles (if used) is to select Chart -> Chart Options... and click the Titles tab where you can modify each title.
  • To edit all the data that goes into the chart, select Chart -> Source Data... and check the Data Range parameter on the Data Range tab.
  • To edit series names used to make the chart legend, 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.
  • Another way to edit series names is to select the series with the mouse or from the drop-down list on the Chart toolbar, and edit the formula in the formula bar. See the next section for more detail.
  • To edit labels shown along the category axis (if shown), select Chart -> Source Data... and click the Series tab. Use the field labelled 'Category (X) axis labels' at the bottom of the dialogue to see where the data is taken from or modify the formula (if it is an array formula).

Translating the sample chart

Now that you know the components of an Excel chart and how to manipulate them with your version of Excel, let's translate our sample chart. Off we go!

Our chart has the following translatable elements:

1. Chart title
2. Axis titles
3. Legend
4. Category or value names plotted along X or Y axes.

Figure 4: Translatable components

1. Translating a chart title

Perhaps the easiest way to translate the chart title is to click it with the mouse and edit the text. There are other ways depending on your version of Microsoft Excel:

• Excel 2007 and later:

  To select the chart title accurately, select 'Chart Title' from the drop-down list in the Current Selection group on Layout and Format tabs.

• Excel 2003 and earlier:

  To select the chart title accurately, select 'Chart Title' from the drop-down list on the Chart toolbar.

  To edit the chart title quickly, select Chart -> Chart Options... and click the Titles tab. This will allow you to edit all axis titles as well.

Sometimes you will also need to resize the chart title because the translation will not show as intended. Excel will not allow you to resize the text box, but you can change font properties to accomplish the task, or resize the whole chart.

2. Translating axis titles

As explained above, there are usually 2 axis titles on a chart, but sometimes there may be secondary axis titles appearing on the right or top of the chart.

Just like with a chart title, you can edit axis titles by clicking them with the mouse and editing the text. There are other ways depending on your version of Microsoft Excel:

• Excel 2007 and later:

  To select an axis title accurately, select 'Horizontal (Category) Axis Title', 'Vertical (Value) Axis Title', 'Secondary Horizontal (Category) Axis Title', or 'Secondary Vertical (Value) Axis Title' from the drop-down list in the Current Selection group on Layout and Format tabs. You can also use the mouse, of course.

• Excel 2003 and earlier:

  To select an axis title accurately, select 'Category Axis Title', 'Value Axis Title', 'Secondary Category Axis Title', or 'Secondary Value Axis Title' from the drop-down list on the Chart toolbar.

  To edit axis titles quickly, select Chart -> Chart Options... and click the Titles tab. Edit individual titles on the dialogue. You can also edit the chart title from this dialogue.

  

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

  

• In Excel 2003 and earlier:

  Select Chart -> Source Data... and check the Data Range parameter on the Data Range 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 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.

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

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

• 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 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 in the Current Selection group on Layout and Format tabs [Excel 2007 and later] or from the drop-down list on the Chart toolbar [Excel 2003 and earlier].

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.

Links & resources