Here are some very frequently asked questions in this newsgroup. It's not really in question/answer format, for quicker scanning.  I hope folks see this before they post, save themselves some time, and perhaps cut down on the newsgroup volume a little.

CALCULATION - DOESN'T STAY AUTOMATIC

Sometimes calculation (Tools - Options - Calculation)  is Automatic, and sometimes it's manual.  You get a lot of wrong answers when it doesn't calculate, eh?  Your boss has given away your parking space. 

This is because that setting in the first workbook that's opened determines it for all the workbooks.  Or any workbook that's opened when none was already opened.  No one knows why it's this way.  You can put a workbook in your "At startup, open all files in" folder (Tools - Options - General), whose calculation setting is Automatic.  This should set calculation to Automatic.  Do not close it if it's the last open file, lest you then open a workbook whose setting is Manual, and around you go again.  Excel  is expected to fix this in the year 2028. 

CHARTING ZEROS

A line chart drops to zero for a blank cell when it should just stop or skip the data point and go on to the next data point. 

FIX: Tools, Options, Charts, Plot empty cells as: "Not plotted (leave blank)" Jon Peltier is the resident charting guru: http://peltiertech.com/index.html

CHARTS -- LABELS NEEDED ON DATA POINTS

You need labels <at the data points> (data labels) in a line chart, column chart, xy chart, or most any kind of chart. 

Get Rob Bovey's XY Chart Labeler. www.appspro.com. It puts labels on a lot more than just xy charts.

EXCEL ACTS GENERALLY GOOFY

If you suspect something in Excel has broken, try these things:

Restart Excel.

Reboot your system.   

Start Excel with Start - Run - type: excel /regserver.  It will start, put in all factory default registry settings, then end. Now start Excel normally.

Clear the temp folder (It's in the Windows folder).

Tools - Options - General:  deselect "Ignore other applications."

DIRECTION KEYS MOVE THE SHEET instead of the cell pointer

FIX:  The Scroll lock (on the keyboard) is on.---

MACRO WARNINGS APPEAR WITH NO MACROS IN WORKBOOK.

Cause: Empty modules in a regular module in the workbook, or subs or functions in a sheet module.  Start the VB editor (Alt+F11). Open the project explorer if necessary (Toolbar button). Look for regular modules in your workbook (usually "Module1," etc.). Double click them to open the window. If there are Subs or Functions, you will lose them if you continue. Macros are subs or functions.  Close the module window (Ctrl-F4) and use the right mouse button to remove the module. When it prompts you to export it, answer "no."  If you still get the macro warning, in the Project Explorer, double-click each sheet object and the workbook object and look for macro code (subs or functions).

OPENS MANY FILES WHEN EXCEL STARTS

Look for these files in your XLStart folder.  Look in Program Files\Microsoft Office\Office10 (for Excel 2002).  The location may be different.  Also look in the folder specified in Tools - Options - General tab - "At startup, open all files in."

MERGED CELLS - ALL KINDS OF PROBLEMS

It's generally recommended you avoid using Merged cells.  They can give  you all kinds of problems, and when you least expect it.

NEWSGROUP POSTS -- ATTACHING FILES

Don't do it. The newsgroup responders hate it.  It is problematic for some users in the world, who pay for online time, and may not have fast connections. There are also concerns about malicious macros. Let responders who are interested ask you to send the file.

NEWSGROUP POSTS - EMAIL ADDRESS IN POSTS

It's generally recommended you don't use your email address in posts any more.  The spambots (spam robots) scour the web, pick them up, and sell them to low-lifes who send you all that spam.  You can "encode" your email address with something like:

bob removethisword at att dot com

 

Anyone who can't figure that out probably will waste your time anyway!  :)  And for heaven's sake, don't post someone else's email address.  Unless you really don't like them.

NEWSGROUP POSTS -- GENERAL POLITENESS AND EFFECTIVENESS

Answer the OP's (original poster's) question. Don't use the newsgroups to vent your spleen about other posters, Microsoft, Republicans, or anything else.  When you rant, you say more about yourself than the subject at hand.

 

See Chip Pearson's Hints for N\new Posters at http://www.cpearson.com/excel/newposte.htm

NUMBERS -- ONLY 15 DIGITS

You enter a number, but only get 15 digits.  The rest are dropped

Excel has only two data types for storing stuff in cells -- numbers, and text.  Now there are many ways to format the data (Format - Cells - Number), but only those two ways the contents are actually stored in the cell. For numbers, Excel uses a floating point type that's capable of 15 decimal digits (it's actually stored in binary -- the 15 decimal places is an equivalent).  It's the IEEE 754 standard. So you won't get the 16th digit.  Period.  15 decimal digits represents a LOT of precision for a value.  For more, see Chip Pearson's site, http://www.cpearson.com/excel/rounding.htm, or Microsoft's Knowledge base article http://support.microsoft.com/default.aspx?scid=kb;en-us;78113.

 

FIX: For "numbers" like credit card numbers, you'll need to store it in the cell as text.  Then you can have as many digits as you want.  Well, almost.  You can format the cell (Format - Cells - Number) as text.  Now you can enter the "number."  Note that if you've already entered it (and lost the 16th digit), this won't fix it, as the digit is already lost.  You must have formatted the cell as text, and then entered the number. 

 

Or, you can precede the number with an apostrophe (').  That makes it text.  But you must do that for each number you enter.

 

With either solution, you can't use this "number" in any kind of numeric calculations -- it's text, and numerically, it's too big.

OPENING FILES -- CAN'T DO IT IN EXPLORER OR A FOLDER

When you double-click a file in a folder, it starts Excel, but doesn't open the file.  Works OK when using File - Open in Excel. 

FIX:  In Tools - Options - General, uncheck "Ignore other applications."

ROWS AND COLUMNS -- INTERCHANGE

You want to interchange the rows with the columns in a table. 

Copy the table, find an empty place where the transposed table will fit, and select the upper left cell of the space.  Edit - Paste special - Transpose.  You can't paste/transpose directly over the original cells.

SELECTION - YOU GET A RANGE SELECTED AS IF YOU'RE DRAGGING

You click a cell, and it selects everything from the prior selected cell to the new one. 

FIX:  Look for "EXT" in the status bar (bottom of Excel window). Toggle it off with F8.

SHEET TABS  -- CHANGING COLOR

Pre-Excel 2002:  You can't.

TAB KEY BEHAVIOR

The Tab key moves the cell pointer a whole screen's-worth, instead of one cell to the right. 

FIX:  Tools - Options - Transition.  Uncheck "Transition Navigation Keys.  This is a euphemism for "Act like Lotus 123."

TEXT IN A CELL -- HOW MUCH?

With large amounts of text, not all of it displays.

A cell can hold 32K characters, but only about 1000 display in a cell, unless you've interspersed linefeeds (Alt-Enter) in the text.

TEXT FILE -- SAVING WORKSHEET AS -- DATA GETS CHANGED

You save a file as csv or txt, and reopen it in Excel  Leading zeroes have been dropped, cells that weren't dates become dates. 

FIX:  This may not be the save -- it may be the automatic formatting when you use Excel to reopen the text file.  Use NotePad to open the file.  It will show you the characters in the text file, without any formatting changes.  See "Text files and Excel" in this site.

WINDOWS -- DUPLICATE

You see two or more document windows inside Excel, labeled something like "Book1.xls:1" and "Book1.xls:2". 

FIX:  Close one of the document windows (the smaller "x" button in the upper right corner).  Re-save the file in this state.  This was caused by using Window - New Window).

Home