\Excel 2016 For Mac\ Lags

11/26/2021by admin
  1. Microsoft Excel Lag
  2. Excel 2016 Mac Slow

Hello, I am running Windows 10 on Parallels 12. There is a noticeable lag whenever I use Alt shortcuts in Excel 2016. It happens when the keys are pressed individually and in sequence, e.g. It ends up typing 'TUD' in the cell instead of executing the shortcut when there isn't a sufficient delay after hitting the Alt key. 9 out of 10 times, an Excel user would complain about the slow Excel spreadsheets. And there is hardly anything you can do about it. Well, that’s NOT completely true. The way Excel has been made, it does get slow with large data sets. However, there are many speed-up tricks you can use to improve the performance of a slow Excel spreadsheet. Same issue in Excel for me (MBP2015, i7, MacOs 10.10.5); other office apps run fine up to now. Tried the latest office-2016 upgrade v3000 from the Office insider fast path for Excel only: same problems with scrolling, saving, whatever with this latest version.

Sale-->

Applies to: Excel Excel M365 Excel 2016 Excel 2013 Excel 2010 Office 2016 SharePoint Server 2010 VBA

Excel M365 introduces new features that you can use to improve performance when you are working with large or complex Excel workbooks

SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS Improvements

In Office 365 version 2005 monthly channel and later, Excel's SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, and MINIFS as well as their singular counterparts SUMIF, AVERAGEIF, and COUNTIF are much faster than Excel 2010 aggregating string data in the spreadsheet. These functions now create an internal cached index for the range being searched in each expression. This cached index is reused in any subsequent aggregations that are pulling from the same range.

The effect is dramatic: For example calculating 1200 SUMIFS, AVERAGEIFS, and COUNTIFS formulas aggregating data from 1 million cells on a 4 core 2 GHz CPU that took 20 seconds to calculate using Excel 2010, now takes 8 seconds only, on Excel M365 2006.

RealTimeData Function (RTD)

In Excel M365 version 2002 monthly channel or later, Excel's RealTimeData (RTD) function is much faster than Excel 2010 calculating data in the spreadsheet. We removed bottlenecks in its underlying memory and data structures as well as made it thread-safe to allow it to be calculated on all available threads of Multithreaded recalculation (MTR).

For example simulating 125,000 RTD updates for stock topics like 'Last Price', 'Ask', 'Bid' to calculate values like 'Trade Volume', 'Market Value', 'Trade Gain/Loss' etc. in 500,0000 cells in all, took 47 seconds using Excel 2010 and only 7 seconds using Excel M365 Version 2002, on the same hardware.

Another positive effect of making RTD function thread-safe, is that Multithreaded recalculation (MTR) doesn't need to be paused to run RTD function anymore. This improves performance noticeably when running RTD along with lots of other calculations.

For example, we ran a workbook with 10,000 RTD and 10,000 VLOOKUP functions, with each VLOOKUP depending on an RTD function result. Without thread-safe RTD full recalcuation took 10.20 seconds and with thread-safe RTD it took 5.84 seconds.

VLOOKUP, HLOOKUP, MATCH improvements

In Office 365 version 1809 and later, Excel's VLOOKUP, HLOOKUP, and MATCH for exact match on unsorted data is much faster than ever before when looking up multiple columns (or rows with HLOOKUP) from the same table range.

These lookup functions now create an internal cached index for the column range being searched. This cached index is reused in any subsequent lookups that are pulling from the same row (VLOOKUP and MATCH) or column (HLOOKUP). The effect is dramatic: lookups on 5 different columns in the same table range can be up to 4 times faster than the same lookups using Excel 2010 or Excel 2016, and the improvement is larger as more columns are looked up.

For example calculating 100 rows of these 5 VLOOKUP formulas took 37 seconds to calculate using Excel 2010 and only 12 seconds using Excel 2016.

LAA memory improvement for 32-bit Excel

Although the 64-bit version of Excel has large virtual memory limits, the 32-bit version has only 2 GBs of virtual memory. Some customers use the 32-bit version because some third-party add-ins and controls are not available in the 64-bit version.

The 32-bit versions of Excel 2013 and Excel 2016 now have Large Address Aware (LAA) enabled. This will minimize out-of-memory error messages.

LAA doubles available virtual memory from 2 GB to 4 GB on 64-bit versions of Windows, and increases available virtual memory from 2 GB to 3 GB on 32-bit versions of Windows.

For more information, see Large Address Aware Capability Change for Excel.

To download a tool that shows how much virtual memory is available and how much is being used, see Excel Memory Checking Tool.

Full column references

In earlier versions of Excel, workbooks using large numbers of full column references and multiple worksheets (for example =COUNTIF(Sheet2!A:A,Sheet3!A1)) might use large amounts of memory and CPU when opened or when rows were deleted.

Excel 2016 Build 16.0.8212.1000 reduces the memory and CPU used in these circumstances.

In a sample test on a workbook with 6 million formulas, using full column references failed with an out-of-memory message at 4 GB of virtual memory with Excel 2013 LAA and with Excel 2010, but only used 2 GB of virtual memory with Excel 2016.

Structured references

In Excel 2013 and earlier versions, editing tables where formulas in the workbook use structured references to the table was slow. This led to the perception that tables should not be used with large numbers of rows. This issue no longer occurs in Excel 2016.

For example, an editing operation that took 1.9 seconds in Excel 2013 and Excel 2010 took about 2 milliseconds in Excel 2016.

Filtering, sorting, and copy/pasting

We've made a number of improvements to the response time when filtering, sorting, and copy/pasting in large workbooks.

In Excel 2013, after filtering, sorting, or copy/pasting many rows, Excel could be slow responding or would hang. Performance was dependent on the count of all rows between the top visible row and the bottom visible row. These operations are much faster after we improved the internal calculation of vertical user interface positions in Build 16.0.8431.2058.

Opening a workbook with many filtered or hidden rows, merged cells, or outlines could cause high CPU load. We introduced a fix in this area in Build 16.0.8229.1000.

After pasting a copied column of cells from a table with filtered rows where the filter resulted in a large number of separate blocks of rows, the response time was very slow. This has been improved in Build 16.0.8327.1000.

A sample test on copy/pasting 22,000 rows filtered from 44,000 rows showed a dramatic improvement:

  • For a table, the time went from 39 seconds in Excel 2013 and 18 seconds in Excel 2010 to 2 seconds in Excel 2016.
  • For a range, the time went from 30 seconds in Excel 2013 and 13 seconds in Excel 2010 to instantaneous in Excel 2016.

Copying conditional formats

In Excel 2013, copy/pasting cells containing conditional formats could be slow. This has been significantly improved in Excel 2016 Build 16.0.8229.0.

A sample test on copying 44,000 cells with a total of 386,000 conditional format rules showed a substantial improvement:

  • Excel 2010: 70 seconds
  • Excel 2013: 68 seconds
  • Excel 2016: 7 seconds

Adding and deleting worksheets

When adding and deleting large numbers of worksheets, a sample test on Excel 2016 Build 16.0.8431.2058 shows a 15%–20% improvement in speed compared to Excel 2013, but 5-10% slower than Excel 2010.

New functions

Excel 2016 Build 16.0.7920.1000 introduces several useful worksheet functions:

  • MAXIFS and MINIFS extend the COUNTIFS/SUMIFS family of functions. These functions have good performance characteristics. Use them to replace equivalent array formulas.
  • TEXTJOIN and CONCAT let you easily combine text strings from ranges of cells. Use them to replace equivalent VBA UDFs.

Other updates to Excel 2016 for Windows

For more details about the month-by-month improvements to Excel 2016, see What's new in Excel 2016 for Windows.

Excel 2010 performance improvements

Based on user feedback about Excel 2007, Excel 2010 introduces improvements to several features.

FeatureImprovement
Printer and page layout view
To improve performance of basic user interactions in page layout view, such as entering data, working with formulas or setting margins, Excel 2010 caches the printer settings and introduces optimized rendering calculations. Caching the printer settings reduces the number of network calls and reduces the dependency on a slow or unresponsive printer. In addition, connecting to the printer is cancelable so that the user does not have to wait for a slow or unresponsive printer.
Charts
Starting in Excel 2010, the rendering speed of charts has increased, especially with large data sets, and text-rendering performance has improved. In addition, Excel 2010 caches an image of a chart and uses the cached version when possible, to avoid unnecessary calculations and rendering.
VBA solutions
Improvements to the object model and the way it interacts with Excel increases the performance speed of many VBA solutions when run in Excel 2010 compared with Excel 2007.

Large data sets and the 64-bit version of Excel

The 64-bit version of Excel 2010 is not constrained to 2 GB of RAM like the 32-bit version applications nor upto 4 GB of RAM like the Large Address Aware 32-bit version applications. Therefore, the 64-bit version of Excel 2010 enables users to create much larger workbooks. The 64-bit version of Windows enables a larger addressable memory capacity, and Excel is designed to take advantage of that capacity. For example, users are able to fill more of the grid with data than was possible in previous versions of Excel. As more RAM is added to the computer, Excel uses that additional memory, allows larger and larger workbooks, and scales with the amount of RAM available.

In addition, because the 64-bit version of Excel enables larger data sets, both the 32-bit and 64-bit versions of Excel 2010 introduce improvements to common large data set tasks such as entering and filling down data, sorting, filtering, and copying and pasting data. Memory usage is also optimized to be more efficient in both the 32-bit and 64-bit versions of Excel.

For more information about the 64-bit version of Office 2010, see Compatibility Between the 32-bit and 64-bit Versions of Office 2010 and for choosing between 64-bit and 32-bit, see Choose between the 64-bit or 32-bit version of Office.

Shapes

Excel 2010 introduces significant improvements in the performance of graphics in Excel. At a high level, these improvements are in two areas: scalability and rendering.

The scalability improvements have a large impact in Excel scenarios because of the large number of graphics contained on worksheets. Often, this large number of shapes is created accidentally by copying and pasting data from a website, or by commonly run automation that creates shapes, but never removes them. This large number of graphics, combined with the way that graphics relate to the data grid in Excel, presents several unique performance challenges. Improvements in Excel 2010 increase the performance speed for worksheets that contain many shapes.

In addition, starting in Excel 2010, support for hardware acceleration improves rendering. Excel 2010 also introduces performance improvements to the Select method of the Shape object in the VBA object model.

FeatureImprovement
Basic use
The first set of improvements made in Excel 2010 surrounds basic use scenarios. These scenarios include operations and features such as sorting, filtering, inserting or resizing rows or columns, or merging cells. When these operations occur, it may be necessary to update the position of a graphic object on the grid. In the worst-case scenario, it is necessary to make an update to every single object on the worksheet. In Excel 2010, performance of these basic scenarios improves even when there are thousands of objects on the worksheet. These improvements were not achieved with a single feature or fix, but through a dedicated focus on performance that included improving the shape lookup mechanism, testing stress files, and investigating obstructions.
Text links
A text link on a shape is created when the user specifies a formula, for example '=A1', that defines the text for a given shape. These particular shapes were prone to cause performance issues on sheets with a large number of objects and/or when changes were made to cell content. Starting in Excel 2010, the way Excel tracks and updates these shapes has improved to optimize performance for changing cell content. This work improves scenarios such as typing a new value in a cell or performing complex object model operations.
Big Grid
Starting in Excel 2007, the size of the grid expanded from 65,000 rows to over one million rows. This increase caused some performance and rendering issues when working with graphics objects in the new regions of the larger grid. Starting in Excel 2010, Excel optimizes functionality that relies on using the top left of the grid as the origin to improve the experience of working with graphics in the new regions of the grid. Rendering fidelity and performance are improved relative to Excel 2007.
Rendering: Hardware acceleration
Starting in Excel 2010, improvements were made to the graphics platform by adding support for hardware acceleration when rendering 3-D objects. While the GPU can render these objects faster than the CPU, the experience in Excel 2010 depends on the content on your worksheet. If you have a sheet full of 3-D shapes, you will see more benefit from the hardware acceleration improvements than on a worksheet with only 2-D shapes (which do not leverage the GPU).

Calculation improvements

Starting in Excel 2007, multithreaded calculation improved calculation performance.

Starting in Excel 2010, additional performance improvements were made to further increase calculation speed. Excel 2010 can call user-defined functions asynchronously. Calling functions asynchronously improves performance by allowing several calculations to run at the same time. When you run user-defined functions on a compute cluster, calling functions asynchronously enables several computers to be used to complete the calculations. For more information, see Asynchronous User-Defined Functions.

Multi-core processing

Excel 2010 made additional investments to take advantage of multi-core processors and increase performance for routine tasks. Starting in Excel 2010, the following features use multi-core processors: saving a file, opening a file, refreshing a PivotTable (for external data sources, except OLAP and SharePoint), sorting a cell table, sorting a PivotTable, and auto-sizing a column.

For operations that involve reading and loading or writing data, such as opening a file, saving a file, or refreshing data, splitting the operation into two processes increases performance speed. The first process gets the data, and the second process loads the data into the appropriate structure in memory or writes the data to a file. In this way, as soon as the first process begins reading a portion of data, the second process can immediately start loading or writing that data, while the first process continues to read the next portion of data. Previously, the first process had to finish reading all the data in a certain section before the second process could load that section of the data into memory or write the data to a file.

PowerPivot

PowerPivot refers to a collection of applications and services that provide an end-to-end approach for creating data-driven, user-managed business intelligence solutions in Excel workbooks. PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within Excel. Leveraging familiar Excel features, users can transform large quantities of data from almost any source with amazing speed into meaningful information to get the answers they need in seconds.

PowerPivot also integrates with SharePoint. In a SharePoint farm, PowerPivot for SharePoint is the set of server-side applications, services, and features that support team collaboration on business intelligence data. SharePoint provides the platform for collaborating and sharing business intelligence across the team and larger organization. Workbook authors and owners publish and manage the business intelligence that they develop to their SharePoint sites.

For more information about PowerPivot, see PowerPivot Overview.

HPC Services for Excel 2010

With a wealth of statistical analysis functions, support for constructing complex analyses, and broad extensibility, Excel 2010 is the tool of choice for analyzing business data. As models grow larger and workbooks become more complex, the value of the information generated increases. However, more complex workbooks also require more time to calculate. For complex analyses, it is common for users to spend hours, days, or even weeks completing such complex workbooks.

One solution is to use Windows HPC Server 2008 to scale out Excel calculations across multiple nodes in a Windows high-performance computing (HPC) cluster in parallel. There are three methods for running Excel 2010 calculations in a Windows HPC Server 2008 based cluster: running Excel workbooks in a cluster, running Excel user-defined functions (UDFs) in a cluster, and using Excel as a cluster service-oriented architecture (SOA) client.

For more information about HPC Services for Excel 2010, see Accelerating Excel 2010 with Windows HPC Server 2008 R2.

Conclusion

Excel 2016 introduces performance and limitation improvements focused on increasing Excel's ability to efficiently handle large and complex workbooks. These improvements allow Excel to scale along with hardware, improving performance as the CPU and RAM capacity of computers expand.

See also

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Summary

When you receive the message that Excel is not responding, you should take actions to fix it right now. This page shares 8 solutions to fix this problem. You can not only fix Excel not responding, but also recover the Excel file when Excel is crashing.

Excel Not Responding

Microsoft Excel is widely used by the officer for saving data. It works great from calculation, charting to graphing data.

However, the sudden message pops up saying,

'Excel is not responding'

'Excel has stops working'

So you are probably wondering:

How to fix Excel not responding?

We are helping you.

This page collets the comprehensive 8 solutions to fix Excel not responding. The 8 solutions also work when your Excel 2003/2007/2010/2013/2016/2019 is running slow, freeing, crashing, stopping working, etc.

This is the most straightforward and common way. Excel Safe Mode allows you to overcome the startup issues.

Step 1 Open Command box on computer.

Windows 10: choose Start > All apps > Windows System > Run;

Windows 8: click Run in the Apps menu;

Windows 7: click Start.

Step 2 Type 'excel.exe /safe'. It will run Excel in the Safe Mode.

Now, Open the Excel file again, your problem might have resolved.

Way 2.
Enable Excel in the latest update

Excel not responding may occur because you do not install the latest Windows updates.

What you need to do is to check and update Excel to the latest version.

On Windows for Excel 2013 and 2016:

Step 1 Open Excel app on your computer.

Step 2 Go to 'File' > 'Account' > 'Product Information' > 'Update Options' > 'Update Now'.

On Windows for Excel 2010:

Step 1 Open Excel app on your computer.

Step 2 Go to 'File' > 'Help' > 'Check for Updates' > 'Install Updates' or 'Check for Updates'.

On Windows, Excel 2007 and 2003, the update support has ended from Microsoft.

Microsoft Excel Lag

Excel update on Mac:

Step 1 Run Excel on your Mac.

Step 2 On the top menu, go to 'Help' > 'Check for Updates' > Choose 'Automatically Download and Install' under 'How would you like updates to be installed?' > Click 'Check for Updates'.

Some developers create add-ins for Excel. The add-ins can occasionally conflict with Excel. Disable add-ins to fix the problem.

The add-ins are sorted by two types:

Excel add-ins that are saved as .xla, .xlam, or .xll files.

Microsoft excel 2016 for mac

COM add-ins. They are created for one version of Excel frequently, which are not compatible with a newer version, and may cause crashes. They are not disabled by Safe mode.

For fixing it, you can do as following:

Step 1 Run Excel in Safe Mode (Solution 1).

Step 2 Click 'File' > 'Options' > 'Add-ins' > Select 'COM Add-ins' > click 'Go' to clear all the check boxes in the list and click 'OK'.

Then you need to close and restart Excel to have a try.

Way 4.
Change Default printer

This is yet another simple and efficient solution. When Excel is started, it examines the default printer that will display the Excel workbooks. Excel is printer-intensive. Therefore, it will run even slower or crashed when Excel files are saved in Page Break Preview view.

For fixing it, you can change the default printer.

Click on the 'Control Panel' > 'Printer and Devices' > Right click 'Microsoft XPS Document Writer' to set it to the default printer.

Now, reopen your Excel document, and the error may have been resolved.

Corrupted Excel in MS Office can also generate Excel not responding error. For removing this trouble, you could repair Excel.

On Windows 8.1, 8, 7 and Vista:

Step 1 Click 'Start' > 'Control Panel' > 'Category' > Select 'Uninstall a program' under 'Programs' > Click Excel > 'Change'.

Step 2 Depending if your copy of Office is Click-to-run or an MSI-based install, do the following for your Office installation type:

Then follow the on-screen instructions to repair your Excel.

On Windows 10:

Step 1 Right-click Start button > Select 'Apps and Features' > Select 'Excel' > 'Modify'.

Step 2 Repeat Step 2 like before to complete the repair process.

For

Note

This will repair the entire Office suite even if it's just one application you want to repair such as Excel. If you have a standalone app installed, then search for that application by name.

If repairing your Excel did not resolve your issue, continue on to the next item on the list.

Way 6.
Uninstall and reinstall Excel

If the above solutions fail to fix your Excel not responding or crash, then you can try to uninstall and reinstall Excel, which seems to be a common solution for all crashing apps.

Go to 'Control Panel' > 'Programs' > 'Programs and Features' > Find Microsoft Office and right click 'Uninstall'.

Then you can reinstall Excel on your computer to remove this error.

Your up-to-date anti-virus software may be the reason why your Excel is not responding or crashing.

So check your anti-virus software update.

Changing your anti-virus settings may make your PC vulnerable to viral, fraudulent or malicious attacks. Microsoft does not recommend that you attempt to change your antivirus settings.

Way 8.
Rescue data from not responding Excel

The above ways aims at fixing such excel troubleshooting as non-responding, crashing, freezing, non-working, etc.

Another point that you should care about is how to save data when Excel is not responding before you save it.

Last but not least.

This is the last solution to recover data from not responding Excel.

Aiseesoft Data Recovery is the remedy.

Data Recovery
  • It recovers Excel from corruption, not responding, crashing, non-working, etc.
  • Support Excel recovery in 2003, 2007, 2010, 2013, 2016 and 2019.
  • Apart from Excel, other document like Word, RTF, TXT, etc. are supported.
  • Media files like photos, images, videos, audio files are available to the recovery.
  • You can make Excel recovery on your Windows 10/8.1/8/7/ Vista/XP (SP2 or later).
  • Excel recovery on computer, flash drive, removable drive, memory card, etc. is fully met.

Now, let's check it.

Step 1 Download this software on your computer. In the main interface, checkmark 'Document'.

Select one disk drive where you lost the Excel. Click 'Scan'.

Step 2 In the scanning result, click 'Document' > 'XLSX' to find the Excel file that you want to recover, checkmark and click 'Recover' to save the lost Excel file to your computer.

Notes

Excel 2016 Mac Slow

1. If the quick scan cannot find your wanted Excel, you can try 'Deep Scan' to get a thorough scan on your computer. Surely, it will take longer time.

2. Not only Excel file document not saved, this software also lets you get back saved-over Excel file easily.

FAQs about Excel Not Responding

1. Why does Excel keep crashing when I paste?

a. The Excel is not updated to the latest version.
b. Excel might be running in the background that causes freezing.
c. Some add-ins may be intrusive with Excel.

2. How to fix Excel not responding without closing?

a. Install the latest version of Microsoft Office.
b. Type excel.exe /safe in the Run box to open Excel in safe mode.
c. Open Excel in safe mode, and click File > Options > Add-ins. Select COM Add-ins and uncheck the add-ins in the list.

3. What to do if Excel file corrupted?

If you are suffering from corrupted Excel files, you can fix excel file corrupted with its auto-repair feature manually. Just click File > Open and select the corrupted workbook. Click the drop don button next to Open button, and select Open and Repair > Repair.

Conclusion

Here's the last part…

Now I have a quick question for you:

Did you fix your Excel not responding from the above-stated solutions?

If yes, congratulations, and share this post now.

If no, leave a comment and let me help you.

What do you think of this post?

Excellent

Rating: 4.8 / 5 (based on 123 votes)

January 11, 2021 11:30 / Posted by Amanda Brown to File Recovery

  • How to Compress a PDF File and Reduce PDF File Size for Email

    Want to know how to compress PDF files? This article tells you how to reduce size of PDF with the best PDF compressor.

  • How to Quickly Convert PDF to Excel Spreadsheets

    This is a step-by-step article that tells how to convert PDF to editable Excel file with ease.

  • How to Delete Documents and Data on iPhone (to Free up Space)

    What is Documents & Data on iPhone? How to clear/delete Documents and data on iPhone and free up space on iPhone? Read this article and find the answer.

Comments are closed.