Thursday, July 12, 2007

Excel Tip

I found out this awesome trick in Excel. Check it:

If you get a workbook from someone, you can figure out what formulas they used by selecting all the cells (ctrl+A), then going to "Edit - GoTo - Special". There you click "Formulas" and "ok" and this selects the cells with formulas. You can now make those cells have red or bold font! If you click the backwards apostrophe (under the tilde), it will even show all the actual formulas.

Here's where it gets crazy. If you go to "Tools - Formula Auditing" and click "Trace Precedents", Excel will draw a blue line from all the input data cells to the destination cell, even if the input data is from a different workbook!

Now you know how the formulas work in your new workbook of data.


Ang said...

Good to see you've gotten beyond copy, paste and print.

graham said...

I assume this has nothing to do with your civil trial underway?

Anonymous said...

You can also delete all the spreadshee data -- non-formula and non-text content -- with a $30 Excel add-on (Google: 'Excel numbers delete remove' [without the quotes]) or with a little custom function (and Excel's "Conditional Formatting").