Read the Manual
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?
Toad:
Precision as displayed
Panes frozen and split line is below display area (can’t scroll)
29 March 2006, 2:48 pmJim Thomlinson:
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…
29 March 2006, 3:37 pmStacie Penney:
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
29 March 2006, 4:17 pmJohn Walkenbach:
Check the size of the *.xlb file.
Make sure ‘Always show full menus’ is selected.
Check the Fixed decimal option
29 March 2006, 4:50 pmJack Sinclair:
Macro Security level set to “medium” rather than “high”
29 March 2006, 5:16 pmTim:
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.
29 March 2006, 5:23 pmA 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
Jon Peltier:
“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.
29 March 2006, 9:07 pmron:
Fixed decimal set to 2 places
29 March 2006, 9:19 pmRob van Gelder:
Ignore Other Applications - ticked
29 March 2006, 10:09 pmDisplay a list of installed Add-Ins
Sheet Protection Status
Rob van Gelder:
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
29 March 2006, 10:11 pmUHeHeHe:
Too many Names or Too many Small Lines~
29 March 2006, 10:44 pmso scroll gets slow
Jiri Cihar:
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
30 March 2006, 12:51 amBoomer:
It would be great to check that the Date 1904 is turned off.
30 March 2006, 3:31 amjkpieterse:
To add to all the other items:
Duplicate global/local defined names
30 March 2006, 3:33 amRembo:
VBE library reference not set or to the wrong library.
30 March 2006, 5:50 amGareth:
A simpler one…
User switches on auto-filter but row immediately below is totally blank so nothing appears in drop-down lists.
30 March 2006, 6:53 amJason Morin:
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:
www.contextures.com/xlfaqApp.html#AlreadyOpen
30 March 2006, 7:18 amDebra Dalgleish:
–Analysis ToolPak is not installed
30 March 2006, 8:17 am–High Contrast is turned on in Windows Display properties
–Sheets have been grouped
Andy Pope:
- confused by multiple files after using New window
30 March 2006, 8:34 am- show placeholders ticked on Tools > Options > View > Objects
Sige:
You know better than I what a pain “MERGED CELLS” can cause…
30 March 2006, 8:35 amXL-Dennis:
To add to the rapid growing list:
Make sure that Option Explicit is activated in the VB-editor.
Kind regards,
30 March 2006, 8:53 amDennis
fzz:
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:
30 March 2006, 3:35 pm-transition formula entry
-transition formula evaluation
Jim Thomlinson:
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…
30 March 2006, 3:41 pmGraham:
Non-standard color palette.
30 March 2006, 3:48 pmSam:
a) Dont paste-special on a filtered list.
b) Dont allow deletion(or any other operation) on >8192 discontinuous cells selected
Regards
30 March 2006, 11:59 pmSam
Alex J:
Did anyone mention: never use workbook sharing?
31 March 2006, 10:32 amHarald Staff:
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
31 March 2006, 12:04 pmBrett:
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,
31 March 2006, 1:52 pmBrett
Brett:
Always show full menues
View…Menues…customize…
Thanks,
31 March 2006, 1:58 pmBrett
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. Not fun.
6 April 2006, 1:06 pmJamie Collins:
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).
7 April 2006, 2:13 amBrett:
Response 29 should read View…Toolbars…Customize…Options tab of Customize dialogue box. Sorry about that.
Brett
21 June 2006, 5:43 amWendy:
When user entered a date: 5/29/07 it was automatically changed into a formula. The resulting number, 0.024630542,
29 May 2007, 11:34 amwhen 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.
Wim Gielis:
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
4 June 2007, 11:39 amRandy Harmelink:
>> 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
5 June 2007, 4:59 pmTigerp:
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].
20 May 2008, 7:41 amAny ideas? Macro code:
With Application
.Calculation = xlManual ‘(THIS IS THE LINE THAT FAILED)
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Thanks
Dick Kusleika:
Tigerp: If you’re using xl97 and an ActiveX control, change the TakeFocusOnClick property to False.
21 May 2008, 10:53 am