You receive an error message when you add a chart to a workbook in Excel

When you add a chart to a workbook in Microsoft Office Excel, you may receive the following error message:When you click OK to clear the error message, you may receive the following error message:

This problem occurs because of the Auto scale setting. When you add a chart to the workbook, the Auto scale setting is enabled by default. This setting causes charts to use two or more fonts instead of one. When you add multiple charts to a workbook with this setting enabled, the font limitation for a workbook may be reached. For Microsoft Excel 2000 and later, the maximum number of fonts is 512. If you add charts manually or if you copy and paste existing charts, you can reach the font limitation for a workbook. The following is an example of copying existing charts:

• You create a chart object in the worksheet.
• You copy and paste the chart object on the same worksheet ten or more times.
• You then copy the worksheet several times in the same workbook.

A registry key is available to prevent automatic font scaling for new charts. To fix existing charts, use one of the methods listed in the "Disable automatic font scaling in existing charts" section.

Prevent automatic font scaling for new charts

You can disable automatic font scaling for all new charts to prevent the workbook from exceeding the maximum number of allowable fonts through setting a registry key.
To do disable the Auto scale setting, perform the following steps:Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
NOTE: Because there are several versions of Microsoft Windows, the following steps may be different on your computer. If they are, see your product documentation to complete these steps.

1. Quit all running programs.
2. Click the Start button, and then click Run.
3. In the Open box, type regedit , and then click OK.
4. Go to the following registry subkey (folder), depending on your version of Excel:

   Excel 2003

   HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options
   

   Excel 2002

   HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options
   

   Excel 2000

   HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options
   
5. Point to New on the Edit menu, and then click DWORD Value. Type AutoChartFontScaling , and then press ENTER.
6. Click Modify on the Edit menu. Type 0 in the Value Data box, and then click OK.
7. Click Exit on the File menu to quit the Registry Editor.

After adding this registry key, all new charts created in Excel will not have the Auto scale setting selected.

Disable automatic font scaling in existing charts

Use one of the following methods to disable automatic font scaling in your existing charts.

Method 1: Disable Autoscale in each Chart

1. Select a chart.
2. On the Format menu, click Selected Chart Area.
3. Click the Font tab.
4. Click to clear the Autoscale check box.
5. Click OK.
6. Repeat these steps for each chart in your workbook.

Method 2: Programmatically disable Auto scale on all existing charts in the workbook

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To programmatically disable Auto scale on all existing charts in a workbook, please do the following steps:

1. Start Excel and open the file that is causing the error message.
2. In the Tools menu, point to Macro, and then click Visual Basic Editor (or press ALT+F11).
3. In the Visual Basic Editor, select Module from the Insert menu.
4. Type or paste the following code in the module:

   		Sub AutoScale_Off()
   		    Dim ws As Worksheet, co As ChartObject, i As Integer
   		    Dim ch As Chart
   		    For Each ws In ActiveWorkbook.Worksheets
   		
   		    ' Go through each worksheet in the workbook
   		
   		        For Each co In ws.ChartObjects
   		
   		        'In each chart turn the Auto Scale font feature off
   		  
   		            i = i + 1
   		            co.Chart.ChartArea.AutoScaleFont = False
   		        Next co
   		    Next ws
   		
   		    For Each ch In ActiveWorkbook.Charts
   		
   		    'Go through each chart in the workbook
   		
   		        ch.ChartArea.AutoScaleFont = False
   		        i = i + 1
   		    Next
   		    MsgBox i & " charts have been altered"
   		    Application.DisplayAlerts = True
   		End Sub

5. Click anywhere in the code of function AutoScale_Off, and then click Run Sub/UserForm on the Run menu. After the macro runs, you should be able to insert a chart without error.