Microsoft Excel Problems and Solutions


Source: Contexture

The article below lists some of the most frequently faced problems of Microsoft Excel and provides reasonable solutions for them. Its in a questions and answers format.

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.




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







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 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".




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
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 downloadable from

See also David McRitchie's 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.

Source: Contextures