Monday, March 8, 2010

Format Numbers : Excel Currency Format

You can add currency symbol to your numbers in excel.

Open Number Format dialog box using Format Cells option or just press Ctrl+1

1. Click Accounting
2. Choose currency symbol from drop down

Format Numbers : Excel Currency Format



In case you do not get your currency symbol from drop down, add your symbol using custom formating option in the same dialogbox
1. Click Custom
2. Type - "Rs "#,##0_);[Red]("Rs "#,##0)

I use "Rs " to show numbers in Indian Currency. You can replace it with your symblol.

Format Numbers : Excel Currency Format

Posted by Yogesh Gupta at 10:08 PM
Labels: , ,
Reactions: 

8 comments:

  1. If I am not mistaken the Rs. symbol will be there as per International settings in Control Panel.

    Also http://www.dq.winsila.com/tips-tricks/an-excel-addin-to-show-currency-in-indian-format-and-words.html is a great resource to have.

    Regards,

    Ninad.

    PS.: BTW, your posts at PHD are welcomed.

    ReplyDelete

  2. Hi,

    How can I sum the numbers in a column in Excel where some numbers carry a "<" sign in front of them (such as <1) and I want to include numbers such "<1" in calculation as "1".

    For Example if numbers are entered as follows

    0.5
    <1
    0.5

    I would like to value to be returned as "2" when I apply "sum" function.

    Thanks

    ReplyDelete

  3. Hi Vijay

    Excel does not recoganise numbers with "<" sign as numbers and will not include them in calculation. You will need to remove them before getting them included into calculation.

    Some simple steps to remove them quicly are :-

    1. Select the cells or entire range with numbers having "<" before them.
    2. Press Ctrl+H
    3. write < in find value
    4. Leave replace value as blank and press replace all button

    You will have this removed from all the numbers and excel will recoginse them as numbers that can be used by formulas.

    I hope this helps, let me know in case any further help

    Regards//Yogesh Gupta

    ReplyDelete

  4. @Vijay, Yogesh,

    You can use this formula to perform the summation (just change the range to encompass your cells...

    =SUMPRODUCT(1*SUBSTITUTE(A1:A1000,"<",""))

    ReplyDelete

  5. Thanks Rick - I would have never found it.

    Thanks Yogesh for a helpful site.

    ReplyDelete

  6. @Yogesh,
    Pl let me know how can i get the accounting type display format through programming in C#.Net.
    I using your Yogesh.ExcelXml so pl let me know according this asap.

    Thanks
    Anil

    ReplyDelete

  7. DEAR YOGESH JI
    WISHING YOU HAPPY NEW YEAR

    Regards

    Harish Nebhwani , Sultanate of Oman

    ReplyDelete

  8. I WANT TO USE DOLLARS ( VALUE) instead of Rupees ( VALUE )

    Also Need password for your Module to edit.

    ReplyDelete

Subscribe to: Post Comments (Atom)