Home > Skills > FAQs > Files FAQ

Excel Application and File FAQs

Answers to frequently asked questions about the Excel application and Excel files. For example, "Why does Excel say that my file has links?" and "Why do the column headers show numbers, instead of letters?"

header font wingding

For Macintosh computers, see -- Excel for Mac Resources

1. Start Excel, Open and Close Files

1.1) Files open automatically

1.1a) Prevent files from opening automatically in Excel

Q: When I start Excel, why do files open up automatically?

A: To stop files automatically opening, you can remove a folder name in the Excel options:

  • Click the File tab, then click Options
  • Click the Advanced category, and scroll down to the General section.
  • In the box for 'At startup, open all files in', you might see the name of a folder, and its path
  • Clear the folder information from that box (or go to that folder and remove the unwanted files).
  • Click OK, to close the Options dialog box.

excel options startup

1.1b) Check the XLSTARTUP Folders

If there was nothing listed in the Startup box, the unwanted files might have been accidentally saved to one of the XLSTART folders. The location of this folder depends on which Office version is installed. For example, it might be in this path:

 C:\Users\YOUR NAME\AppData\Roaming\Microsoft\Excel\XLSTART

To find the folder on your computer, open Windows Explorer, and go to the C drive. In the Search box, tpe "XLSTART", and press Enter.

1.1c) Remove Unwanted Files

After you locate the files, in Windows Explorer, go to those folders, and move or delete the unwanted files, to prevent them from opening automatically when Excel starts.

1.2) Compile error in Hidden Module

Q: When I start Excel, why is there an error message "Compile error in Hidden Module"

A: 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 Excel
  5. If there is no error, check the next add-in, 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.

1.3) Some Excel files have become "read-only".

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

1.4) Excel file began opening two copies of itself.

If I close one of the copies, both close. Any changes made to one copy show up in the other. How can I fix this problem

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.

close window

1.5) File with that name is already open

Q: 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." What can I do?

A) 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.

run regserver

Other things to check:

  1. In Excel, choose File >Options.
  2. At the left, click the Advanced category
  3. Scroll down to the General section
  4. Make sure the "Ignore Other Applications" setting is not checked.

1.6) Excel says file has links

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

A:In addition to hyperlinks on a worksheet, an Excel workbook could contain links in:

  • linked formulas,
  • defined names (Insert > Name > Define menu),
  • objects (buttons, images, etc.) assigned to macros in other workbooks

I use Bill Manville's free FINDLINK program to find those hidden links in my Excel files.

Excel freezes / crashes

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

A) 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.

1.8) Excel crashes - "not enough resources"

Q) Excel crashes on opening / closing / prompts "not enough resources"

A) 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..

1.9) Close all the open files

Q) There's no "Close All" button, so how can I close all the open files in Excel 2013 and later versions?

A) In Excel 2013 and later versions, each file opens in a separate window. Unlike earlier single-window versions, there is no Exit button or command, to close all the files, without clicking each window individually.

Here are 4 different ways to close all open windows in Excel 2013 and later versions. Thanks to Alex Blakenburg for suggesting the shortcut options.

Option 1. Add the missing "Close All" and "Exit" commands to the Quick Access Toolbar.

  • Close All and Exit on QAT

Option 2. Press the Shift key, and click the X at the top right of one of the Excel windows. You will be prompted to save any unsaved files, and then all the windows will close.

  • Shift and click X

Option 3. Press the Alt key, and tap the F key, then tap the X key. When you press Alt+F, the File tab is activated. Even though the X shortcut doesn't appear in the list, the Exit command runs when you tap X.

  • Press Alt, F, X

Option 4. Right-click the Excel icon in the Windows Taskbar, at the bottom of your screen. Then click the Close All Windows command.

  • Press Alt, F, X
  • NOTE: This technique only works if the Taskbar has "Always, hide labels" as the setting for "Combine taskbar buttons:
    • To change that setting, right-click the Taskbar, and click Taskbar Settings.
    • Taskbar Settings
  • This short TikTok video shows the steps to close all Excel windows, by using the Taskbar command.
@contextures Don’t close Excel files one at a time! Use Windows taskbar command #excel #exceltips #microsoftexcel #learnexcel #Contextures ♬ This Is How We Do It - Montell Jordan

Other shortcuts to close Excel Windows

If multiple workbooks are open, any of these shortcuts will close the active Excel workbook only

  • Alt+F4
  • Ctrl+F4
  • Ctrl+W

If a single Excel workbook is open:

  • Alt+F4 shortcut closes the active Excel window, and also closes Excel.
  • Ctrl+F4 and Ctrl+W shortcuts close the active Excel window, and leave Excel open .

2) Worksheet Appearance

2.1) Column headers show numbers

Q: Why do column headers show numbers instead of letters?

A: To see why this happens, and how to switch the column headings back to letters, watch this short video tutorial. The written instructions are below the video.

How to Change Column Headings to Letters

Follow the steps below, to manually change column headings back to letters, instead of numbers. If you make this change frequently, you can use a macro: Macro - Change Column Headings to Letters

Change column headings to letters
  1. At the top of Excel, click the File tab, and then click Options.
  2. Click the Formulas category
  3. Remove the check mark from 'R1C1 reference style' .

    r1c1 reference style 2007

2.2) Change font in column and row headers

Q: How can I change the font in column and row headers?

When you create a new file in Excel, the row and column headers are displayed in the Normal font for your workbook settings. The Normal font is also used in the worksheet cells, unless you select a different format in the cells.

header font 01

You could even change the row and column headers to pictures, by changing the Normal font to a graphic font, such as Wingdings. Then, instead of referring to cell J1, you can put good news in the "Happy Face Folder" cell. Remember to format the cells in a non-graphic font, after you change the Normal style.

header font wingding

To change the Normal font in Excel 2010:

  1. On the Excel Ribbon, click the Home tab
  2. In the Styles group, click Cell Styles, to open the Cell Styles palette.
  3. Right-click on the Normal style, and click Modify

    Normal font modify

  4. Click the Format button, and select the font and font size you want for the Normal style.

    Normal font modify

  5. Click OK, twice, to close the dialog boxes.

To change the Normal font in Excel 2003:

  1. On the Excel menu bar, Format, and click Style...
  2. In the Style Name drop down list, select Normal, and click Modify
  3. Click the Font tab, and select the font and font size you want for the Normal style.

    Normal font modify

  4. Click OK, twice, to close the dialog boxes..

2.3) 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...

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

selected tab

2.4) Lock top row to keep it visible

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

You can use the Freeze Panes command to lock rows above the selected row, and columns to the left of the selected column.

This video shows the steps, and there are written steps on the Freeze and Zoom in Excel page.

2.5) Freeze Pane and gridline settings disappear

Q: Why did my Freeze Pane settings and gridline settings disappear?

A: When you lock the title row(s) in place, as described above, those settings should stay in place, if you've saved the file. However, sometimes the freeze pane setting, or the gridline setting, disappears, even though you haven't changed the settings.

This can happen if you:

  • open a second window in the same workbook
  • close the original window, which had the settings.

To avoid this problem, go to the Freeze and Zoom page, and watch the short video in the Freeze Pane Settings Disapper section

2.6) Scrollbars go to row 500

Q: Why do the scrollbars go way down to row 500, when 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.

      delete entire row

  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
'=====================================

2.7) Fill colour doesn't show on worksheet

Q: 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?

A: If the high contrast setting is turned on you won't see the fill colour.

  • Click the Windows Start button
  • Click the Settings icon
  • Click the Ease of Access button
  • At the left, click on High Contrast
  • If the High contrast setting is on, turn it off

high contrast setting

Note: The keyboard shortcut to toggle high contrast on or off is:

  • left Alt key + left Shift key + Print Screen key

2.8) Chart data disappears when rows hidden

Q: When I hide rows, the data disappears from my chart

By default, charts do not display the data in hidden rows and columns, but you can change a setting to make the data appear in the chart.

This video shows how to change an Excel chart's settings, so all the data will appear in the chart, even if some of the data rows or data columns are hidden.

There are written steps on the Show Hidden Data in Excel Chart page.

2.9) Change worksheet gridline color

Q: How can I change the gridline color on a worksheet?

Instead of leaving the gridlines in their default color, you can follow these steps to change the color:

  1. Click the File tab in the Excel Ribbon, then click Options.
  2. Click the Advanced category, then scroll down to see the 'Display options for this worksheet' section
  3. Click the drop down arrow for Gridline color, and click on the color that you want, then click OK to close the window.

gridline color

2.10) Hide the Excel Ribbon

Q: How can I hide the Excel Ribbon, to make more room on the worksheet?

You can temporarily hide the Ribbon, leaving only the tabs visible.

  • To hide the Ribbon commands, double-click on one of the tabs.
  • To show the Ribbon commands again, double-click on one of the tabs

You can see the steps in this short video.

2.11) Remove the big "Page 1" on sheet

Q: How do I remove the big "Page 1" watermark on my worksheet?

When the worksheet is in Page Break Preview mode, the page number is shown in the centre of the sheet.

You can't remove the number in that view, but you can change to a different view, such as Normal view. See the steps, below the screen shot

Page Break Preview mode

To go back to Normal view:

To quickly change the view, click the Normal icon, at the bottom right of the Excel window.

Normal view icon

Or, on the Ribbon's View Tab, click the Normal command.

click the Normal command

3) Excel Limits and Specifications

3.1) How many worksheets I can put in a workbook? go to top

There is not a set limit in the Excel specifications, but there is a practical one which depends on the computer resources.

3.2) How many characters can be placed in a cell? go to top

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

4.1) Protect formulas

Q: How can I protect formulas from being deleted or changed?

Before protecting the worksheet:

  1. Select all cells that users ARE allowed to change.
  2. On the keyboard, press Ctrl + 1 to open the Format Cells dialog box
  3. On the Protection tab, remove the check mark from Locked.
  4. Click OK, to close the dialog box

locked cells

Protect the worksheet

    1. On the Excel Ribbon, click the Review tab
    2. Click Protect Sheet.

4.2) I've lost my password! go to top

Well, there are passwords and there are passwords.

  • One is to open the file
  • one other is to unprotect the worksheet
  • still another is to unprotect the workbook
  • yet another one is to unprotect the Excel VBA 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 https://www.google.com/ will find both commercial and free solutions of varying quality and brutality.

4.3) Protect Excel file from copying

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

4.4) Allow AutoFilter on protected sheet

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

When you protect the sheet, add a check mark to the option for "Use AutoFilter".

allow aurofilter on protected sheet

4.5) Protect file with a password

Q: Can I prevent people from opening a file without a password?

Yes, you can change a setting, when saving the file, so it requires a password to open:

  1. Open the file that you want to protect with a password
  2. On the Ribbon, click the File tab, and click Info
  3. Click the Protect Workbook command, then click Encrypt with Password

    encrypt with password

  4. In the Encrypt Document dialog box, type a password, then click OK.
  5. Re-enter the password in the confirmation box.

NOTE: Microsoft can't help you recover lost or forgotten passwords, so keep a list of your passwords in a safe place, or you could be locked out of a protected file.

5) Entering and Saving Data

5.1) Typed number divides by 100 go to top

Q: When I type a number, it is automatically divided by 100. For example 44 becomes 0.44 even if I format the cell without decimals.

To turn off this setting:

  • Click the File tab on the Excel Ribbon (or click the Office button in Excel 2007)
  • Click the Options command, then click the Advanced category.
  • In the Editing Options section, remove the check mark from "Automatically insert a decimal point", and click OK.

NOTE: In Excel 2003, go to Tools>Options/Edit and uncheck the Fixed decimals checkbox.

insert a decimal point

5.2 Prevent automatic hyperlinks

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

To turn that option off:

  • Click the File tab on the Ribbon, then click Options
  • Click the Proofing category, then click theAutoCorrect Options button
  • Click the "AutoFormat as you type" tab
  • Remove the check mark from Internet and network paths with hyperlinks
  • Click OK, then close the Options window

To undo the hyperlink creation::

  • 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. Watch a short video that shows the Change Excel Hyperlinks to Text steps.

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

5.3) 16 digit credit card number cut off

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

A: 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.

5.4) Excel is speaking

Q: I accidentally typed something and now Excel is talking to me. How do I turn that voice off?

You might have used a keyboard combination that turned on the Windows Narrator in some versions of Windows. To turn this feature off, press the Narrator keyboard shortcut:

  • Window logo key + Ctrl + Enter

window key

go to top | Main Index

Original FAQs compiled by Harald Staff, Excel MVP 2000-2005. Revisions and additions by Debra Dalgleish.

FAQ Menu

 

 

Last updated: April 19, 2024 10:27 AM