Excel Resources

[ Excel KB Articles ] [ Macros ] [ Tips ] [ Startup Problems ] [ FAQ ]

If this reference material helps you significantly, consider a donation--$1, $5 or $10 would be greatly appreciated! Thanks.

In supporting Excel for Microsoft, I've had a chance to talk with hundreds of Excel customers. I did support from January 2002 to July 2003 and I have seen more issues than an average user will in a lifetime. Note that my focus is on repairing Excel and some of you are more adept at chart design, layout and financial theory. So don't expect me to know everything. My job is to be able to find solutions though research, experimentation and colaboration.

You'll find many common issues and questions answered here. Since the lay offs and outsourcing in July of 2003, I've taken a different career path and this site is now fairly static. Nonetheless, there is valuable resource material here. Keep in mind that the troubleshooting techniques were for Excel XP (2002) and Microsoft may have overcome these issues in the newer versions of the product. -Mark Henri, July 2006

Tip of the Month

August 2003
Retail Rounding-How to make everything end with .09 cents:
Assuming the amount to start with is in A1
=TRUNC(A1,1)+0.09
July 2003
Get Excel support from www.keen.com. Keen is a site for independent consultants. You simply go on-line, choose your personal technician and then phone him or her. You are billed by the minute. A typical Excel issue takes around 7-10 minutes. The highest rate I've seen was 1.70 a minute so your support call should run around 15.00. Not bad!
June 2003
It's possible to extract data from a database directly into Excel accross the internet. It's surprisingly easy if your web server supporta ASP. I have scripts that allow extracting data based on an SQL statement. Contact me for details.
May 2003
Error 1919 during installation is difficult to fix. Here's what you need to do to resolve it--Fixing Error 1919 During Installation
1. Download and install MDAC 2.6
2. Go to this page and choose your OS and the name of your Windows directory and run the two REG files for you system.
3. Attempt to install the product again
April 2003
Mailing labels in Excel? Well, Excel doesn't support the creation of mailing labels. However, Word can take an Excel spreadsheet as a data source for it's mail merge functionality. Just make sure that your column names are in row 1 and the data starts in row 2.
March 2003
To determine if there are duplicates in a list--
1. Enter 0,8,2,3,4,5,6,7,8,9 in cells A1:A10
2. In cell B1 enter
=IF(SUM(IF($A$1:$A$10=$A1,1,0))>1,"Y","")
and do not press enter yet. 3. Hold down the control + shift and then press enter one time. The formula will look like this-- {=IF(SUM(IF($A$1:$A$10=$A1,1,0))>1,"Y","")}
4. Copy the formula down to cell B10
A Y will now appear wherever there is a duplicate.
February 2003
Would you like a default worksheet (as opposed to a workbook) to appear when you insert a new sheet? It's easy but requires some specific steps to accomplish--
1. Open a blank workbook
2. Delete all sheets except for one
3. Setup the sheet with the desired formats, printer settings, options, etc.
4. Click on 'File, Save As', select template from 'Files of Type'
5. Change the name to 'sheet' (without the extent; it will add it automatically)
6. Change the save in location to c:\program files\microsoft office\office\xlstart or one of the other startup (XLSTART) folders used by Microsoft Excel.
7. Click save.
January 2003
Back up your spreadsheets regularly. Don't work from a floppy disk or across a network connection; it's very risky. If the connection fails, the spreadsheet will corrupt instantly.
December 2002
Spend some time with your families and stop doing spreadsheets for a little while.
November 2002
Install the shareware version of WinZip, you can get it at www.winzip.com. It adds menu items to Windows Explorer for fast and easy zipping and emailing.
October 2002
Though calling Microsoft for support is a good idea, those wishing to save some money should first go to www.mrexcel.com and register with the on-line forum. In it, you can enter your question and you'll find others who are willing to help. Remember, if all else fails, Microsoft Support is there.
September 2002
Check out this example of how to use Excel as a presentation layer for your web site
August 2002
Here's how to create and use a macro in Excel. It's written in Visual Basic and stored with the workbook. You could also add it to a personal collection of macros. Want more? Well, I'm starting my own macro collection focusing on getting specific tasks done. Also, check out the ones from Julian's Excel Solutions.

Free Knowledge
Microsoft provides an excellent resource on line called the knowledge base (KB) and their articles can help with many of the issues you'll face with Excel. Here's a link to the most common KB articles.

Here's a cool link to another Excel site, this one deals with slow response/save/open issues-
click here

Insider Information
If you are adventurous and don't mind a candid discussion of the typical customer issues that I face every day, read my insider's page. Also, here's the Excel standard troubleshooting steps that we use.

Examples
Here's some example spreadsheets--

Amortization Table (w/o Macros)
Computing Futures
Macro Function In A Cell
Import From A Text File
TextImportCSVData.txt - right click on this link and save to the same location as the spreadsheet above.
Timer Stuff
Change a Cell Name to the URL behind it
VLOOKUP Example
Custom Reports With VSView
this requires the use of the VSView ActiveX control.
Nickel Rounding Example
Custom Functions Using Macros
Generate HTML then FTP to Web
Recipe Calculator
Slope Chart

Downloads
mdac 2.6
Error 1919

Send comments or concerns to