Excel Quick Tips

1. How to create dynamic names in Excel?

What if I want to write P&L : Company name and when I change the company name the P&L : remains same but th company name changes ?

This is required because we use a single model for many companies and situation so rather than manually doing this everytime, why not create a automatic formula. 

The formula is ="P&L:"&"cell reference of the company name".

2. What are the Custom formatting means ?

1. Positive Numbers 

2. Negative Numbers 

3. How to display 0s

4. How to display text 

For example : # #; [Red](# #); -

So in this what will happen the positive number will be having space in thousands, then the second which we separated by ";" will show in red and brackets with space in thousands, then the third in place of 0 "-" will show and for the last we left it as nothing because we were working on numbers. 

3. Creating a Dropdown or List in excel !

Creating a dropdown is easy just go on Data tab then >> Data Validation and select List, it will be that easy. 

But when you create a list you will not be able to overwrite the Listed cells, how to stop that ? To stop that you need to go again on Data Validation and move on to the stop or alert tab that would be third and then untick the error alert. 

4. How to create a list of headings ? 

the function says Alt + A + T

5. How to remove Green Referrences ? 

Go to File >> Options at the end of the left hand side. 

Then go to formulas and in Error Checking, uncheck the Enable background error checking