0

We have several finance people who use absolutely massive, ancient Excel spreadsheets for various analysis purposes, and intermittently their financical operations are interrupted by a critical error that there are "Too many cell formats."

Is there a workaround for this, or do I tell them to suck it up and trim some fat?

4
  • 1
    Is there any particular reason why this information can't be placed in a centralized database and reports run for analysis purposes?
    – DKNUCKLES
    Jul 13, 2012 at 14:39
  • 4
    Dknuckles:I suspect that there is no technical reason why that would not work. Convincing finance people to change long existing, trusted (if poorly implemented) software for something they might not understand and which they probably have to learn how to change and maintain? If bumblejacket succeeds at that then I want lesson how to do that.
    – Hennes
    Jul 13, 2012 at 14:44
  • suspect you've seen this but support.microsoft.com/kb/213904
    – user33788
    Jul 13, 2012 at 14:56
  • In my experience, staff are usually open minded to changes if it means increased application performance as well as no critical errors resulting in a loss of work.
    – DKNUCKLES
    Jul 13, 2012 at 15:18

3 Answers 3

3

They're going to have to trim some fat first. You may need to invest time and manpower in making sure your spreadsheets are planned out, structured and built right. Whoever's in charge of your reports/spreadsheets, may need to drop a habit or two.

For starters:

  1. Don't forget about formula dependence when arranging cells.

  2. Don't excessively use volatile functions.

  3. Keep data sorted, if possible.

  4. Refrain from referencing entire rows or columns.

  5. In terms of formatting and layout, keep it simple, Snufflelupagus (K.I.S.S). Create a style guide and have your report analysts stick to it. I implemented a spreadsheet style guide and made everyone adhere to it in our company -- which resulted in smaller file sizes and faster report submission. For example, date headers should always be in format 'mm/dd/yy', bold and black.

  6. Update your formulas. If you're running Excel 2007 and above, there's no need to use:

    =IF(ISNA(VLOOKUP(100,MyRange,2,False)),"",VLOOKUP(100,MyRange,2,False))

    over

    =IFERROR(VLOOKUP(100,MyRange,2,False),"")

  7. Be careful when copy-pasting cells from one workbook to another. Custom styles get duplicated when formatted cells are copied.

  8. You don't always have to hide errors (see #6). They don't look great, but they stand out. In our company, visible errors help us pinpoint problem areas faster. This means you can do away with =IF(NOT(ISERROR()),A1,"") formulas.

  9. When doing lookups, use INDEX/MATCH instead of VLOOKUP iff the number of columns in your source data range is greater than 2. Or if you're doing a horizontal lookup, the number of rows. Besides, INDEX/MATCH formulas are more flexible.

  10. Keep the use of array formulas to a minimum, if possible. Use them on single cells or only if they reference a small range.


Further reading:

0
1

Often many of these files could be optimized.

Existing macros improved, un-necessary formatting fixed, remove unused custom views, remove unused defined names, external links.

If these users are not capable of understanding how to fix these things, productivity in the office might be worth a knowledgeable person intervening, and then showing how to make these types of improvements.

Even simply checking whether the saved version type is the best fit for what they do daily with the file can reduce file size.

0

The "Too many cell formats" problem is usually due to cells being copied pasted from other workbooks over time. An easy way to remove unused custom formats is to install the free FormulaDesk add-in, then click the 'Trim Bloated Workbook' button on the FormulaDesk menu. You can remove blank rows and columns, as well as the extra unnecessary cell formats. While you're about it you can also profile your workbook to determine why it's slow, using the profiler.

Disclaimer: FormulaDesk is my product

enter image description here

enter image description here

You must log in to answer this question.