How to write amounts in Lakhs and Crores (Indian Format)?

Have you ever wondered as to how to write amounts in lakhs and crores instead of millions and billions ie 10,00,00,000 format.
Its quite simple :
Right Click the cell you want to format >> format >> Number >> Click Custome (last item on the left hand side Menu).Now type one of the below syntax formats in the "Type:" Text box >> Click OK.
Without decimal : 99,99,99,999[>=10000000]#","##","##","###;[>=100000]#","##","###;#,###
With decimal : 99,99,99,999.00[>=10000000]#","##","##","##0.00;[>=100000]#","##","##0.00;#,##0.00
And now you get amounts in Indian currency format.

INFO function

=Info() can be used to get information about the location of the file, information about the OS, excel version, about the memory status etc....

Here are some examples:
=info("directory") Path of the current directory or folder.
=info("memavail") Amount of memory available, in bytes.
=info("memused") Amount of memory being used for data.
=info("numfile") Number of active worksheets in the open workbooks.
=info("origin") Absolute A1-style reference, as text, prepended with "$A:" for Lotus 1-2-3 release 3.x compatibility. Returns the cell reference of the top and leftmost cell visible in the window, based on the current scrolling position.
=info("osversion") Current operating system version, as text.
=info("recalc") Current recalculation mode; returns "Automatic" or "Manual".
=info("release") Version of Microsoft Excel, as text.
=info("system") Name of the operating environment:
=info("totmem") Total memory available, including memory already in use, in bytes.

Where Can it be used?
=info("directory") function can be used to get the path of the file in your reports.
=info("recalc") can be used to check whether the calculation status is 'Manual' or 'Automatic'.

Now there are other ways to get most of the above information, this formula is one more option that you have.

Some Text functions

We will see some Text functions which can be very useful:
1. TEXT() :
This functions formats a number and convert it to Text. For E.g., Cell A1 is having date 1/1/2009. We can convert this date to text in whatever format we like; e.g., TEXT(A1,"MMDDYYYY). The result will be 01012009. Likewise we can convert a number to text like 100 to 100.00 (Text(A1,"0.00"). Note that you may have to specify the decimals.
2. TRIM():
Trim function is used to remove extra spaces from text. Syntax: TRIM("Hello "). Result will be "Hello".
3. LEN():
LEN function is used to know the length of a text or character. It will count the spaces too.
4. UPPER():
This function is used to convert a text to UPPER case.
5. LOWER()
This function is used to convert a text to LOWER case.

When I open a workbook it prompts me to enable macros; although there are no macros in the file. Why?

Whenever Macros are recorded in a workbook, Modules get created. These modules house the VBA Codes. So whenever Excel detects any module in a workbook it prompts you to enable macros even though there might not be any macros in he file.
Example: Sometimes we try our hand on Macros and then later delete these macros. Even though there are no Macros, Excel gives an Enable Macros prompt on opening the file because the file still houses the Module(s) (including Class Module(s) if any) which have not been removed. So if you are wondering why this popup when there are no macros? All you have to do is go to the VBA Screen (Alt+F11) right click the Module(s) and delete them; this will get rid of the Enable Macros prompt.
Note : Some people get the impression that this popup appears for macros have been used on the workbook in past. However this is not true as running macros from personal.xls or an addin or any other file will neither create VBA code (in sheets or modules) nor will it create new modules. Hence mere running of macros ona file will not result in ‘Enable Macros prompt’ when you open them.

Phantom Links

What are phantom links?
A Phantom link is a link which does not exist and hence can not be updated. Most of the phantom links arise when someone sends you a workbook which is linked to other workbooks to which you do not have access. It's called a phantom link ‘cos the reference does not exist and hence can not be updated.
It will also arise when you change file name of one of the linked workbooks. The file which contained link can not find the old file name, making the link phantom. In such case excel prompts you to update the link.

Unhide all sheets thro Macro

You would have noticed that we cannot select all the worksheets from the Unhide options. This becomes tougher when we have unhide multiple sheets. We have to choose each sheet and then unhide them. Easiest way to unhide all the sheets is to use following VBA code:
'-------VBA Code---------------
Sub Unhide()
'Unhide all sheets macro by Saalim
Dim wkst As Worksheet
For Each wkst In Worksheets
wkst.Visible = xlSheetVisible
Next wkst
End Sub
'------------Code End----------------
This way we can save a lot of time on manually unhiding the sheets.

-Saalim

Web Query

Most of the people wonder if they can download a web page to Excel worksheet. It is absolutely possible with a web query. Here are the steps to do that:
1. Data >> Import External Data >> New Web Query.
2. In the resultant window input the webpage address from which you want to update the rate and click go.
3. Once the page is loaded, Check the fields (having green arrow marks) that contain the rates and click import.
4. The data is now imported into excel.
5. Now all you have to do is refresh this query. To do this right click the area where the data has been imported and you have various options including refresh. (I usually prefer to have a macro for this...)
6. There are other settings which you can access by right clicking the data area and selecting 'Data Range Properties'.

-Saalim

Default No. of Worksheets

Do you know that we can change the default no. of worksheets in a new workbook from to any numbers?

To do this:
1. Go to Tools
2. Select Options
3. Go to General Tab
4. Here you can specify the no. of sheets for a new workbook. (default should be 3)

Hope this helps!!

-Saalim

Change in my blog contents

I have decided to upload some useful stuffs and will try to share my knowledge on MS Excel from now onwards..... :-)