Answers to Frequently Asked Questions about Microsoft Excel

The Excel Application and Excel Files

  1. Starting Excel and Opening Files
    1. When I start Excel, why do a million files open up automatically?
    2. When I start XL, I get the error message "Compile error in Hidden Module"
    3. All of a sudden a number of my Excel 2000 files have become "read-only".
    4. I have an Excel file that began opening two copies of itself .
    5. A message says a file with that name is already open.
    6. Why does Excel say my file has links, when I know it doesn't?
    7. Excel freezes / crashes / caused an invalid page fault / Illegal Operation Error
    8. Excel crashes on opening / closing / prompts "not enough resources".
  2. Worksheet Appearance
    1. Why do the column headers show numbers instead of letters?
    2. How can I change the color of the sheet tabs?
    3. How do I lock in my Title Row and keep it visible while I scroll?
    4. Why do my scrollbars go to row 500 -- my data ends in cell E50? 
    5. When I apply Fill colour to a cell, I can't see it on the worksheet 
    6. Fill pattern in my chart disappears when I print the worksheet.
  3. Excel Limits and Specifications
    1. I need more than 256 columns and/or 65536 Rows.
    2. How many worksheets I can put in a workbook?
    3. How many characters can be placed in a cell?
  4. Passwords and Protection
    1. How can I protect formulas from being deleted or changed?
    2. I've lost my password!
    3. How can I protect an Excel file from unauthorized copying?
    4. Is there a way to allow the use of AutoFilter on a protected worksheet?
  5. Entering and Saving Data
    1. When I type a number, example 44, it becomes 0.44
    2. When I clear a large range , it takes forever. How can I make it faster?
    3. How can I prevent hyperlinks from appearing when I type an email address?
    4. My 16 digit credit card information does not display properly.
    5. Excel closed-- I did not save my file. Is there anything I can do to get it back

1. Starting Excel and Opening Files

When I start Excel, why do a million files open up automatically?
    1. Choose Tools > Options and select the General tab.
    2. All files listed in the "At startup, open all files in:" box will be loaded when Excel starts.
    3. Alter or remove this list.

When I start XL, I get the error message "Compile error in Hidden Module"

An add-in with a programming error is causing problems.

  1. Choose Tools > Add-ins
  2. Note which are checked
  3. Uncheck all (but one)
  4. Restart XL
  5. If no error, check the next one and repeat from step 4

Got the error? Uncheck the one you last checked. If no luck, see if there's an .xla file in the startup directory (see previous tip). Scanner software typically place itself there, sometimes with code errors like the one in question.

Not an add-in? It will be a hidden workbook then.

  1. Try (in Excel) Windows, Unhide and write down which files are listed.
  2. Unhide them.
  3. Now go to the VBE (press Alt + F11).
  4. On the left side there should be the project explorer.
  5. Select the first project you unhid
  6. From the menu choose Debug, compile
  7. No Errors? Select the next project listed, repeat 6.

All of a sudden a number of my Excel 2000 files have become "read-only".

Clear out c:\windows\temp directory on the machine that houses the files, then reboot.

I have an Excel file that I use every day at work. Some time ago, mysteriously, the file began opening two copies of itself every time I double-click the icon. If I close one of the copies, both close. Any changes made to one copy show up in the other.

This sounds like you just have two windows open that are displaying the same workbook.

Select the window that you want to close, and press Ctrl + w

OR, you can close the extra window by clicking the lower of the two X buttons in the upper right corner of the screen (don't click the top X button, as that will close Excel). Then save the workbook.

  

When I click on an Excel file to open it, the file opens but an error message is displayed that says a file with that name is already open and that I can not open two files open with the same name.

If you are sure it's not really happening (you may have Book1 open and then attempt to open a Book1 from another folder), then try re-registering Excel.

  1. On the Windows taskbar, click the Start button
  2. Choose Run
  3. Type "C:\Program Files\Microsoft Office\Office\Excel.Exe" /regserver (include the quotes) -- adapt to fit your path.

Other things to check:

  1. In Excel, choose Tools>Options.
  2. On the General tab, make sure "Ignore Other Applications" is not checked

Why does Excel say my file has links, when I know it doesn't?

Links come in several flavors; linked formulas, defined names (Insert > Name > Define menu), objects (buttons and stuff) assigned to remote macros, ... You might find the FINDLINK.XLA program useful - you can get it from Stephen Bullen's web site: http://www.oaltd.co.uk/

* When I try to open XL, it freezes and all I can do is reboot
       * Excel crashes on me regularly, what can I do
       * EXCEL caused an invalid page fault...
       * Illegal Operation Error when starting Excel

To-Do List:
Try opening Excel without any addins or hidden workbooks:

  1. On the Windows taskbar, click the Start button
  2. Choose Run
  3. Type "C:\Program Files\Microsoft Office\Office\Excel.Exe" /Automation (include the quotes) -- adapt to fit your path.

If your error disappears, there is either an add-in or a (hidden) workbook that is loaded upon XL's start, which is causing problems.

  • Try locating the XLSTART directory, move everything from there.
  • In XL: Tools, Addins, note the ones checked and uncheck them one at the time, each time restarting XL
  • In XL, Tools, options, general. Check if a path is entered after "Alternate startup file location". Clear it.

Another option is to open XL in Safe mode:
"C:\Program Files\Microsoft Office\Office\Excel.exe" /Safe

Also, you might try:
"C:\Program Files\Microsoft Office\Office\Excel.exe" /Regserver

Another possible problem is a corruption of your toolbar customisation file:

  • Locate all files with extension .xlb
  • Rename the extension(s) to something like .old
  • Now try and start XL again.

Finally, this is what MS has to say:
XL: How to Troubleshoot Startup Problems in Microsoft Excel

Excel crashes on opening / closing / prompts "not enough resources".

This could really be anything. Considering that, it's remarkably often a corrupt toolbar file. So worth a try the following:
1. Close Excel (if it managed to open).
2. Reboot if necessary.
3. Locate all files (with Start > Search etc) with extension .xlb
4. Rename the extension(s) to something like .old
5. Start XL again.

2. Worksheet Appearance

Why do the column headers show numbers instead of letters? How do I change my column headings so they are back to letters instead of numbers?

  1. Choose Tools > Options and select the General tab.
  2. Remove the check mark from 'R1C1 reference style'

How can I change the color of the sheet tabs?

In Excel 2002, and later versions, you can colour the sheet tabs. Right-click on a sheet tab, and choose Tab Color...

Note: When the sheet is selected, the colour will show in a thin strip at the bottom of the sheet tab. The full tab is coloured for non-selected sheets.

How do I lock in my Title Row and keep it visible while I scroll down to see the rest of my data?

Assuming title row is 1, select cell A2, then choose Window > Freeze Panes.

Why do my scrollbars go to row 500 -- my data ends in cell E50?

Excel may be remembering some data that was in row 500, but has been deleted. To go to the cell which is currently the "Last Cell" in the worksheet, hold the Ctrl key, and press the End key. If the Last Cell (cell J500 in this example) is outside the range of cells that is actually being used, you can reset the used range, so the scroll bar works correctly.

Note: If any cells contain references to the deleted cells, those references will be replaced with a #REF! error. If you have cells that are formatted, but outside the range that contains data, the formatting will be lost.

To manually reset the used range:

    1. Select the last cell that contains data in the worksheet
    2. To delete any unused rows:
      • Move down one row from the last cell with data.
      • Hold the Ctrl and Shift keys, and press the Down Arrow key
      • Right-click in the selected cells, and, from the shortcut menu, choose Delete
      • Select Entire Row, click OK.
    3. To delete any unused columns:
      • Move right one column from the last cell with data.
      • Hold the Ctrl and Shift keys, and press the Right Arrow key
      • Right-click in the selected cells, and, from the shortcut menu, choose Delete
      • Select Entire Column, click OK.
    4. Save the file. Note: In older versions of Excel, you may have to Save, then close and re-open the file before the used range is reset.

 

 

 

 

To programatically reset the used range,

Note: This code may not work correctly if the worksheet contains merged cells. To check your worksheet, you can run the TestForMergedCells code. 

Sub DeleteUnused()
  

Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range


For Each wks In ActiveWorkbook.Worksheets
  With wks
    myLastRow = 0
    myLastCol = 0
    Set dummyRng = .UsedRange
    On Error Resume Next
    myLastRow = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, lookat:=xlWhole, _
        searchdirection:=xlPrevious, _
        searchorder:=xlByRows).Row
    myLastCol = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, lookat:=xlWhole, _
        searchdirection:=xlPrevious, _
        searchorder:=xlByColumns).Column
    On Error GoTo 0

    If myLastRow * myLastCol = 0 Then
        .Columns.Delete
    Else
        .Range(.Cells(myLastRow + 1, 1), _
          .Cells(.Rows.Count, 1)).EntireRow.Delete
        .Range(.Cells(1, myLastCol + 1), _
          .Cells(1, .Columns.Count)).EntireColumn.Delete
    End If
  End With
Next wks

End Sub

'================================
Sub TestForMergedCells()

  Dim AnyMerged As Variant

  AnyMerged = ActiveSheet.UsedRange.MergeCells

  If AnyMerged = False Then
      MsgBox "no merged"
  ElseIf AnyMerged = True Then
      MsgBox "all merged"
  ElseIf IsNull(AnyMerged) Then
      MsgBox "mixture"
  Else
      MsgBox "never gets here--only 3 options"
  End If

End Sub
'=====================================

When I apply Fill colour to a cell, I can't see it on the worksheet. However, the colour shows up in Print Preview, or when I print the worksheet. What's wrong?

If the high contrast setting is turned on you won't see the fill colour. There's information in the following MSKB article:
The fill color, the fill pattern, or the line color of a WordArt or AutoShape object in an Office document does not change
   http://support.microsoft.com/?id=833258

When I apply Fill pattern to a chart, it looks okay on the worksheet. However, the pattern disappears when I print the worksheet. What's wrong?

Fill patterns were designed for older printers with low resolution. Today's printers may print the patterns in such fine detail, and so small, that the pattern sometimes disappears. If you really, need to print patterns, you could roll your own, using white rectangles, with transparent border, and lines, etc.

Create a square that represents one repeat of the pattern. For example, if you want closely spaced vertical lines, draw a 1" white square with 6 vertical 1" black lines. Use the alignment and spacing commands to finish the pattern. Group the objects in each pattern, copy, and paste into the data series. Then format the series to have a black border. If necessary, set the Picture format (in Fill Effects) to Stack. It doesn't look too good on the screen, but prints beautifully

 

3. Excel Limits and Specifications

I need more than 256 columns and/or 65536 Rows.

In Excel 2003, and earlier versions, that's the limit. Excel 2007 has 1,048,576 rows and 16,384 columns.

How many worksheets I can put in a workbook?

It is not a limit per se, but of course there is a practical one which depends on the computer resources.

How many characters can be placed in a cell?

In Excel 97 and above, you can have up to 32K characters in a worksheet cell, but only the first 1000 or so are displayed (the exact number depends on your font and display characteristics). However, you can increase the number of characters displayed if you add line breaks in the cell (press Alt+Enter).

4. Passwords and Protection

How can I protect formulas from being deleted or changed?

  1. Select all cells that users ARE allowed to change.
  2. Choose Format > Cells > Protection
  3. Remove the check mark from Locked.
  4. Then, to protect the worksheet, choose Tools > Protection > Protect worksheet.

I've lost my password!

Well, there are passwords and there are passwords. One is to open the file, one other is to unprotect the spreadsheet, still another is to unprotect the workbook and yet another one is to unprotect the macro code. File and VBA passwords cannot be cracked by a "normal macro", workbook and worksheet passwords are fairly easy. A search for "excel password" at http://www.google.com/ will find both commercial and free solutions of varying quality and brutality.

How can I protect an Excel file (and associated code) from unauthorized copying and/or create time limited functionality?

There is no foolproof method for this, and most solutions limit themselves to require macros enabled on opening. This said, creating your own system is a fun challenge allowing lots of "evil creativity".

Is there a way to allow the use of AutoFilter on a protected worksheet?

This needs a macro to run first: 

Sub Protect_keep_filter()
  With ActiveSheet
    .EnableAutoFilter = True
    .Protect DrawingObjects:=True, _
      contents:=True, Scenarios:=True, _
      UserInterfaceOnly:=True
  End With
End Sub

Note that the .enableautofilter has to be reset each time you open your file. (It's not persistent between closes.)

5. Entering and Saving Data

When I type a number, example 44, it becomes 0.44 even if I format the cell without decimals.

Goto Tools>Options/Edit and uncheck the Fixed decimals checkbox.

When I clear a large range , it takes forever. How can I make it faster?

If you have Google Desktop Search installed, turn it off in Excel.

How can I prevent hyperlinks from appearing when I type an email address?

To turn that option off in Excel 2002 and later versions:

  • Choose Tools | AutoCorrect Options
  • Select the AutoFormat as you type tab
  • Remove the check mark from Internet and network paths with hyperlinks
  • Click OK

To undo the hyperlink creation in all versions:

  • Type the email address and press Enter
  • Immediately, press Ctrl+Z. This is a shortcut for Undo, and will convert the hyperlink back to text.

To change a group of cells that contain hyperlinks, you can use the Convert with Paste Special technique.

The code below, when run on a selection, will also delete the hyperlinks.

Sub delHyperlinks()
Dim myCell As Range
For Each myCell In Selection
    myCell.Hyperlinks.Delete
Next myCell
End Sub

When I import information from my merchant account the field which holds the 16 digit credit card information does not display it properly.

Excel only maintains 15 significant digits for numbers and cannot be used for credit cards which require 16 digits. If you don't need to do math on them, input long numbers into a range that you've formatted as text (Format>Cells, Number tab).

To prevent both of the above issues (hyperlinks and 16-digit numbers) -- as well as entries that are automatically converted to dates (e.g. 3/4) -- type a single quote ( ' ) before the entry. Excel will treat the cell as pure text and change nothing.

I just began to design an intricate spreadsheet and after over an hour got an error message in Excel - which then closed automatically. Unfortunately, I did not save my file. Excel did not automatically recover the file. Is there anything I can do to get it back?

No. There should be an Autosave add-in in Tools > Add-Ins menu that you now might consider start using. (But then again, you may not want to overwrite an existing file with every little test you do in it, so be careful).

Jan Karel Pieterse has an add-in Autosafe.zip downloadable from http://www.oaltd.co.uk/MVP/Default.htm.

See also David McRitchie's http://www.mvps.org/dmcritchie/excel/backup.htm for more on backup and recovery.

Finally, Excel XP has great backup and recovery tools, so upgrading is a good future solution to those problems.

| Main Index

FAQs compiled by Harald Staff, Excel MVP 2000-2005

   
 HomeExcel TipsSample Spreadsheets

 

 

 

Contextures contact information

Last updated: May 7, 2008 10:00 PM