Excel General Formula Troubleshooting Tips for Excel

[alow]

If you have a formula that is new to you, or a cell that's full of nested formulas giving you an error, there are a couple things you can do to see where the problem actually is. If you have Excel 2007 or later, you can select the cell with the error, then go to the Formula Tab and click on Evaluate Formula. This will step through the formula in the order Excel calculates it, so you can see what might be causing the problem. It will underline the part of the formula to be calculated first, and you can click on Evaluate to calculate that portion. This will go through what Excel does in less than a second step by step, and will give you the error at the step causing the problem.

If you don't have a newer version of Excel, or you want to try this manually, you can use the formula bar for a similar process. If you highlight a part of a formula in the formula bar, you can press F9 to calculate just that part. This will either give you an error or a value, depending on what you highlight.

I know this is a mouthful, just trying to get some general help out there.

Enjoy!

 

[muckshifter]

... a great tip.


I made this a "stickie" for a few days, if you want to add some more, please do, if not, I'll un-stick the thread at a later date.

 

[pjmorris]

Thats a great tip. My thought would be to suggest using names for cells rather than addresses. For those cells with 'absolute' addresses this is a much more reliable method than using the name.

 

[Becky]

Excellent tip!

 

[pjmorris]

Ooops, just re-read my post - the final sentence should have said:

"For those cells with 'absolute' addresses this is a much more reliable method than using the address."

Another thought, though apologies its not strictly formulae based. When producing spreadsheets for others to populate identify cells that they can change with a light coloured background, unprotect those cells and then protect the entire sheet. Set the protection options such that they can only navigate to unprotected cells and you've immediately prevented the users messing with formulas and causing problems!!

Things would be much easier without users!!

Regards

Peter

 

[alow]

If you wanted to do that, to change whether a cell is "locked" or not, you would select the cells that you want to be able to edit while the sheet is protected, then right click on one of the selected cells and click on Format Cells. In the Format Cells window, go to the Protection tab and uncheck the "Locked" box. This box is what determines which cells will be affected by worksheet protection and is turned on by default. After you have unlocked the cells you would like to be able to edit, you would go to the Review tab and click on Protect Sheet (in earlier versions, it's Tools | Protection | Protect Sheet). In this window, you want to make sure that the checkbox at the top is checked, and then you can check the boxes in the bottom to achieve the desired level of protection you are looking for. You also have the option of adding a password if you don't want someone to be able to unprotect the sheet and edit it in an undesired way.

Just thought I'd add some instruction for protecting a sheet, good addition!

 

[Vold]

Excellent tip!

Thx Becky
Vold

 

[MikeyS]

Indeed. Thank you.