Welcome to the seventh post of this series – Select Precedents and Dependents. Hope you are enjoying this series. Click here to read to navigate to go to special.
What are Precedents and Dependents?
As we all know that a result of a formula or a function is derived using a single or a set of variables and / or constants. In short, result of a formula is an outcome of one or more constants or variables. Precedents are the inputs used to get the result. Dependent is the result itself. What does it mean? The active cell must be a formula. Yeah, I know, that’s obvious. But I wanted to mention..Just in case…
Say for example, A = 1, B = 2 and C = A+B = 1+2 = 3.
In this example, A and B are the Precedents. The value of C is dependent on A and B. Hence C is dependent.
Isn’t it quite possible that A and B are also dependent on some other variables and or constants? Yes, you got that right! It is quite possible. That is why Excel gave us an option of selecting Precedents and Dependents that are Direct only and All Levels.
First things first
When you have to find the precedents / dependents you need to first select the cell to which you need to select precedents or dependents.
Example
Below is the data you can download to excel and play around with this options. I have also shown the formulae used in Table 3 and Table 4.
Prec-Depen
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Table 1 | ||||||
| 2 | Sales | Amount | |||||
| 3 | Product 1 | 10000 | |||||
| 4 | Product 2 | 15000 | Table 3 | Table 4 | |||
| 5 | Profit | Amount | Total Profit | ||||
| 6 | Product 1 | 2000 | 10000 | ||||
| 7 | Table 2 | Product 2 | 8000 | ||||
| 8 | Cost of Goods Sold | Amount | |||||
| 9 | Product 1 | 8000 | |||||
| 10 | Product 2 | 7000 |
Worksheet Formulas
|
I used the above example for easiness. Total profit is calculated for a company that sells two products (Product 1 and Product 2). We have sales for both the products in Table 1 and cost of goods sold in Table 2.
Most of us know that profit is Sales minus cost of goods sold. Using this formula I calculated profit for both the products in Table 3. Direct only Precedents for Table 3 are Table 1 and Table 2 as we are using Sales from one table and cost of goods sold from another table to arrive at the profit for each product. There are no precedents to Table 1 and 2. Hence, all levels precedents and direct only precedents are same for Table 3.
Last step, Table 4 is total of profit calculated for each product i.e. total profit of the company. Direct only precedent for table 4 is table 3 as total profit is the sum of profit of the two products. All levels precedents are Table 1, 2 & 3 like you see in the above visual.
For Product 1 sales (In Table 1) and cost of goods sold (In table 2) direct only precedent is Product 1 profit (In Table 3). All levels dependents are Product 1 profit (In Table 3) and total profit (In Table 4). Same is the case with Product 2.
Note: Selecting Dependents and Precedents via Go to Special will not enable you to select the referenced cells from different worksheets. This will only select the cells referenced from active worksheet. Only exception is read the Keyboard shortcut for direct only select precedents.
Keyboard Shortcuts:
1. Select Precedents:
a. Direct Only: Ctrl + [ or Press F5, Alt + S + P (Direct Only is by default selected). This shortcut will take to a cell referenced from a different worksheet. But the limitation is, if we have multiple cells, it only selects the first referenced cell. Say for example, Sheet1! A1 = Sheet2! A1 + Sheet2! B1 then this will only select cells Sheet2! A1. Instead, if Sheet1! A1 = SUM(Sheet2!A1:B1) then it selects both the cells.
b. All Levels: Ctrl + Shift + { or Press F5, Alt + S + P + L
2. Select Dependents:
a. Direct Only: Ctrl + ] or Press F5, Alt + S + D (Direct Only is by default selected).
b. All Levels: Ctrl + Shift + } or or Press F5, Alt + S + D + L
One limitation is we will not be able to find the dependents from a different worksheet. When you try to select dependents from a different sheet, annoyingly, it says “No cells were found”.
Alternative to Selecting Precedents and Dependents:
Yes, there is an alternative to selecting precedents and dependents. It is through formula auditing. We have trace dependents and precedents. Honestly, I prefer formula auditing method to selecting precedents and dependents. The reason is that we talked about the limitation of select precedents and dependents above. This can be easily performed using trace precedents and dependents.
Navigation
Excel 2003: Toolsà Formula Auditing. Then select on trace dependents or precedents as required.
Excel 2007: Select Formulas on the ribbon. Under the formula auditing toolbar click on trace dependents or precedents as required.
Keyboard shortcut:
Trace Precedents: Alt + T + U + T
Trace Dependents: Alt + T +U + D
Note: When you click once on Trace Precedents or Trace Dependents you will see Direct Only precedents / dependents. Click twice on trace precedents or dependents to see All Levels of precedents / dependents.
When you trace precedents or dependents you will see arrows on the worksheet directed towards the dependents or precedents. When there is a dependent or precedent in a different worksheet all you need to do is trace precedent or dependent as required. An arrow is directed towards an object that looks like a spreadsheet. Double click on the arrow and you will see Go To screen with referenced cells from different worksheets. Select the reference that you want to select and double click or just click on OK button below.
A screenshot below portrays the above explanation. I used the same example to select precedents and dependents (with 4 tables). The only change I made is I kept Table 1 and Table 2 in a different sheet.
Keyboard shortcut to remove arrows: Alt + T +U + A
Both the options are useful and handy in excel. It is just that user needs to use the options to suit their requirements.
Share your experience:
Hope you are enjoying this series.
Do you use these options more often? Share your experiences, how do you deal with this type of data. Have another tip do this stuff? Also, do not forget to leave your comments on this post. I learn a lot from you all. I’ll be posting soon, eighth post of this series “Go to Special – Select Last cell”.
Links related to this series:
Thanks a lot. This was just what I was looking for.
ReplyDelete