Great video, just a couple comments:- 1. With regards to hiding/unhiding the ribbon, all you need to do is double click one of the ribbon headers (File, Home, Insert, Draw etc). 2. Sometimes when you want the leading zeroes, it may be better to just format the cells, as "00000". The value will still be recorded as 158, but it will be displayed as 00158. This would eliminate the need for turning of "Enable background error checking", which I think is a very useful thing to have turned on when working with very large worksheets. 3. There should never be a need to use the "Enable iterative calculation", if you design the worksheet correctly. This can be a very dangerous option.
Great comments! Thank you. For point 3 iterative calculations are necessary for specific types of financial models that require a revolving credit facility. For example a 3 statement model or an LBO model
@@KenjiExplains Just for reference, I have worked in the financial sector (banks, fund/super management, auditing etc) for over 40 years, mostly as an Excel/Access Developer. In all that time I have never come across a file where I couldn't get rid of the circular formulae. Often, you require a little bit of algebra to work it out, but it can be done.
Excel 10 and older used to highlight active cell whether in active or inactive windows, then Microsoft took that away, and now it's marketed as a new, inelegant feature.
Can only use leading zero for text like identifiers. Easy to enter, just use the ' at the start of the cell. The warning sign is correct, the cell below are 'numbers' so any formula will not work correctly /consistently when filled down as the syntax and options available are different for text/numbers. The whole column should be changed to text.
@@richlawrence4160 - Agreed, but when you're dealing with a host of values that need to start with a 0, adding a ' manually is quite cumbersome. I didn't realise such an automated feature existed until I saw this!
This is just an example to show you how to activate it, but in real life it can be necessary for financial models like a 3 statement model or an LBO model that have a looping effect :)
Leading zeros isn't always a good idea, and removing the formula error (actually string indicator) even worse, cause if you happen to use any of these numbers Excel will throw an expected/unexpected error which you then have to fix.
👉 Get 25% OFF our Data Analyst Program this Black Friday: www.careerprinciples.com/career-track/the-data-analyst-program
Finally, we got valuable insights in Excel. Nice work, kenji
finally time they added focus cell to excel.
makes it so much easier for everyone to see what is being presented when working remote.
Totally agree!
Great video, just a couple comments:-
1. With regards to hiding/unhiding the ribbon, all you need to do is double click one of the ribbon headers (File, Home, Insert, Draw etc).
2. Sometimes when you want the leading zeroes, it may be better to just format the cells, as "00000". The value will still be recorded as 158, but it will be displayed as 00158. This would eliminate the need for turning of "Enable background error checking", which I think is a very useful thing to have turned on when working with very large worksheets.
3. There should never be a need to use the "Enable iterative calculation", if you design the worksheet correctly. This can be a very dangerous option.
Great comments! Thank you. For point 3 iterative calculations are necessary for specific types of financial models that require a revolving credit facility. For example a 3 statement model or an LBO model
@@KenjiExplains Just for reference, I have worked in the financial sector (banks, fund/super management, auditing etc) for over 40 years, mostly as an Excel/Access Developer. In all that time I have never come across a file where I couldn't get rid of the circular formulae. Often, you require a little bit of algebra to work it out, but it can be done.
Excel 10 and older used to highlight active cell whether in active or inactive windows, then Microsoft took that away, and now it's marketed as a new, inelegant feature.
I cannot believe the leading zero's one - Thankyou!
Can only use leading zero for text like identifiers. Easy to enter, just use the ' at the start of the cell. The warning sign is correct, the cell below are 'numbers' so any formula will not work correctly /consistently when filled down as the syntax and options available are different for text/numbers. The whole column should be changed to text.
@@richlawrence4160 - Agreed, but when you're dealing with a host of values that need to start with a 0, adding a ' manually is quite cumbersome. I didn't realise such an automated feature existed until I saw this!
Love the focus cell, TYVM!!!
the leading zeros is great thanks
Thanks for watching!
Hi Kenji - I've noticed if I "convert to number" first and then set the format for the leading zeros, I don't get the green triangle error.
Useful information and best part is to provide a tutorial task for learning
thanks for this. most of the tips here are for Excel desktop. any chance for an Excel web video?
I can't see the Data tab in the Excel Options box while using MS Excel 2016. How can I activate it?
Good practices. Thank you for this share.
Sir please make a playlist of Power Query Basic to Advance
Using table names in the formula is better and more advanced than using references. Don't change it.
Point 9 is confusing. Why we need to set one of the cells equal to the total?
This is just an example to show you how to activate it, but in real life it can be necessary for financial models like a 3 statement model or an LBO model that have a looping effect :)
Leading zeros isn't always a good idea, and removing the formula error (actually string indicator) even worse, cause if you happen to use any of these numbers Excel will throw an expected/unexpected error which you then have to fix.
Jesus loves you