Thursday, March 30, 2006
Common errors in Excel
Dick from Daily dose of Excel has written a post about a friend of his who is putting together a utility that will identify and possibly resolve a number of common problems with Excel spreadsheets. He has asked for input.
Here are some of the comments including mine.
- Calculation set to manual
- R1C1 style option selected
- Extraneous files in the XLSTART folder
- Circular references
- Array formulas entered as non-array
- SUM function on hidden cells instead of SUBTOTAL
- Precision as displayed
- Panes frozen and split line is below display area (can’t scroll)
- Why is this file so big? Blank cells beyond the end of the data that have been created and not deleted.
- Vlookup into a range that is mixed text and numeric returns #NA / lookup into a range of text that is padded at the end with blank characters returns #NA...
- Page break view vs. Normal view (usually in page break preview and they don’t know how to switch it)
- Formulas that are showing up as text (instead of calculating. We use and AS400 system and people pull info regularly. This is typical when they insert a column.)
- Mixed Zip codes - 5 digit vs 9 digit in the same column and not formating properly
- Check the size of the *.xlb file.
- Make sure ‘Always show full menus’ is selected.
- Check the Fixed decimal option
My comments
- I also see a lot of users “delete” the data in a cell by hitting space bar and Enter. So adding to the utility the ability to clear the contents of all cells containing only a space would be good
- When adding time - need to use the [h]:mm format to ensure that time that adds to more than 24 hours shows the total time in hours not in days and hours.
This isn’t a default date format in Excel though it can be found in the custom format list - but should be available as a time format - Edit directly in Cell - turning this off (default has it on) allows double click to go to cell precendents, and also makes it easier to edit large formulas. I turn it off on all my clients pcs.
- Turn of the reviewing toolbar. It persists in returning no matter how often I turn it off - anybody know how to stop that?
I don’t know anybody who uses this feature as it involves sharing workbooks and my forays inthat regard with office 2000 discovered lots of problems with attempting to make this work successfully. It also appeared to cause an inordinate number of “Excel has just crashed - hope you saved recently - tough luck if you didnt!” errors - The tool bar takes up valuable screen space - A show all errors utility is good too. I have built a routine for some users that searches their spreadsheets and builds a page showing the cell reference of all errors enabling them to go to the error. Probably should post it up on my website… one day when i have time!
- Locating Named ranges referring to #REF! or to external sheets.
A lot of users dont know how to break the inadvertent links created by copying formulas with name ranges in them to other work books and thus creating links that cannot be located using Find or the Edit Links (break links) menu option
If you have any additional pet whinges then either add them in the comments here or head over to Dicks site to add them.
The Excel Guru's Free Tips and Hints.
Welcome
Welcome to the Excel Tips blog. Free Microsoft Excel Tips and Hints designed to increase your productivity.
Advertisement
Subscribe Me
Previous Posts
- Creating Sheet Tabs in Excel with the Number of th...
- Disabled Items in Excel - AKA crashed files
- Dividing a First Left Heading Title
- Help Needed
- Constructing multi conditional array formulas in E...
- SUMPRODUCT array formulas in Excel
- Urgent Request
- Resetting the last cell in Excel
- Using theTranspose function in Excel
- Excel MVP and Professional Guru association ideas
My Blogs and Websites
- Spy Journal Home
- Spy Journal Archives
Personal Blog- Excel Tips Blog
- Blog Tips Blog
- Tech Tips Blog
- Urban Space Novel
- Photo Archive
- Parklife Results
- Parklife Soccer Club Website
- KROSTech LAN Parties
- Jethro Management
- Jethro Consultants
- Oz Bush Poet
Excel Links
- ---RSS Feeds---
- Unofficial Microsoft Office Stuff
- KC on Exchange and Outlook
- Office Zealot
- Automate Excel
- Colo's Excel Junk room
- Daily Dose Of Excel
- The Planning Deskbook
- Excel Pragma
- Andrew's Excel Tips
- ---No RSS Feed---
- Excel Tip
- Mailbarrow
- Experts Exchange
- AJP Excel Information
- Beyond Technology
- Contextures
- ExcelTip
- Mr Excel
- Pearson Software Consulting
- Peltier Technical Services
- John Walkenbach - J-Walk
- Vangelder (wont work in Firefox)
- VBA Express
- xlDynamic
- The Excel Nexus
- Code Net
- Better Solutions
- Excel Business Tools
- Data Pig Technologies
- VBA Express
Other Links
Copyright Notice. All documents and text contained in this web site www.spyjournal.biz and sub pages is copyright material of Jethro Management (c) 2004 unless noted as being copyright material of someone else. No public reproduction of the content on this site in any form is permitted without express written permission.
Unless otherwise expressly stated, all original material of whatever nature created by Tim Miller and included in this weblog and any related pages, including the weblog's archives, is licensed under a Creative Commons License.