- Excel includes a number of very useful tools that you can use to ensure your formulas summarize the proper values. One of those tools gives you the ability to determine which cells are used in which worksheet formulas. In this movie, I will show you how to use tracing arrows both to identify cells that provide values to another formula and to indicate which other cells use values from a given cell. This workbook contains visitor information that might be of use to our hotel owners. If you take a look at the formula in cell D3, you see that it's the Sum of Visitors and the values that it uses are the range B4 to B15, so these cells here.
If it were not clear which cells were used or if the cells were in a variety of different parts of the worksheet, then I might want to use tracer arrows to identify which cells provide values to the formula in D3. To do that, I can go to the Formulas tab of the ribbon and with cell D3 selected, go over to the right side of the ribbon tab and click Trace Precedents. Clicking Trace Precedents identifies which cells provide values for Sum of Visitors.
And you can see that there is a dot and it indicates that all of the cells within the blue bordered region provide values for the formula in D3. If you want to remove your arrow, you can go back to the Formula tab and click Remove Arrows. In the same way, you can display a cell's dependence which shows which other cells use the selected cell's value as an input. So in this case I'll click cell D3, here it is already clicked, and then again on the Formulas tab, I'll click the Trace Dependents button.
So I can see that cell D3 is used in the formula in cell D9. And I can see that that cell also uses the value in D6. Tracer arrows make it easier to identify from where the values used in a formula are taken, and they also indicate values in other worksheets or even in other workbooks. So let's say that I click cell D9 and then click Trace Precedents. You can see that I have the value from cell D3, but I also have a black line with a dot that indicates that there is another worksheet that contains a value used in this formula.
And if I click the line, double click it, you can see that the Go to dialog box appears. I'm given Tracing.xlsx and then Sheet2 as the reference. I'll go ahead and go to that and there is the value that is used in the other formula and that is the Cost to Acquire a New Visitor. Identifying a cell's precedence and dependence will help you discover and correct many formula inaccuracies. They can also help you understand the logic behind a formula if you're seeing it for the first time.
Author
Updated
2/18/2016Released
8/7/2015- Customizing the Ribbon
- Formatting worksheets, cells, and cell data
- Sorting and filtering data
- Working with formulas
- Detecting formula errors
- Creating charts
- Importing data
- Inserting objects and graphics
- Using PivotTables
- Recording macros
- Sharing workbooks
Skill Level Beginner
Duration
Views
Q: This course was updated on 02/18/2016. What changed?
A: We updated one tutorial, "Managing objects using the Selection pane." The new Selection pane, released in a January 2016 Office update, allows Excel for Mac users to more easily rearrange worksheet and slideshow objects.
Related Courses
-
Introduction
-
Welcome59s
-
-
1. Getting Started with Excel
-
Getting help in Excel3m 11s
-
2. Managing Workbooks
-
Setting workbook properties2m 45s
-
3. Working with Worksheets, Cells, and Cell Data
-
Creating named ranges5m 58s
-
Creating an Excel table5m 9s
-
4. Sorting, Filtering, and Managing Worksheets
-
Sorting worksheet data3m 6s
-
Creating a custom sort order4m 49s
-
Filtering worksheet data3m 55s
-
Managing worksheets5m 4s
-
-
5. Summarizing Data Using Formulas and Functions
-
Adding a formula to a cell3m 56s
-
6. Analyzing Data and Formulas
-
Rounding cell values4m 14s
-
Managing scenarios7m 1s
-
7. Formatting Worksheet Elements
-
Managing text alignment4m 46s
-
Copying cell formats3m 29s
-
Managing cell styles4m 10s
-
Managing Office themes5m 45s
-
8. Working with Charts
-
Creating pie charts2m 25s
-
Creating line charts3m 11s
-
Creating XY (scatter) charts2m 38s
-
Creating stock charts2m 42s
-
Adding trendlines to charts3m 31s
-
Creating sparkline charts4m 17s
-
9. Working with External Data
-
Using hyperlinks4m 18s
-
10. Working with Objects
-
Adding and adjusting images4m 58s
-
Manipulating text boxes3m 20s
-
Creating SmartArt graphics4m 19s
-
Creating WordArt2m 55s
-
11. Exploring PivotTables
-
Applying a PivotTable style2m 26s
-
12. Reviewing and Sharing Your Spreadsheets
-
Checking spelling2m 55s
-
Managing workbook comments3m 29s
-
Exporting to other formats2m 48s
-
Protecting a workbook3m 23s
-
-
13. Automating Workbooks Using Macros
-
Running an existing macro4m 31s
-
Recording a macro2m 46s
-
Adding comments to a macro2m 23s
-
-
Conclusion
-
Next steps1m 8s
-
- 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.
CancelTake 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.
Share this video
Embed this video
Video: Auditing formulas by identifying precedents and dependents