The Microsoft Excel Support Team Blog

Excel Crashes When You Enter Data in a Cell

2013-05-07T15:59:16Z

We want to bring an emerging issue to your attention. We have found that in certain circumstances, Excel will crash when you type data into a cell and press <Enter>. The same crash may occur when you select data and click the Quick Analysis button. We are currently investigating the issue, but it has been confirmed to be related to Flash Fill and Quick Analysis, and it only occurs in 32-bit Excel. It most often occurs when data has been imported from OLAP data sources, and copied to a new location. If you look at the crash report, the crash application will be Excel.exe, and the Module will be listed as ntdll.dll.

We have a couple of workarounds:

Workaround 1:

Click on File --> Options --> Advanced.
In the Editing Options section, clear the checkbox for Automatically Flash Fill.
Click on the General tab.
Clear Show Quick Analysis options on selection.

Workaround 2:

In your Data tab, click Connections.
Click the Properties... button.
Clear the checkboxes under OLAP Server Formatting.
Import your data.

Either of these should temporarily resolve the issue. We will update this blog as new information becomes available. If this blog helps you resolve crashes in Excel, please leave a comment.

How to Troubleshoot Memory Errors in Excel; "Available Resources", "Out of Memory", "System Resources"

2013-04-19T19:31:00Z

Thanks for the feedback the “available resources” post! We realized that many people are getting these type of memory issues in Excel and are looking for some answers. So we wrote a detailed KB that addresses most of the common memory issues.

If you are seeing any of the following messages;

"Excel cannot complete this task with available resources. Choose less data or close other applications."
“Out of Memory”
"Not enough System Resources to Display Completely"

2779852 How to Troubleshooting Available Resources errors in Excel
http://support.microsoft.com/kb/2779852

Office 2013 VBA component stops functioning

2013-04-11T21:46:00Z

We are seeing a few cases where the VBA component of the Office 2013 apps stop functioning. The applications are working properly when installed. Then a few days later VBA buttons no longer work. Here are the scenarios that we
have seen:

Access:

When running wizards the receive the following error, “This feature isn't installed, or has been disabled"
When opening VBA editor the user receives “Microsoft Access is unable to load the Visual Basic for Applications dynamic-link library (dll) Vbe7" when opening the Visual Basic Editor (VBE)”

Excel

The Visual Basic, Macros and Record Macro options are disabled/greyed on the ribbon.
When opening workbooks with VBA code, the user receive the following message, “The Visual Basic for Applications (VBA) macros in this workbook are corrupted and have been deleted."

Word

The Macro button is not greyed out but when trying to run or record a macro they receive the following
message, “The function you are attempting to run contains macros or content that requires macro language support"

PowerPoint

The Visual Basic, and Macros options are disabled/greyed on the ribbon.
When opening presentations with VBA code, the user receive the following message:
"The document contains macros. Macro language support for this application is disabled. Features requiring VBA are not available. Would you like to open this document read-only?"

In most cases repairing MS Office fixes the issue.

What we believe is happening is there is a registry cleaner or system maintenance type application removing some our registry keys that we
need for VBA to run successfully. In the above scenarios when the MS Office application was installed the VBA worked properly.

Problem Reopening Excel 2013 PivotTable Workbooks

2013-03-22T15:27:01Z

We’ve had a few reports recently from people who have built new PivotTables in Excel 2013, and when they try to reopen the book, receive a message saying:

We found a problem with some
content in <filename>. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

When they click “Yes,” and the file opens, they see a second message:

Removed Part: /xl/pivotCache/pivotCacheDefinition1.xml part with XML
error. (PivotTable cache) Load error. Line 2, column 0.

Removed Feature: PivotTable
report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)

The data associated with the PivotTable is still there, but the PivotTable no longer functions. This can also occur if you open a PivotTable from Excel 2010 is opened in Excel 2013, the data refreshed, and the file saved. When you try to reopen the file, it reports a problem with the content.

We have found that this will occur if Excel tries to read a 0 value in where it expects a date. Microsoft is currently investigating the issue further. In the meantime, you can work around this issue as follows:

Right Click on the PivotTable
Click PivotTableOptions
On the Display tab, clear the checkbox labeled
“Show Properties in ToolTips.”
Save the file with the new setting intact. Note:
If you fail to use the workaround before saving and closing the file, we cannot
recover the PivotTable.

For an explanation about how Excel stores dates as serial numbers, see http://support.microsoft.com/kb/214094.

How to: Use the Open XML SDK 2.0 in Visual Studio 2010 to create a simple Excel Spreadsheet (in C#)

2013-03-04T22:27:21Z

There are plenty of examples out on the internet to use the Open XML SDK 2.0 and Visual Studio 2010 to create Excel workbooks. I wanted to create my own how-to because the other examples I found seemed overly complex and some lacked basic information such as what references and namespaces need to be included.

1. Install the Open XML SDK 2.0 from here. You will want to download and install both the OpenXMLSDKTool.msi and OpenXMLSDKv2.msi

2. Once installed, Launch Visual Studio 2010. Create a new Windows Console Application under C#. Give your Console Application a name and click OK

3. The project is then created. Now you need to add references. Click Project > Add Reference and under the .NET tab look for "DocumentFormat.OpenXML". Click this reference and click OK. Again, Click Project > Add Reference and under the .NET tab look for "WindowsBase". Select this reference and click OK.

4. In your code, add the following namespaces:

using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

5. Under your "program" class, find "Static void Main (string[] args)". After the closing terminator add the following method:

public static void CreateSpreadsheetWorkbook(string filepath)
        {
            // Create a spreadsheet document by supplying the filepath.
            // By default, AutoSave = true, Editable = true, and Type = xlsx.

            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);

            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

            // Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
            sheets.Append(sheet);

workbookpart.Workbook.Save();

            // Close the document.
            spreadsheetDocument.Close();
        }

6. In your Main method, add the following line of code:

CreateSpreadsheetWorkbook("C:\\Test\\Test.xlsx");

The entire method will look like this:

static void Main(string[] args)
        {
            CreateSpreadsheetWorkbook("C:\\Test\\Test.xlsx");
        }

What we are doing here is calling the CreateSpreadsheetWorkbook method and telling it to create our basic spreadsheet in a directory on the C:\ drive called Test. We are also assigning a name to the workbook of "Text.XLSX" (if we didn't assign a name you would see an "Access Denied" error when the code is run"). Also, you will want to make sure the directory exists or else you will get a "DirectoryNotFoundException" during runtime.

At this point you should save the project and run it. If you have a directory C:\Test you should see a workbook called "Text.XLSX" appear in that folder. This is a very basic spreadsheet. It contains no data and only a single sheet called "mySheet". This is done by the two lines of code in the CreateSpreadsheetWorkbook method.

Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
sheets.Append(sheet);

If you want to know more about customizing the workbook, check out the following reference:

Generating Excel 2010 Workbooks by using the Open XML SDK 2.0 - http://msdn.microsoft.com/en-us/library/office/hh180830(v=office.14).aspx

Unable to use Excel 2010 PowerPivot workbooks in Excel 2013

2013-02-27T17:18:00Z

When you try opening an Excel 2010 PowerPivot workbook in Excel 2013 you will find that you are unable to manipulate the PivotTable. In addition, you will be prompted with an error message that asks you to upgrade the data model:

However, when you try to enable the PowerPivot add-in, you may not see it in the COM add-ins dialog box. This problem can happen because in Excel 2013, PowerPivot and the two new add-ins - PowerView and Inquire, are only available on certain SKUs (versions). These include:

Office 2013 Professional Plus (available through Volume Licensing, MSDN and TechNet subscriptions)
Office 365 Professional Plus - available as standalone subscription
Office 365 Professional Plus (available as part of the Office 365 Mid-Size Business, Office 365 Enterprise Plan 3, or Office 365 Enterprise Plan 4 offerings)

If you have purchased an Office 2013 or Office 365 SKU other than those listed above, you will need to return your product to the place of purchase or stop your current subscription to change to one of the products that does support these add-ins.

Workarounds

There are a couple of ways to work around this issue:

Access a machine that has one of the above-listed SKUs

If you have access to a machine that has one of the above listed SKUs, you can use the PowerPivot add-in on that machine to update the PivotTable and save the file. Once the PowerPivot data model is updated, then you can open and use the PivotTables on your other machines. Keep in mind that once updated, you cannot use the PivotTable in Excel 2010

Recreate the Workbook in 2013

If you do not have access to a machine with the PowerPivot 2013 add-in, you can rebuild your PivotTable in Excel 2013. There is no way to update the model, so you will need to import your data again and recreate the workbook from scratch. The data model engine is a part of Excel 2013. To do that:

If you have tables in Excel that you want to use, do the following.

Select a cell in one of the tables.
On the Insert tab, click on PivotTable.
Check Add this data to the Data Model.
Click Ok. Your PivotTable will be created.
In the Fields List, click the All tab. At this point you can add data from your other tables.
You may need to create relationships between the tables. You can do that on the Data ribbon or the PivotTable Tools/Analyze ribbon.

If you are importing data from multiple tables in a single external data source, do the following.

Choose to use an external data source.
Choose a connection that includes multiple data tables. When the PivotTable is created, the data will be added to the data model automatically.
In the Fields List, click the All tab, and add data from your tables.

If you have mixed data, do the following

Import the data into Excel tables first.
Create any necessary relationships.
Follow the instructions in item 1.

For more information on Excel's built-in data model, see the following blog: http://blogs.office.com/b/microsoft-excel/archive/2012/08/23/introduction-to-the-data-model-and-relationships.aspx

The two new COM add-ins for Excel Power View and Inquire also require the same SKUS of Excel that PowerPivot does. You can find out more about the Power View add-in at http://blogs.office.com/b/microsoft-excel/archive/2012/10/04/intro-to-power-view-for-excel-2013.aspx.

You can also read more about Inquire at http://office.microsoft.com/en-us/excel-help/what-you-can-do-with-spreadsheet-inquire-HA102835926.aspx.

Excel 2013 crashes after click-to-run install with DisplayLinkManager driver

2013-02-07T23:55:00Z

After you install Excel 2013 or Office 2013 click-to-run and launch Excel 2013 it may crash, giving you the following error: "Microsoft Excel has stopped working".

This problem can happen when the DisplayLinkManager driver is installed on the system. This problem is known to happen with DisplayLinkDriver versions 5.5.29055.0 or 5.6.30272.0.

To resolve this problem, update the DisplayLink driver. To do this, connect to http://www.displaylink.com/support/downloads.php and install any version of the DisplayLinkDriver later than 5.6.30272.0.

Note: The other Office Applications (Access, Word, PowerPoint, Outlook) will also crash if using the old DisplayLinkDriver.

UPDATE: A Microsoft Knowledge Base article to help troubleshoot hangs and crashes in Excel

2013-01-25T22:39:00Z

By Laura Holzwarth

Troubleshooting crashes in Excel be difficult. To assist you in this endeavor, we have published a two articles that walks you through a list of several methods and advanced troubleshooting steps involving hangs and crashes in Excel. These include:

Verify/install the latest updates
Start Excel in safe mode
Investigate possible issues with add-ins
Check whether your antivirus software is up to date or is conflicting with Excel
Make sure that Excel is not being used by another process
Check whether your file is being generated by a third party
Test by using a new local or network profile
Perform a selective startup to determine whether a program, process, or service conflicts with Excel
Repair or reinstall your Office program

The article also includes a section on advanced troubleshooting.

Excel: How to Troubleshoot Crashing and Not Responding Issues with Excel - http://support.microsoft.com/kb/2758592

Excel: Excel: How to troubleshoot crashing and not responding issues in an Excel Files - http://support.microsoft.com/kb/2735548

"We couldn't free up space on the Clipboard. Another program might be using it right now." [Updated 2/1/2013]

2013-01-25T21:47:00Z

By Anita Oakley

Hello everyone, we want to bring an emerging issue to your attention today. We have seen several reports of the error below in Microsoft Excel:

This occurs in multiple versions of Excel when you either cut and paste cells or drag them to move the contents from one location to another.

(Updated) We have narrowed this down to a conflict with the Bing Desktop Search Bar. If you have the Bing Desktop installed and run up against this issue, you can resolve the problem by updating to Bing Desktop 1.1.166.0 or later. You should receive the fix through Microsoft Update, assuming that you have automatic updates set.

However, if you are currently experiencing this problem, you can:

1. Check for updates to see if the Bing Desktop update is listed. If so, update your system.
2. Uninstall Bing Desktop through Control Panel  Programs and Features, and then install the new version from http://www.microsoft.com/en-us/download/details.aspx?id=35844

For more information about the Bing Desktop, see http://www.microsoft.com/en-us/download/details.aspx?id=35844.

Please also see the following Knowledge Base article, http://support.microsoft.com/kb/2807672 - "Excel produces error when dragging cells to another area"

Top 10 List of Performance Issues in Excel Workbooks

2012-12-18T22:05:00Z

By Laura Holzwarth

Performance issues are some of our biggest call generators. Users get frustrated with Excel when they upgrade to later versions. Performance issues often boil down to spreadsheets that have been around a long time and the design. Here are some of the common problem areas:

Slow Opening: Opening an Excel file the first time may be slow if you have a large number of calculations. Excel needs to recalculate the file and verify the values in the workbook. See KB’s below for more information.

210162 - Workbook loads slowly the first time that it is opened in Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;210162

925893 - External links may be updated in Excel 2007 when you open a workbook that was last saved in an earlier version of Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;925893

Updated Add-ins: Check your third party add-in companies to make sure you are using the latest version of the add-in. All too often the code in the add-ins has changed and the newer add-in
will run much better.

Formulas: Formulas referencing entire columns might display poor performance in .xlsx files. The grid size has grown from 65,536 to 1,048,576 rows and 256 (IV) to 16,384 (XFD) columns. A popular (not a best practice) way to create formulas was to reference entire columns.

For instance if you have a VLOOKUP that looked like this:

=VLOOKUP(A1,$D:$M,2,FALSE)

In Excel 2003 and prior it was referencing 655,560 cells (10 columns x 65,536 rows). With the new bigger grid, this same formula is referencing over 10 million cells. (10 columns x 1,048,576 rows = 10,485,760).

Shapes: When copy and pasting data from various locations, such as websites and other applications, hidden objects can be copied into the Excel file. These objects can cause performance issues in the preadsheet.
To determine if you have shapes in the file turn on Selection Pane.

On the Home Ribbon choose, Find and Select, Selection Pane to display a list of all shapes in the worksheet. You can pick and choose to delete the shapes by selecting the shapes and deleting
them. If you want to delete all shapes on the sheet, press Ctrl-G, choose Special, and then choose Objects. This will highlight all shapes and then you can delete them.

Defined Names: Check for any defined names linking to other workbooks or temporary internet files. Typically these links are unnecessary to the workbook, and slow down the opening of the Excel file. You can use these tools to see hidden defined names that we can’t see in the Excel interface.

http://www.jkp-ads.com/officemarketplacenm-en.asp

Styles: Old Excel files may collect styles and make the file grow. For more information see http://support.microsoft.com/kb/213904 and clean out the file of excess styles use the following tool.

Style Cleaner Tool

Excess file format cleaner: If files have been formatted by highlighting entire columns the file will grow in size rapidly. We also occasionally see this problem when copying or importing data from web pages or
databases. To clean up excess formatting use the Excess Format cleaner found there http://xsformatcleaner.codeplex.com/ for more information see the following KB

How to reset the last cell in Excel

http://support.microsoft.com/kb/244435

Personal.xls: If you are using a personal macro template located in the XLStart Folder, you will want to make sure that you open it in the latest version of Excel, do a full calculation, and save it as a personal.xlsx. You will only have to do this once. If you don’t do this, it calculates each time you open Excel, slowing down the open process.

XLSB extension: If you have large files that take a long time to open (after their first calculate and save –number 1 in this list), try saving the file with an .xlsb extension. Saving with this binary format will make the
file smaller and the load times faster. See the following article for more information about the new file formats: http://blogs.office.com/b/microsoft-excel/archive/2006/07/20/all-about-file-formats.aspx.

Macros and VBA: If you have a macro that worked until Excel 2010, check out the following link for the Object Model changes: http://msdn.microsoft.com/en-us/library/ee836187.aspx

There certainly could be more issues that can affect performance issues in Excel, but these are the most common. If you are looking for more information about how to improve performance in Excel, see the
following MSDN article: http://msdn.microsoft.com/en-us/library/aa730921(v=office.12).aspx