This is an error that may pop up after you’ve pasted or formatted some cells. It is usually because you have used up the maximum number of different formatting combinations Excel can handle, which probably means you’ve spent a long time building a very complex workbook. So you might find yourself in a bit of a mess trying to resolve this, having put so much work into it.
If you can’t be flexible with any of your formats, there are add-ins you can install to help, but I’m going to explain how to resolve this by amending the formatting within your workbook.
You need to limit the number of different formats used across the workbook. The quick wins here are fonts (type, colour, size) and cell formats (borders and colours). There is a quick way to adjust the format of a number of cells to match another cell using the Format Painter tool.
In this basic example you have the word ‘Test’ in a number of different fonts and formats / patterns, but want the format in B4 to be copied across to the others:
Rather than having to copy and paste formats for each one, or worse still edit each cell one by one, you can use the Format Painter tool from the Home tab to simply brush the formatting across the relevant cells.
Firstly select the cell with the desired formatting (in this case B4) and either click on Format Painter or double-click if you’ll be making multiple selections. In this case we’ve double-clicked Format Painter and then selected the other cells with text to copy all the formatting across:
In addition to this, if you can limit the number of Conditional Formatting rules being used that will also help. If you’re highlighting bad results in two different shades of red in different places, just use one shade for both.
Do you know anymore tips to workaround this error? If so please let us know in the comments!