Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

migrated from serverfault.com Jul 13 '12 at 15:40

This question came from our site for system and network administrators.

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 '12 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 '12 at 14:44
    
suspect you've seen this but support.microsoft.com/kb/213904 –  user33788 Jul 13 '12 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 '12 at 15:18

2 Answers 2

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:

share|improve this answer
    
+1 really helpful answer –  datatoo Jul 13 '12 at 17:36

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.