Excel does its best to help you avoid and fix formula errors. One of the ways it does that is to display indicators and messages that flag and describe the problem. In this video, learn how to interact and manage formula error indicators.
- [Instructor] Excel includes a number of terrific tools that you can use to ensure your formulas include the proper values. One of those tools gives you the ability to determine which cells are used in which worksheet formulas. I will show you how to trace precedents and dependents in your formulas in this movie. My sample file is the Tracing workbook, and you can find it in the Chapter Five folder of the exercise files collection. Cells can play one or two roles in a formula, either as a precedent or as a dependent.
Displaying a cell's precedents indicates which other cells provide the formula's inputs, while displaying its dependents shows which other cells use the selected cell's value as an input. You can identify those cells using tracer arrows. To do that, select the cell that contains the formula you want to analyze, in this case, I already have cell D3 selected. Go to the Formulas tab on the ribbon, and then, to display the precedents, that is the input values for a formula, in the cell that you've selected, click the Trace Dependents button, and you can see here that the cell range of B4 through B15, provides the values for the sum of all inquiries.
You can also trace a cell's dependence, in other words, are there any cells in our worksheet that use the value in cell D3, as input for its formula? Well, with cell D3 still selected, I will click the trace dependents button, and we can see that both the formulas in D6 and D9 use the value from cell D3. If you want to identify precedents or dependents other than the worksheet, or perhaps, in another workbook, then you can do that.
In this case, I happen to know that the cost to replace a 10% loss, which is the formula in cell D9, uses a value from another worksheet. If you look at the formula bar, you can see that it uses a value from cell B1 on the Acquisition Cost worksheet. So, with cell D9 selected, if I click Trace Precendents, then, I see the previous cells, which I identified earlier, and I also see that I have an external reference, and that could be another workbook, it can be another worksheet.
You can tell your mass pointer is in the correct position when it changes to an upward pointing arrow, upward and to the left. Click the dashed line, and that displays the Go to dialogue box. Select the reference you want to go to, in this case, there's only one. Click Okay, and you will see that reference. Let's switch back to the Summary worksheet. When you want to get rid of tracer arrows, you can do that by going to the Remove Arrows button.
Clicking the Remove Arrows button by itself will remove everything, or you can click its down arrow, and only remove precedents or dependents. In this case, I just click Remove Arrows to remove them all from my worksheet.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks
Skill Level Beginner
What you should know1m 11s
1. Getting Started with Excel
2. Managing Workbooks
3. Working with Worksheets, Cells, and Cell Data
4. Sorting, Filtering, and Managing Worksheets
5. Summarizing Data Using Formulas and Functions
6. Formatting Worksheet Elements
7. Working with Charts
8. Working with External Data and Objects
9. Exploring PivotTables
10. Reviewing and Sharing Spreadsheets
Further information1m 2s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.