Excel KB articles

These are articles from the Microsoft Knowledge Base, familiarly known as the KB. Standards-compliant Web browsers like Mozilla Firefox and Opera, which work great everywhere else, seem to have problems with Microsoft's pages. If you have difficulties, you might want to use IE to view or print these.

To be fair, in their blog for IE7 beta since mid-2005, Microsoft people have been saying that their Web resources and Windows Vista will respect user choices of browsers other than IE. They may never get all the IE-specific code in the KB fixed though: it's a big database. As of second quarter 2006 I still see KB articles that don't lay out properly in Firefox.

Miscellaneous

214094 How to use dates and times in Excel http://support.microsoft.com/kb/214094/: Explains how dates and times are stored internally, and how to do date and time calculations, with examples, including some advanced stuff.
214134 How to calculate the number of months between two dates http://support.microsoft.com/kb/214134/: Two ways
214313 How to calculate the number of days in a month http://support.microsoft.com/kb/214313/
214240 Glossary of charting terminology http://support.microsoft.com/kb/214240/: A guide to Excel jargon for chart types and the different formatting elements of charts.
213447 Visual Basic module to create a Gantt chart http://support.microsoft.com/kb/213447/: Interesting to see that Excel's charting system can be hacked to make Gantt charts from spreadsheet input data, a type of chart used in project management software to show connected tasks and critical path.
264372 Control and understand settings in the Format Cells dialog box http://support.microsoft.com/kb/264372/: You can control how information in cells is displayed, including how numbers and other data are presented, alignment, fonts, borders, and text and background colors and patterns. Some of these formats are available from handy toolbar buttons, but the Format Cells dialog box from the menus is like the master controls. Custom number formats are also covered here; if the exact number format you need isn't provided with Excel, you can create it.
213904 Error: "Too many different cell formats" http://support.microsoft.com/kb/213904/: On the other hand, don't get carried away with cell formats. Excel has a fixed-size buffer that only provides for about 4,000 different cell format combinations in any one file. If you want your work to look professional, you need to limit your font and other format choices rather severely anyway.
280504 Troubleshoot startup problems in Excel http://support.microsoft.com/kb/280504/: Occasionally you may find that other MS Office programs such as Word and PowerPoint run fine, but you get errors or a lockup when you try to start Excel. This is usually due to one or more corrupted startup files, files Excel reads when it launches normally. The good news is, there's only so many things that can cause this problem, and Excel has an "application safe mode" /s command-line switch to diagnose it.*
189126 Microsoft policy regarding missing or incorrect passwords http://support.microsoft.com/kb/189126/: "Microsoft support engineers cannot help you retrieve passwords that have been lost." Their tech support people have no way of knowing for sure who is calling them, so they have this published policy that says they can't help at all with lost or forgotten passwords.
288118 Add-ins not included with Excel 2002 & 2003 http://support.microsoft.com/kb/288118/: List of add-ins present in earlier versions that were dropped from Excel 2002, some available for download and some not. I think some of these were dropped because they tended to cause more problems than they resolved.

top

Database

Conditional consolidation

Damaged file recovery

Following are the three primary reference KB articles on recovery of damaged workbook files. Some of the techniques cited in these three articles are more useful than others. See also my basic Excel tips page for more general discussion.

820741 How to recover data from damaged workbooks in Excel 2002 & 2003 http://support.microsoft.com/kb/820741/: Covers the new recovery tools in 2002+ including AutoRecover and Open and Repair.
179871 Methods for recovering data from damaged Excel 2000 workbooks http://support.microsoft.com/kb/179871: Excel 2000 was the first version able to save a workbook file to Web-page/HTML format. Reopening the new HTML file in Excel and saving a new XLS workbook file based on it is a recovery technique that's effective fairly often (see below). When it works it recovers data and formulas.
142117 XL: Summary of methods to recover data from corrupted files http://support.microsoft.com/kb/142117/: Applies to Excel 97 and earlier.

Specific recommendations

There are two basic scenarios for damaged workbook files. Here are some things to try:

Scenario 1: you can open the file, but it misbehaves in some manner after it's open.

Do Save As and save the problem file to a new filename in normal XLS workbook format. Do File Close. Do File Open and open the new file. Test if the problem is resolved.
If the data you need to recover is an Excel list, see if you can save the list data to CSV text format. Then import the CSV text file into a new Excel workbook file. If this works you will lose your formatting and any formulas but recover the list data.
If you're working with Excel 2000 or earlier, and you have access to Excel 2002 or later, try opening and saving the problem file in the later version. If that doesn't resolve the problem, try Open and Repair in Excel 2002 or later.
In Excel 2000 and later: save the file to HTML (Web page) format, do File Close, open the HTML file in Excel, do Save As and save to a new filename in XLS format, do File Close, open the new XLS file.
Third-party workbook repair utilities.

Scenario 2: you can't open the file at all; trying produces an error message or hangs Excel or Windows.

Try opening the problem file with Excel recalculation disabled: Tools, Options, Calculation tab, Manual. This rarely works but is quick and easy to try. (Don't forget to set recalculation back to Automatic after you try opening the file.*)
Try external references recovery per KB article 214253 "How to link to data in a file that is damaged" (also linked below). If it works it will recover all or part of the data that would normally display or print, but not your formulas.
Try opening the file in one of the free viewers for Excel 2000 and Excel 2003 (see linked articles below). The free viewers are occasionally able to open damaged workbook files that Excel itself can't open. If so, you'll be able to copy your data and paste it into a new workbook in Excel, but your formulas will not be recovered.
Third-party workbook repair utilities.

To find third-party Excel workbook file repair utilities, try a Google search on excel file repair.

Some other relevant KB articles:

214253 How to link to data in a damaged file http://support.microsoft.com/kb/214253/: Describes a particular recovery technique for workbook files that won't open directly. This technique uses external link formulas in a new workbook file to pull data, but not the original formulas, out of the damaged workbook file.
289273 Description of the AutoRecover functions in Excel 2002 and Excel 2003 http://support.microsoft.com/kb/289273/: A more detailed description of how AutoRecover works.
149235 How to help prevent file corruption in Excel http://support.microsoft.com/kb/149235/: Some general suggestions on things to do to prevent workbook file corruption from being a problem.
273711 Description of the Excel 2000 Viewer http://support.microsoft.com/kb/273711/: There's also a free viewer for Excel 2003 with no KB article; search at microsoft.com for excel viewer. The free viewers for Word, Excel, and PowerPoint were created primarily to allow people without MS Office to view and print, but not modify, Office document files sent to them. A better option now available in this situation is the free open-source OpenOffice.org suite, which can view, print, and modify MS Office document files; see my OpenOffice.org pages in this section.
214395 How Excel determines the current mode of calculation http://support.microsoft.com/kb/214395/: Nearly all users will want to leave Excel recalculation set to Automatic at all times, except for the long-shot recovery technique described above.* If you ever find that formulas don't recalculate after you change something, or if you see the indicator "Calculate" in your Excel status bar, check Tools, Options, Calculation tab, for each open file. If you find it set to Manual, change it back to Automatic and save the file.

top

Rounding issues

Mail merge

See also my basic Excel tips page for more general discussion of Office mail merges.

290408 Frequently asked questions about mail merge in Word 2002 http://support.microsoft.com/kb/290408/
294683 How to use mail merge to create form letters in Word 2002 http://support.microsoft.com/kb/294683/
318118 How to create a form letter by using information from an Excel 2002 worksheet in Word 2002 http://support.microsoft.com/kb/318118/
212329 How to use mail merge to create a form letter in Word 2000 http://support.microsoft.com/kb/212329/
141922 WD97: How to start a mail merge http://support.microsoft.com/kb/141922/
294684 How to use mail merge to create mailing labels in Word 2002 http://support.microsoft.com/kb/294684/
318117 How to use addresses from an Excel 2002 worksheet to create labels in Word 2002 http://support.microsoft.com/kb/318117/
212034 How to create mailing labels by using Mail Merge in Word (2000) http://support.microsoft.com/kb/212034/
195609 WD97: How to create mailing labels using the mail merge feature http://support.microsoft.com/kb/195609/
294685 How to use mail merge to create envelopes in Word 2002 http://support.microsoft.com/kb/294685/
212017 How to use mail merge to create envelopes in Word 2000 http://support.microsoft.com/kb/212017/
195989 WD97: How to create envelopes using mail merge http://support.microsoft.com/kb/195989/

top

Disappearing merge fields

Sometimes you will have a field that needs to appear as part of a line in a form letter or mailing label, such as a title or honorific, which is sometimes filled in and sometimes blank in the merge source, and you'll want it to disappear without a trace from the letter or label when that field is blank in the source.

How to insert a Word mail-merge field that disappears when the source field is blank:

In your merge document (the form letter or label format) press Alt-F9 to display field codes.
Select (highlight) the merge field you want to behave this way and do Insert Field.
Select category Mail Merge and field name If, and click OK to insert an IF field.
Delete everything after "IF" inside the new field.
With the insertion point still inside the IF field brackets and after "IF", click the Insert Merge Field button on the Merge toolbar. Insert a merge field for the field name you're tweaking.
After the merge field ending bracket (but inside the IF field ending bracket) type space, equals, space, quote, quote, space, quote, quote, space. (These are double-quote characters, the keyboard shift character.)
With the insertion point still after that last space, click the Insert Merge Field button on the toolbar again and insert another merge field for the field name you're tweaking.
Enclose this second merge field in double quotes (outside its field brackets) and put a space between the closing bracket and the closing quote. Make sure there isn't a space after the IF field, between it and the next merge field.

Here's an example of how your IF field should look when you're done:

{IF {MERGEFIELD Honorific} = "" "" "{MERGEFIELD Honorific} "}

Data format issues

Sometimes the results of the merge fields in Word are not as expected, typically with dates, phone numbers, currency, zip codes, or maybe just a desired numeric format.

320473 Excel data does not retain its formatting in mail merge http://support.microsoft.com/kb/320473/
304387 Date, phone, currency fields merged incorrectly with Access or Excel data source http://support.microsoft.com/kb/304387/
284718 How to format five-digit and nine-digit postal codes in Word 2002 http://support.microsoft.com/kb/284718/

top

Supported file formats

Numbers stored as text

"Two-minute call" Excel issues

I'd suggest you read these short articles and go try the setting or issue they refer to. Then you'll never have to pay a credit card charge for an embarrassing two-minute tech-support call about them. This is the sort of thing that makes Homer Simpson say "d'Oh!"

269866 Columns and rows are labeled numerically http://support.microsoft.com/kb/269866/: Tools, Options, General, R1C1 reference style.
305567 Decimal point or trailing zeros appear http://support.microsoft.com/kb/305567/: Tools, Options, Edit, Fixed Decimal; a setting sometimes useful for massive data entry.
305568 Error: cannot shift nonblank cells off the worksheet http://support.microsoft.com/kb/305568/: Why you probably shouldn't apply cell formats to entire rows or columns.
307812 Troubleshooting navigation issues http://support.microsoft.com/kb/307812/: That darn Scroll Lock key ... also F8, wheelie mice, funky keyboards, and other fun stuff.

Believe it or not, people with keyboards full of some combination of cat hair, cigarette ashes, food crumbs, and gummy residue of various spilled beverages, will still call software tech support when they poke a key and don't get the expected result. Fortunately, ordinary keyboards are relatively cheap, even new ones, and you can often find perfectly good used keyboards at thrift stores or your local computer recycler for less than $5. You can even use a PS/2 keyboard and mouse with a computer with USB ports but no PS/2 ports, or vice versa; there are inexpensive adapters available.

Some early spreadsheet history

Program	Sponsor	1st year	Platforms	Remarks
VisiCalc	Software Arts	1979	Apple II	Dan Bricklin and Bob Frankston.
SuperCalc	Sorcim	1980	Osborne running CP/M	Bundled with WordStar. (Sorcim = "micros" backwards)
MultiPlan	Microsoft	1982	CP/M, DOS, C64, Apple II	R1C1 addressing, later seen as an option in Excel.
Lotus 1-2-3	Lotus Software	1983	MS-DOS, Windows	DOS/PC killer app, very fast; still available.
As Easy As	Trius	1984?	MS-DOS	Shareware to compete with 1-2-3 at about a tenth the cost.
Excel	Microsoft	1985	Mac, Windows (1987)	Graphical formatting; dethroned Lotus.
Quattro	Borland	1988	MS-DOS	Graphics on DOS.
Quattro Pro	Borland, Novell, Corel	1989	MS-DOS, Windows	Never got much traction against Excel, but still available.

top

I entered the spreadsheet world with Lotus 1-2-3 version 2 on DOS, at a time when that version had been out for a while. I quickly became so adept with it I hardly needed to look at the menus across the top two lines of the screen, or much of anything else; the keystrokes were burned into my brain. I could do things like set up a one-table spreadsheet database for someone, including macros, at jaw-dropping speed, too fast for someone looking over my shoulder to follow what I was doing. From that experience I learned one should never immerse oneself in any application to that level, because in a few months or a year you'll be using something else. That, and not to do things so fast that you scare the Muggles.

site feedback