 |
 |
| About MrExcel |
| Consulting Services |
| Learn Excel Resources |
| Challenge of the Month |
| MrExcel Seminars |
| MrExcel Store |
| Podcast |
| Search |
| Media |
| Contact |
| Home |
 |
Leo Laporte, Bill Jelen, and Kate Abraham on the set of The Lab on TechTV Canada. The tip in this show is from Learn Excel from MrExcel. Say that you've inherited this workbook from a co-worker. You want to learn about formulas in the workbook.
Highlight all Formula CellsSelect all cells with Ctrl+a
In Excel 97-2003, use Edit - GoTo - Special. In Excel 2007, Home - Find and Select - Go To Special. In the Go To Special dialog, choose Formulas. Click OK.
Ctrl+B to change formulas to bold. Choose Red from the font color dropdown.Result: all formulas are in red.
See all Formulas at OnceThere is a grave accent on your keyboard. It is usually just below the F1 key. It looks like a backwards apostrophe. Hold down Ctrl and press the grave accent. You will see all formulas:
Press the key again to return to normal mode.
Use Trace PrecedentsChoose a formula and click Trace Precedents. It is on the Formulas ribbon in Excel 2007 and the Tools - Formula Auditing menu in Excel 97-2003. Excel will draw blue arrows to show all the cells that flow into this cell.
The icon in the lower left means there were some off-sheet precedents. Double-click the dotted line to see the off-sheet precedents.
Cool trick - click the Trace Precedents again to see the precedents of the precedents.
Eventaully, you see that just about every cell in the worksheet flows into this cell!
Tracing Dependents – which other cells rely on this cell?The opposite of tracing precedents is Trace Dependents. Select an input cell, choose Trace Dependents and you will see the cells that refer to the input cell. Click the Trace Dependents icon a few more times and you will see how the input cell flows through the worksheet.
Evaluate Formula – learn how to evaluate your formulas in slow motionThis command is new in Excel 2003. It allows you to see the formula get calculated one step at a time. The command is under Tools - Formula Auditing - Evaluate Formula in Excel 2003, and on the Formulas ribbon in Excel 2007. Select a cell and choose Evaluate Formula. The dialog shows the formula. One term is underlined. This is the term that Excel will evaluate next. You can either evaluate that term or Step In. In this image, I Stepped In to see the formula in E30. I've evaluated the first term of the stepped in formula and am about to evaluate the second term. Once I see the result of E30, I will Step Out and keep calculating.
These tricks will help you to keep your large formulas under control in Excel.
To watch the segment:(segments are available 3-5 business days after the show airs)
This tip was originally published on June 22, 2007 and aired on TechTV in Canada and Australia on June 22, 2007. The permanent URL for this page is http://www.mrexcel.com/tip147.shtml. If you are looking for show notes from another episode, visit my complete list of TechTV appearances. Check out the home page for The Lab with Leo.
MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.