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 headerss show numbers, instead of letters?"
For Macintosh computers, see -- Excel for Mac Resources
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:
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:
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.
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.
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.
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.
Clear out c:\windows\temp directory on the machine that houses the files, then reboot.
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.
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.
Links come in several flavors. For example, a workbook could contain links in:
You might find Bill Manville's free FINDLINK.XLA program useful -- you can get it from the Excel MVP page, on Stephen Bullen's web site.
Try opening Excel without any addins or hidden workbooks:
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.
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:
Finally, this is what MS has to say:
XL: How to Troubleshoot Startup Problems in Microsoft Excel
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.
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 a few ways to close all open windows in Excel 2013 and later versions. Thanks to Alex Blakenburg for suggesting the shortcut options.
1. Add the missing "Close All" and "Exit" commands to the Quick Access Toolbar.
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.
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.
4. Right-click the Excel icon in the Windows Taskbar, at the bottom of your screen. Then click the Close All Windows command.
NOTE: This 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.
If multiple workbooks are open, any of these shortcuts will close the active Excel workbook only
If a single Excel workbook is open:
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.
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
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.
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.
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.
You can use the Freeze Panes command to lock rows above the selected row, and columns to the left of the selected column. Note: If cell A1 is the active cell, the freeze point will be in the centre of the Excel window.
To see the steps for freezing the headings with the Freeze Panes command, and the problem with hidden rows, please watch this short video tutorial. Written instruction for all versions are below the video.
Select the row below the section that you want to freeze. Then, on the Ribbon's View tab, click Freeze Panes, and click the Freeze Panes option. There's a very short video here that shows how to freeze panes in Excel 2007.
NOTE: Before you freeze the title rows, make sure that all the rows in the top section are visible, if you want to see them after freezing. For example, if Row 1 is out of view, you won't be able to scroll up to that row later.
On the Ribbon's View tab, click Freeze Panes, and click the Unfreeze Panes option.
Assuming title row is 1, select cell A2, then choose Window > Freeze Panes.
On the Menu bar, click Window >Unfreeze Panes .
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, and then you close the original window, which had the settings.
If you do open a second window in a workbook, make sure to keep the original window open, and close any other windows, when you're finished with the multiple windows.
In the screen shot below, the original window is on the right, and it has a freeze pane setting (1) and hidden gridlines (2). Leave it open, and close the window at the left, where the default settings are shown.
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.
Note: This code may not work correctly if the worksheet contains
merged cells. To check your worksheet, you can run the TestForMergedCells
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 '=====================================
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
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
Note: In Excel 2007 the chart feature for Fill Pattern was deprecated. Use Andy Pope's free Pattern Fill Add-in to restore the pattern feature.
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.
Instead of leaving the gridlines in their default color, you can follow these steps to change the color:
You can temporarily hide the Ribbon, leaving only the tabs visible.
You can see the steps in this short video.
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.
In Excel 2007 or later version, click the Normal icon, at the bottom right of the Excel window.
Or, on the Ribbon's View Tab, click the Normal command.
In Excel 2003, and earlier versions, that's the limit. Excel 2007 has 1,048,576 rows and 16,384 columns.
There is not a set limit in the Excel specifications, but there is a practical one which depends on the computer resources.
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).
Well, there are passwords and there are passwords.
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.
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 you protect the sheet, add a check mark to the option for "Use AutoFilter".
Yes, you can change a setting, when saving the file, so it requires a password to open:
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.
To turn off this setting:
NOTE: In Excel 2003, go to Tools>Options/Edit and uncheck the Fixed decimals checkbox.
If you have Google Desktop Search installed, turn it off in Excel.
NOTE: In Excel 2003, go to Tools | AutoCorrect Options, then change the setting, as described above
The code below, when run on a selection, will also delete the hyperlinks.
Dim myCell As Range
For Each myCell In Selection
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.
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 https://www.oaltd.co.uk/MVP/Default.htm.
See also David McRitchie's https://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.
Perhaps you were using the keyboard to put a line break in a cell, with Alt + Enter. If you accidentally hit the Window key, instead of Alt, that turns on the Windows Narrator in some versions of Windows.
To turn this feature off, press the Window key and tap the Enter key.
Original FAQs compiled by Harald Staff, Excel MVP 2000-2005. Revisions and additions by Debra Dalgleish.
Last updated: March 25, 2021 7:11 PM