0109 Formula Auditing Magic

Advanced Excel Crash Course Section 1: Ninja Shortcuts
4 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€91.73
List Price:  €128.43
You save:  €36.69
£78.50
List Price:  £109.90
You save:  £31.40
CA$135.42
List Price:  CA$189.60
You save:  CA$54.17
A$152.22
List Price:  A$213.12
You save:  A$60.89
S$133.77
List Price:  S$187.28
You save:  S$53.51
HK$781.89
List Price:  HK$1,094.68
You save:  HK$312.79
CHF 88.30
List Price:  CHF 123.63
You save:  CHF 35.32
NOK kr1,058
List Price:  NOK kr1,481.24
You save:  NOK kr423.24
DKK kr684.10
List Price:  DKK kr957.77
You save:  DKK kr273.66
NZ$164.15
List Price:  NZ$229.81
You save:  NZ$65.66
د.إ367.20
List Price:  د.إ514.09
You save:  د.إ146.89
৳10,978.23
List Price:  ৳15,369.96
You save:  ৳4,391.73
₹8,290.53
List Price:  ₹11,607.08
You save:  ₹3,316.54
RM471.80
List Price:  RM660.54
You save:  RM188.74
₦156,534.34
List Price:  ₦219,154.34
You save:  ₦62,620
₨27,950.66
List Price:  ₨39,132.05
You save:  ₨11,181.38
฿3,595.26
List Price:  ฿5,033.51
You save:  ฿1,438.25
₺3,229.78
List Price:  ₺4,521.83
You save:  ₺1,292.04
B$499.40
List Price:  B$699.18
You save:  B$199.78
R1,883.20
List Price:  R2,636.56
You save:  R753.35
Лв179.42
List Price:  Лв251.19
You save:  Лв71.77
₩133,286.20
List Price:  ₩186,606.01
You save:  ₩53,319.81
₪365.03
List Price:  ₪511.06
You save:  ₪146.02
₱5,561.09
List Price:  ₱7,785.75
You save:  ₱2,224.66
¥14,911.76
List Price:  ¥20,877.07
You save:  ¥5,965.30
MX$1,670.32
List Price:  MX$2,338.52
You save:  MX$668.19
QR364.82
List Price:  QR510.77
You save:  QR145.94
P1,359.04
List Price:  P1,902.71
You save:  P543.67
KSh13,398.66
List Price:  KSh18,758.66
You save:  KSh5,360
E£4,718.65
List Price:  E£6,606.31
You save:  E£1,887.65
ብር5,656.15
List Price:  ብር7,918.84
You save:  ብር2,262.68
Kz83,512.74
List Price:  Kz116,921.18
You save:  Kz33,408.44
CLP$93,810.03
List Price:  CLP$131,337.80
You save:  CLP$37,527.76
CN¥710.61
List Price:  CN¥994.89
You save:  CN¥284.27
RD$5,917.87
List Price:  RD$8,285.25
You save:  RD$2,367.38
DA13,435.55
List Price:  DA18,810.31
You save:  DA5,374.76
FJ$226.69
List Price:  FJ$317.37
You save:  FJ$90.68
Q780.94
List Price:  Q1,093.35
You save:  Q312.40
GY$20,949.18
List Price:  GY$29,329.69
You save:  GY$8,380.51
ISK kr13,659.63
List Price:  ISK kr19,124.03
You save:  ISK kr5,464.40
DH1,002.67
List Price:  DH1,403.78
You save:  DH401.11
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,652.42
List Price:  ден7,913.61
You save:  ден2,261.19
MOP$805.88
List Price:  MOP$1,128.27
You save:  MOP$322.38
N$1,866.55
List Price:  N$2,613.25
You save:  N$746.69
C$3,681.10
List Price:  C$5,153.70
You save:  C$1,472.59
रु13,266.49
List Price:  रु18,573.62
You save:  रु5,307.13
S/368.83
List Price:  S/516.39
You save:  S/147.55
K382.06
List Price:  K534.91
You save:  K152.84
SAR375
List Price:  SAR525.02
You save:  SAR150.01
ZK2,508.19
List Price:  ZK3,511.57
You save:  ZK1,003.37
L456.04
List Price:  L638.48
You save:  L182.43
Kč2,310.02
List Price:  Kč3,234.13
You save:  Kč924.10
Ft36,211.46
List Price:  Ft50,697.50
You save:  Ft14,486.03
SEK kr1,038.11
List Price:  SEK kr1,453.39
You save:  SEK kr415.28
ARS$85,066.49
List Price:  ARS$119,096.49
You save:  ARS$34,030
Bs691.23
List Price:  Bs967.75
You save:  Bs276.52
COP$389,029.38
List Price:  COP$544,656.70
You save:  COP$155,627.31
₡50,979.09
List Price:  ₡71,372.77
You save:  ₡20,393.67
L2,470.17
List Price:  L3,458.33
You save:  L988.16
₲730,160.41
List Price:  ₲1,022,253.79
You save:  ₲292,093.37
$U3,841.23
List Price:  $U5,377.88
You save:  $U1,536.64
zł395.31
List Price:  zł553.46
You save:  zł158.14
Already have an account? Log In

Transcript

Hello everyone, today we learn about some of the formula auditing tricks. If ever you are working with a file which has been handed over to you, you want to start with a certain ground with certain knowledge prerequisite knowledge before you can give your own inputs. For example, if you are given this simple model, which talks about sales growth, assumptions, how how the cost behaving in the upcoming years? And based on that, based on those assumption, how are the sales cost and profit numbers being generated? Now, either you double click on these cells one by one and find out how the formulas are playing internally, and how are they calculating the end profit, or else if you want to get a quick glance of how are they moving, you may go to Formulas tab. And there's something on the right hand side called show formula.

Notice there's a shortcut associated with show formulas to so first I click the button show formulas. What does it do? It quickly shows you all the forms We'll add a single glance on the entire sheet. If you click on the same button, once again, it goes back to normal, the same thing you could have achieved by using these two keys on the keyboard, one is control, and the other is called back the key which is reciting just immediately above the tab key. So I'm going to press those shortcut keys together, control and backtick key, you get the same effect. I use this quite often before I start working on any file that has been given over to me by my colleague or by my clients.

So there goes the first trick, show all formulas. Next, this is another trick which shows you how are the formulas flowing from which sources? So if I keep my cursor on this particular cell, and I say trace precedents, it shows me in graphical arrows form, what are the numbers that are helping getting this answer on board. And in fact, if you want to go beyond the one level precedents, you may again check the cell and double click on trace precedents. Similar If I select one particular cell and I say double click on trace dependence, notice it shows dependence dependence. So depends on how many times you click single click or double click.

If you don't want to show these arrows, you may simply say remove arrows. And in fact, if you pick up any cell who uses another cell from a different sheet, then how does it get shown up? I say trace dependence. Okay, there is no dependent. This must be the case for PC Dennis. Let me select the cell and double click on trace precedents.

Okay, it shows precedent and precedents, precedents. And there's one window like sign, which tells me that some cell has been referred from differentiate, I double click on this dotted line, and it tells me that this is the cell. Let me select that and press OK. Look, I'm taking to another sheet which is called test sheet. So that was the button based exercise trace precedents and trace dependence If you want a similar thing using a shortcut key, you as well, me refer to these couple of shortcut keys, which I'm going to use right now. For example, I choose the cell. And I'm going to press the shortcut key control third bracket, open this one.

So control third bracket open, it quickly chooses one level of precedents, which I can readily make it bold by using Ctrl V and Ctrl u for underlying. Similarly, if I place my cursor on the screen cell, and I paste the same shortcut key Ctrl third bracket open, it takes me to one cell previous to it. Now, point number three is, if I had referred to only one cell from a test sheet, let's say in this case, 62. And I use Ctrl third bracket open the same shortcut key. It takes me there, but now the question is, how do I come back? I will press f5 f5 is activating the goto box and automatically the origin where you came from that has already been posted here.

So all Introduce, press enter. So right now what did I use, I use the shortcut Ctrl third bracket open. That is to go to the source. And then if you want to return back, then you use this shortcut key that is f5 and enter. So basically it's going to work like this. I can do a third bracket open and a five and end up with like moving back and forth.

Similarly, if you want to trace all the precedents, not just one level using a shortcut key, then you start using these two shortcut keys for precedents and dependents respectively. So ctrl shift toward bracket. Yep, it chooses all the cells. If I color them, you find out which cells are they pointing to. So these are a couple of shortcut keys, which helps you audit the entire sheet using quick navigation shortcut keys.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.