Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Forcing Stubborn Recalculation.
Written by Allen Wyatt (last updated July 9, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
Kirk wrote that he was having a problem with the recalculation of his worksheet. He mentions that the worksheet is complicated and that pressing F9 does not get the spreadsheet to "recalculate correctly."
The first thing to try is to press Alt+F9 instead of just F9. When you press F9, Excel basically recalculates just the cells that have changed since the last time there was a recalculation. The Alt+F9 shortcut forces a recalculation of all cells in the worksheet. If you really need to do some heavy lifting, Ctrl+Alt+F9 will recalculate the entire workbook.
If that doesn't do the trick, then you may have a problem that is sometimes evident with complex worksheets: The order of the calculations done by Excel. When you calculate a worksheet, Excel basically calculates the cells from left to right and top to bottom. If you have a very large worksheet, with lots of dependent calculations, and the calculations on which everything else is dependent are at the bottom or right side of the worksheet, then you may get incorrect results. (Remember, this happens only with the most complex of worksheets.) The answer is to reorganize your worksheets so that the primary calculations are placed near the top of the worksheet and as far left as possible, and the calculations that are based on those primary calculations are placed later in the worksheet.
If you still have problems with the worksheet, try saving it as an HTML file and then reloading it into Excel. This may sound odd, but the process may help clear out any corruption that may exist in the internal pointers used by Excel.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11653) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Forcing Stubborn Recalculation.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Got an add-in that you don't want loaded each time that Excel starts up? Here are a few ways that you can exclude it.
Discover MoreHate to take your fingers off the keyboard? Here's how you can move from worksheet to worksheet without touching the mouse.
Discover MoreAll the rave these days seems to be displaying information in either "light mode" or "dark mode." If you are interested ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-07-11 06:13:42
Nicholas Kulkarni
Hi Allen, interesting tip. Been troubleshooting some workbooks with problems and this could be very useful.
However this is not our only problem.
We have an Access Database application that manages the workbooks. We have noticed a failure to update links to source no matter what flavour of the F9 Recalculate key is used.
In particular we find that a field pointing to a source that links to another source will not consistently update. We have also found that links to a source workbook work when the Excel workbook is opened from one part of the application but fails when called from another part of the application. Looking at the code I suspect it is due to the way it is being "called". I fairly certain that one part of the application uses a UNC path and the other uses a mapped drive.
Found this on MS
https://support.microsoft.com/en-gb/topic/description-of-link-management-and-storage-in-excel-46628e8d-2cd6-db5f-3474-f8d7144b09d6
(relevant Text from this below my signature)
Your thoughts on this would be greatly appreciated and could be the source of another tip.
Cheers
Nick
Mapped drives vs. UNC
When a source data workbook is linked, the link is established based on the way that the workbook was opened. If the workbook was opened over a mapped drive, the link is created by using a mapped drive. The link remains that way regardless of how the source data workbook is opened in the future. If the source data file is opened by a UNC path, the link does not revert to a mapped drive, even if a matching drive is available. If you have both UNC and mapped drive links in the same file, and the source files are open at the same time as the destination file, only those links that match the way the file was opened will react as hyperlink. Specifically, if you open the file through a mapped drive and change the values in the source file, only those links created to the mapped drive will update immediately.
The link displayed in Excel may appear differently depending on how the workbook was opened. The link may appear to match either the root UNC share or the root drive letter that was used to open the file.
Scenarios that may cause links to not work as expected
There are several circumstances in which links between files can be inadvertently made to point to erroneous locations. The following are two of the most common scenarios.
Scenario 1:
You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.
You create links to a workbook that is stored at the mapped location after you open the file through that mapped drive.
You open the file by a UNC path.
As a consequence the link will be broken.
If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the links with the current broken path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\Server\Folder1. In other words, the Share name is eliminated from the path.
Scenario 2:
You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.
You open the file by a UNC path or a mapped drive mapped to a different folder on the share, such as \\Server\Share\Folder2.
As a consequence, the link will be broken.
If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the links with the current broken path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\Server\Folder1.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments