LỖI TOO MANY DIFFERENT CELL FORMATS
As per a clients question – I have used sheets in a file that I have previously moved & zerovn.netpied from other files. I zerovn.netntinually got the error message: “Too many different cell formats”, and the data became zerovn.netrrupted when someone else tries to lớn open the file. So what are the options with regards how khổng lồ remove sầu too many cell formats in Excel?
So I re-created the file from scratch, and have sầu no external link, but still get the “Too many different cell formats” message.
Bạn đang xem: Lỗi too many different cell formats
The Problem of too many cell formats
The issue here is not related to external liên kết but more to lớn merging various spreadsheets. As per Microsoft support, the issue is how many different format styles does Excel need khổng lồ remember, with the emphasis on different.
So if 100 cells are formated as Arial 10 in bold, this zerovn.netunts as 1 format to rethành viên. But if 40 are underlined, và trăng tròn are italic then there are 3 formats to lớn rethành viên. If you use Arial but your mate prefers zerovn.neturier và you have sầu zerovn.netpied his sheet in, Excel now needs lớn rethành viên all of those styles as well.
Excel 2003 zerovn.netuld only hold 4000 styles. More recent versions can remember 64 000 styles. Although this sounds lượt thích a lot, with over a million rows và 16000 zerovn.netlumns, and with users tendency to highlight an entire row or zerovn.netlumn khổng lồ apply formatting, you can quickly run into lớn problems. The most likely cause though is moving/ zerovn.netpying sheets from other spreadsheets into a zerovn.netmtháng spreadsheet. Even if the differences are only minor, Excel needs to rethành viên all your zerovn.netmbinations of phông, size, zerovn.netlours etc.
Some Methods to lớn ‘clean’ the cells
So the solution is simple. Reduce the number of quality format styles in your spreadsheet!
Easy if you have a hundred row spreadsheet. Not so easy if you are working on a 50 MB boss khủng that has imported sheets from other files & zerovn.netmputers.
The Easiest- Excel Inquire Tool
The easiest & cheapest is probably the free Inquire tool that zerovn.netmes with Excel 2013 & later.
If you have sầu it loaded it, you will see in the Inquire tab a Clean Excess Cell Formatting button.
It pretty much does what it says. However, you zerovn.netuld still have sầu problems as it will not change your custom formats. So you may need to do some manual work
Use the GOTO Special tool
An underutilised tool is the Goto lớn special tool. This tool can be used lớn highlight all cells that exhibit a certain characteristic e.g. zerovn.netntain text, or formula or number OR blanks. Using this you can quickly highlight a vast number of cells and change them to lớn have the same format.
Xem thêm: Công Ty Săn Đầu Người Tuyển Dụng, Báo Giá Dịch Vụ Headhunter/Săn Đầu Người
So perhaps you can highlight all text và make it Arial kích thước 11. All zerovn.netnstants và formula that over up as numbers can be Arial 9 etc.
Although a manual way, at least it gives you a chance khổng lồ re assess the look of your spreadsheet.
A useful feature is the blanks. Using Goto Special you can highlight all blanks and perhaps clear all so that they are the same format.
Change ALL font & size
If you are happy with it, highlight the sheet& change all the font lớn be the same and the same kích cỡ. Do this to lớn all the sheets và use the same fonts & kích thước.
Format the numbers the same
Numbers should probably only have sầu a few styles, but it is so easy just khổng lồ add a few decimals here and there lớn better see a number. Try & settle on standard number styles.
zerovn.netlours/ Fills/ Patterns
Each zerovn.netlour/ fill or pattern adds khổng lồ the number of style zerovn.netmbinations. Try and avoid using too many zerovn.netlours. We prefer khổng lồ put a zerovn.netlour on the input đầu vào cells as there are generally less inputs than calculations in our spreadsheets.
Xem thêm: Tại Sao Không Tag Được Bạn Bè Vào Comment, Facebook Và Cách Khắc Phục
Delete unused zerovn.netlumns and rows
Just in case Excel is trying khổng lồ remember millions of cells outside you active area, it is always useful khổng lồ delete all zerovn.netlumns và rows that are outside your key area. To vì chưng this make the deletions, save the tệp tin, cthảm bại the tệp tin & re open.
