Trace Precedents | Remove Arrows | Trace Dependents | Show Formulas | Error Checking | Evaluate Formula
Formula auditing in Excel allows you to display the relationship between formulas and cells. The example below helps you master Formula Auditing quickly and easily.
Press DEBUG: The above message is not very informative for the an Excel user! Corruption Case Study A workbook template, which was created in Excel 2003 and was later converted to 2007.xlsm format, was sent to us by Gary.
Trace Precedents
- When I open the sheet on the windows computers, on the top, under the tool bar is a yellow line, which says, “Enable Macros.” Once I click on that button, I'm able to edit the spreadsheet and use all the features the creator made for the sheet.
- How can I activate the Debug Mode in Excel 2016 Mac? I have the Developer Tab but when opening the Visual Basic Editor can't see any debugging menu so that I can run the macros and debug them.
You have to pay $96.00. To show arrows that indicate which cells are used to calculate this value, execute the following steps.
1. Select cell C13.
2. On the Formulas tab, in the Formula Auditing group, click Trace Precedents.
Result:
As expected, Total cost and Group size are used to calculate the Cost per person.
3. Click Trace Precedents again.
As expected, the different costs are used to calculate the Total cost.
Remove Arrows
To remove the arrows, execute the following steps.
1. On the Formulas tab, in the Formula Auditing group, click Remove Arrows.
Trace Dependents
To show arrows that indicate which cells depend on a selected cell, execute the following steps.
1. Select cell C12.
2. On the Formulas tab, in the Formula Auditing group, click Trace Dependents.
Result:
As expected, the Cost per person depends on the Group size.
Show Formulas
By default, Excel shows the results of formulas. To show the formulas instead of their results, execute the following steps.
1. On the Formulas tab, in the Formula Auditing group, click Show Formulas.
Result:
Note: instead of clicking Show Formulas, press CTRL + ` (you can find this key above the tab key).
Error Checking
To check for common errors that occur in formulas, execute the following steps.
1. Enter the value 0 into cell C12.
2. On the Formulas tab, in the Formula Auditing group, click Error Checking.
Result. Excel finds an error in cell C13. The formula tries to divide a number by 0.
Evaluate Formula
To debug a formula by evaluating each part of the formula individually, execute the following steps.
1. Select cell C13.
2. On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.
3. Click Evaluate four times.
Excel shows the formula result.
A client contacted me, complaining of Excel spreadsheets that were taking far too long to open. They use Excel for creating invoices, so they have hundreds of Excel spreadsheets with light formatting and very simple calculations. When sorting the spreadsheets by size, I saw that while most of the spreadsheets ranged from 10-250k, there were a handful of spreadsheets with filesizes of 2-3 MB and more. Oddly, the filesizes weren't huge, they aren't holding much data, just a bit of formatting, maybe two or three pages of printed invoices, but they amount of data was almost identical to the smaller sized (and normal opening) spreadsheets.
When opening the file, the amount of RAM needed would spike from 3 MB to 400 MB, and it would completely occupy a single core (tested on a dual-core in the office, and my quad-core laptop), while starting. I thought that they had somehow caught some VBA code, but there are no macros, no VBA code. Ctrl+End shows 39 rows and about 12 columns (ends at M). I've even deleted the data, row by row, or column by column, until there is no data left, and it still gives me the same problem.
I've reviewed plenty of Google searches, but I've gotten nowhere. Can anyone offer any help?
6 Answers
I've found the answer to my problem!
Using the clues given to me by allquixotic, I opened the xlsx files in 7-Zip, and compared the file sizes. There was one file that was much larger than the others. The file xldrawingsdrawing1.xml was a file that contained multiple references to formats, over and over again.
I spent a few hours trying to figure out a pattern, but wasn't able to. Nothing I was able to do could get it working! Then, after a fit of aggravation, I just deleted the damn file, and tried to reopen in Excel (2010- didn't test it in Excel 2007).
It complained that the file was damaged, and asked if I'd like to attempt a repair. Repairing the file simply erased the shape, but didn't change the formatting of the file at all. I had to resave the file as the same file, which was a bit odd, but it worked!
As I'd mentioned before, I hadn't tested this solution for any products other than Excel 2010, so I don't know if the shape file was critical to Excel 2k7 or any OpenOffice products. But, if you have a similar problem, hopefully this could be helpful.
Are you using the binary .xls format or the new XML-based .xlsx format? In general the .xlsx format results in a dramatic reduction in filesize.
Check for things like an excessive number of styles stored in the document.
Try to 'remove personal information' (a feature of Excel/Word/etc.) to clean up certain kinds of cruft that might be sitting in the file.
If the spreadsheet is, or has previously been shared, it may have old share data stored in it.
One simple fix is to copy and paste just the relevant data from that spreadsheet to a new one, then save it in .xlsx format, and see how small it is. If it's very small then you have your answer -- Excel is doing poor accounting of its internal file format's data structures.
Also check for complicated or circular formulas and references to external sheets (especially those on network drives). If you think a formula might be slow, you can step through it using the formula auditing tool.
Last thing: if you save it as an .xlsx and it's still big, try downloading the OpenXML SDK Productivity tool: http://www.microsoft.com/en-us/download/details.aspx?id=5124
Open up the .xlsx in that and just take a look at all the elements within the file and see if anything is obviously extraneous. This may require knowledge of XML and of the specific OpenXML schemas, but it's a surefire way to find out what is causing the bloat.
P.S. -- if this kind of thing annoys you, stop using Microsoft formats / programs or suggest to your customer to do so. If you google around for 'Microsoft Access database bloat' you'll see that Microsoft has a long history of letting their proprietary formats leak tons of useless data to disk that never gets cleaned up. It's like a really nasty memory leak that eats your disk instead of your RAM.
allquixoticallquixoticAnother possible solution would be:
Debug Excel File
- Create a copy of the problematic Excel file
- Open that copy
- Press CTRL+A and then click on 'Clear' -> 'Clear Formats'
- Repeat step 3 on every worksheet
- Save the file and try to reopen it
When there is too much formatting, especially if there are many different styles applied to many individual cells, Excel really struggles trying to apply formatting when opening files.
Naturally, when you remove all formatting you'll end up with just text with default style applied to it in all of the cells with borders, shading, etc gone. But this way you can pinpoint the cause of the problem.
RusI
RusIFor a dramatic reduction in size you might try the format .xlsb. Should reduce more than any other XML-based format that excel provides.
Open one of those spread sheets and take a look at the format. Look for anything usual. Try to copy the entire column to another excel spreadsheet and paste values only (with no formatting whatsoever). See if it helps.
TwirlMandarinTwirlMandarinExcel Debug Mode
Excel Debugging Tool
In my case the problem was related to 'Format as table'. The table had 16000 empty columns. I guess this is one of the most frequent reasons for this kind of problem.
ReviousReviousI had a look at all the conditional formatting rules, in the whole worksheet, and there were loads. I went through and cleared them all, and then created the ones I actually needed, and now it is really fast.Also got rid of all the 'Connections' I didn't need, and save as .xlsb after it all worked well, and it is even faster now :o)