Too many different cell formats

Many times we will encounter the following error messages in our excel files:

Too many different cell formats
No more new fonts may be applied in this workbook

Since this is a common question that I get, let me document down the causes and prevention method.

Causes:
To help users manage file size, Microsoft Excel allows only a total of 4,000 different cell formats and 512 different fonts.

Note that for Excel, different cell formats include different Font Size, Font Colour, Cell Shading, Bold, Italics, Underline, etc.

The following are the 2 most common causes of your workbook exceeding the allowed number of cell formats.

    1. Copying many different worksheet from different other workbooks that contain different formatting
    2. Allowing “auto scale” in Graphs and Charts
      This will cause problem when you have many charts, which you have resized, resulting in different font sizes in each chart’s title, axis and value formats
      If you add charts manually or if you copy and paste existing charts, you can reach the font limitation for a workbook.

Solution:
For the above problems, the solutions are as follows:

    1. Apply standard fonts to all your worksheets
      Using the same font for all cells reduces the number of formatting combinations
    2. Disable “auto scale” in your Graphs and Charts
      This will fix the font sizes in your charts to prevent accidental creation of additional fonts.
      As I understand that most of you will have already created many different charts in your workbook, a macro script is available here for you to automatically disable the “auto scale” function in all existing charts that are in open workbooks.

Prevention:
You can take the following proactive steps to prevent this problem from recurring:

    1. Using “Format > Styles” to format your cells
      This will ensure consistent application of standard formats across your workbook.
      Many of you should be already familiar with using the “Comma” and “Percent” styles
    2. Disable “auto scale” whenever you insert or manually create a Graph or Chart.
      This will prevent problems in the future when you copy the chart

I hope this note will shed light on this problem that many of you face and allow you to take action to prevent it from recurring.

Advertisements

Tagged: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: