File specific actions for improving excel performance – microsoft community gas vs electric oven

#

If your file size mainly comes from a big amount of formulas and text in cells (and not pictures etc.), this format can compress your file heavily. With such a text based file, saving it in the .XLSB format could result in a file size of less than 50%, opening/saving

(It is also possible, but not that likely, that you have Hidden (or even Very Hidden) sheets with excessive Shapes. Pressing Alt+F11 and pressing F4 will reveal what the “Visible” property is set to, for the sheet that you click on in the dropdown list

If you see a sheet with several hundred shapes or a lot more shapes than you would expect, it makes sense to reduce the number of shapes to check if it makes any difference for the file performance. To delete all shapes, clicking “Go To Special…” in the

(If there are so many shapes to be found in the Selection Pane that you cannot select and delete them, one possible workaround is always to first close Excel, then rename the file from .XLSX to .ZIP in Windows, open the .ZIP file, open the subfolder XL,

open the folder Drawings, and delete the big files in there. This will cause Excel to repair the file. Please note that this will also delete other shapes, and may have the undesired side effects of data loss as you may lose valid objects too, or other content,

The default for new Excel files is just 47 styles. Therefore, if you in this dropdown can see a big mess with what looks like a huge amount of duplicates, probably some add-in must have added them. This can cause performance issues. It could be a good idea

The button shows how many Styles there are in the file at the moment. As seen above, there were 55429 Styles. After clicking that button, then saving and reopening the file (important), we now only see the Microsoft Built-in Styles, which are 47 in total,

(You may also want to choose "Options" and "Look in: Workbook" to look at the whole workbook at the same time.) Since Excel formulas (usually) start with an equal sign, this will find you all formulas. If you can see that they are referring to a full

column, they may be slower than needed. Instead of simply referring to something like “A:B”, you can refer to something like “A1:B5000” instead. This may sound like a small change, but this 2nd reference is actually 200 times smaller than the 1st (since Excel

You might think that you just asked it to use 20 rows, and you indeed did, but you also asked it to use every column of those rows, and in e.g. XLSX format, that means 16384 columns. See above how a seemingly simple table stretches all the way to the right

Even if you don’t have a table stretching all the way down or to the right, the Last Cells in your sheets may be much further to the right and/or down than you would expect. This can be checked by pressing Ctrl+End in each sheet. Read more about how to reset

If your file has a huge amount of CFs listed under “Home” tab – “Conditional Formatting” – “Manage Rules”, it may be very slow to work in. You can use the roll bar at the top with the arrow keys to go through your sheets. In the picture below, we can see a

If you notice any delay whatsoever when moving between different sheets in the "Show formatting rules for:" dialogue in the screenshot above, this is an indication that you should clean them up. Redesigning these to have much fewer rules, and each of them referring

to a full range instead of individual cells, can provide a very big performance difference. It may be some work to redesign this, so you may want to test “Home” tab – “Conditional Formats” – “Clear Rules” – “Clear Rules from Entire Sheet”, just to test that

(Wait, how did my file get thousands of Conditional Formats? This can happen if you Copy and Paste excessively. Most people don’t consider that when they copy paste information, they may also copy CFs and other formatting. If you have experienced

this: “When you copy and paste cells that have conditional formatting set in Excel 2016, the conditional formatting rules are duplicated even though the rules already exist in the cells” with a reg key called ReplaceCFOnPaste mentioned in this article that you

-Changing the calculation mode from Automatic to Manual under Formula – Calculation Options, and then trigger it manually with e.g. F9 only when needed in this workbook, can also provide the breathing room that your file needs for becoming acceptable to work

Usually, broken links don’t cause issues with file performance, but it can definitely happen and you may still want to clean them up to improve the file health. To clean them up, you can check your “Data” tab – ”Edit Links” dialogue, and check for links

As seen in "Location", what is selected is a link pointing to a local C:\ drive. This may be intentional if you are the only one working on this file, but usually it is not intended and when the file is sent to another computer, the linked file will not exist.

As seen, there are very many names that have “#REF!”, a reference error, in the “Refers to” column. These are very likely not in use any more, and should be deleted/corrected. Even ones that only have “#REF!” as part of the “Refers To” column should be deleted/corrected.