Too Many Different Cell Formats Error – Excel 2007/Excel 2010

This error can arise when you go to paste data at the end of a list or in a large spreadsheet where some of the following conditions apply:

  • the workbook was originally a 97 – 2003 xls format workbook
  • covers a large area or has a long list of data
  • cell formatting has been used to provide borders and fill over a large area

The solution is to:

  • remove the formatting
  • where the data is in the form of list replace the formatting by using Excel 2007/2010 Table formatting

Select all cells in one sheet (CTRL A) then clear the formatting using the steps in this technet article . Repeat for all sheets in all affected work books.

You can now copy and paste without the error message

To replace the removed formatting – assuming data is in the form of a list:

  • On each sheet
  • Click within the data
  • Inset Tab, Select Table
  • Ensure the entire list is selected
  • Click OK
  • This will give alternate row shading in the default colours
  • Appearance can be changed in the Table Tools style gallery
  • As additional data is added the formatting will extend to cover the new data