Fix Chart Font Scaling.

Hate the way Excel mucks up your chart font sizes when you resize the chart? So do I, and here's how I counteract the default font scaling.

Fixing Font Autoscaling Manually

You can format individual chart elements one-by-one to romove the font autoscaling. When the Format dialog comes up, make sure the Auto Scale checkbox on the Font tab is unchecked. If you select the chart area and format the font, it will affect all font elements which are visible at the time, but font elements added later may have the autoscaling activated. If desired, you can reactivate font autoscaling by again checking the Auto Scale box.

Fixing Font Autoscaling with VBA

You can write VBA procedures to disable chart font autoscaling. This short procedure will turn off font autoscaling for the active chart:

Sub FixOneChartFont()
    ' Disable Font Autoscaling on Active Chart
    ActiveChart.ChartArea.AutoScaleFont = False
End Sub

You can reinstate font autoscaling by changing False to True. This short procedure will turn off font autoscaling for all charts on the active sheet:

Sub FixAllChartFonts()
    ' Disable Font Autoscaling on All Charts on Active Sheet
    Dim myCht As ChartObject
    For Each myCht In ActiveSheet.ChartObjects
        myCht.Chart.ChartArea.AutoScaleFont = False
    Next
End Sub

Changing the Default Font Autoscaling Behavior

Unfortunately, the manual and VBA procedures described above only work for existing charts. You can adjust the Windows registry to change the default autoscaling behavior. Here are the registry hacks that will permanently disable font autoscaling in each new chart you make:

Office 97

HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Excel\Microsoft Excel
Add a String Value
Value Name:  AutoChartFontScaling
Value Data:  "0"

Office 2000

HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options 
Add a New DWORD
Value Name:  AutoChartFontScaling
Value Data:  "0"

Office XP (2002)

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options 
Add a New DWORD
Value Name:  AutoChartFontScaling
Value Data:  "0"

Office 2003

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options 
Add a New DWORD
Value Name:  AutoChartFontScaling
Value Data:  "0"

Office 2007

One of the positive changes that Office 2007 brought about is that by default, font autoscaling in charts is turned off. If desired, you can turn it back on.

Note: Making changes to the Windows registry could lead to errors in the operation of your computer. Before making any changes, back up the registry. Make only one change at a time, then exit the registry editor and use the computer for a while to make sure you have not caused any unintended changes.

Microsoft Knowledge Base Articles Discussing Charts and Font Limitations

XL97: "Not Enough Memory" Error When You Add Chart to Workbook (Q168650)
When you attempt to add a new chart to a workbook or access the chart data source, you may receive the following error message:
Not enough memory.

XL97: How to Turn Off Automatic Font Scaling in All Charts (Q292263)
In Microsoft Excel 97, there is a limit of 256 fonts that can be used by the program. Charts that use the automatic scale feature have two fonts instead of one. This article explains how to programmatically turn the automatic scale feature...

XL97: Error Copying Worksheets Containing Charts (Q264986)
When you copy a worksheet with embedded chart objects several times, you may receive the following error message:
Not enough memory.

XL2000: Error Copying Worksheets Containing Charts (Q215573)
When you copy a worksheet with embedded chart objects several times, you may receive the following error message:
No more new fonts may be applied in this workbook.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2010. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile