My friend Harald is putting together a utility that identifies common problems. The idea is that the utility would identify these easy-to-fix, hard-to-spot errors and eliminate the need for a support call. On the list so far are
- 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
What are some other common problems that you experience or are asked to fix?
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
Macro Security level set to “medium” rather than “high”
I agree with Jim regarding the spaces at the end or beginning of text
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 celar 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
“Make sure ‘Always show full menus’ is selected.”
Also, show Standard and Formatting toolbars on two rows, so all the buttons are visible.
In the VBE: Require variable declaration
Another common error: numbers stored as text.
Fixed decimal set to 2 places
Ignore Other Applications – ticked
Display a list of installed Add-Ins
Sheet Protection Status
I wanted to also mention displayed Page Breaks.
When these are showing, I find they slow everything down.
I have a helper macro for turning them off:
ActiveSheet.DisplayPageBreaks = False
Too many Names or Too many Small Lines~
so scroll gets slow
What are some other common problems that you experience?
Numbers stored as text
Formulas with only numbers ( like “= 234+567?)
Mixed formulas and values
Circular references
Vlookups (Hlookups) expecting an ordered list
Vlookups (Hlookups) expecting absolute addressing
Pivot tables without updating
Auto calculation turns off
…and some users ;)
It would be great to check that the Date 1904 is turned off.
To add to all the other items:
Duplicate global/local defined names
VBE library reference not set or to the wrong library.
A simpler one…
User switches on auto-filter but row immediately below is totally blank so nothing appears in drop-down lists.
1. “I hit the tab key and the cursor moves several columns”…Tools > Options > Transition tab and uncheck transition navigation keys.
2. “I can’t see my file”… try Windows > Unhide
3. “The leading zero disappears when I enter a number such as US zip code”…Preformat the cell as text or precede the number with a single quote (‘)
4. “I tells me I already have the file open”…see Debra’s site:
http://www.contextures.com/xlfaqApp.html#AlreadyOpen
Analysis ToolPak is not installed
High Contrast is turned on in Windows Display properties
Sheets have been grouped
– confused by multiple files after using New window
– show placeholders ticked on Tools > Options > View > Objects
You know better than I what a pain “MERGED CELLS” can cause…
To add to the rapid growing list:
Make sure that Option Explicit is activated in the VB-editor.
Kind regards,
Dennis
How exactly does one tell whether formulas should be entered as array formulas without building a very sophisticated formula parser? Just using multiple cell ranges as arithmetic arguments isn’t necessarily a sure sign that formulas need to be array formulas. There are many things that don’t require array entry in SUMPRODUCT and LOOKUP, and some things that do even using those functions. Or would this cover only the functions that always return multiple entry arrays, e.g., FREQUENCY, LINEST and LOGEST?
And to add to the list, the other Options, Transition tab entries:
-transition formula entry
-transition formula evaluation
Just for emphasis I would have to second the Analysis Toolpack that Debra mentioned. To add on to Dennis’s post Error Trapping is probably better set to Break in Class Module…
Non-standard color palette.
a) Dont paste-special on a filtered list.
b) Dont allow deletion(or any other operation) on >8192 discontinuous cells selected
Regards
Sam
Did anyone mention: never use workbook sharing?
Thanks everybody. Lots of “d’oh, why didn’t I think of that”. I do now.
This is unfortunately work, as in Work, so I’m not allowed to share the result on the internet. But this is an easy thing to build, feel free to use the concept.
Best wishes Harald
John,
Make sure ‘Always show full menus’ is selected.
My kingdom for this option’s location (disclosure: very small kingdom)! I use the keyboard almost exclusively and commands not in the custom short menus won’t respond to mnemonic key strokes. Drives me nuts!!
Thanks,
Brett
Always show full menues
View…Menues…customize…
Thanks,
Brett
Number Precision = 15: first encountered after users had entered hundreds of 16-digit credit card numbers and found that the last digit had been changed to 0 on all of them. Not fun.
Dianne Butterworth: “Number Precision = 15: first encountered after users had entered hundreds of 16-digit credit card numbers and found that the last digit had been changed to 0 on all of them.”
Wrong data type: a credit card number is fixed-width text!
But I take you point and would add: all numerics = double precision floating point (the only fixed point type is CURRENCY, with scale limited to four).
Response 29 should read View…Toolbars…Customize…Options tab of Customize dialogue box. Sorry about that.
Brett
When user entered a date: 5/29/07 it was automatically changed into a formula. The resulting number, 0.024630542,
when date formatted, ended up with a date format of Jan00. The problem was that the Transition Formula Entry option was set in the Tools, Options, Transition tab. Unchecking this option fixed the problem. The setting was designed to transition Lotus users into Excel, however in this case the file was never originally Lotus. Not sure how the option was set.
Hi all, first comment here for me. Wonderful blog Dick and friends!
Harald, here’s an advanced one and perhaps not that useful, but still. What about checking whether variables have a good variable type. For instance, I just saw on a forum:
Dim dtoh1, dtoh2, dtoh3, dtoh4, dtoh5, dtoh6, dtoh7, dtoh8, dtoh9, dtoh10 As Date
Dim dt1, dt2, dt3, dt4, dt5, dt6, dt7, dt8, dt9, dt10 As Date
Don’t know if this is desirable / feasible.
Cheers
Wim Gielis
>> Dim dtoh1, dtoh2, dtoh3, dtoh4, dtoh5, dtoh6, dtoh7, dtoh8, dtoh9, dtoh10 As Date
I was under the impression that doesn’t work as one would expect. Each comma separates a full definition, so if you want them all to be type Date, you’d need to code it as:
Dim dtoh1 As Date, dtoh2 As Date, dtoh3 As Date, dtoh4 As Date, dtoh5 As Date,
Dim dtoh6 As Date, dtoh7 As Date, dtoh8 As Date, dtoh9 As Date, dtoh10 As Date
The original coding would define dtoh1 thru dtoh9 as type Variant
I am trying to write a simple macro to turn off auto-calculation on hitting the macro button. I recorded hitting Tools/Options/ Calculation and hitting the Manual calc radio button, but the resultant causes a runtime error [1004].
Any ideas? Macro code:
With Application
.Calculation = xlManual ‘(THIS IS THE LINE THAT FAILED)
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Thanks
Tigerp: If you’re using xl97 and an ActiveX control, change the TakeFocusOnClick property to False.