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: 

16 comments:

  1. NinadMarch 9, 2010 at 11:43 AM

    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. VijayApril 18, 2010 at 7:51 PM

    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. Yogesh GuptaApril 18, 2010 at 8:59 PM

    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. RickApril 18, 2010 at 9:34 PM

    @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. VijayApril 19, 2010 at 5:29 AM

    Thanks Rick - I would have never found it.

    Thanks Yogesh for a helpful site.

    ReplyDelete
  6. * * @nil jain * *January 9, 2013 at 2:56 PM

    @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. harishJanuary 11, 2013 at 3:50 PM

    DEAR YOGESH JI
    WISHING YOU HAPPY NEW YEAR

    Regards

    Harish Nebhwani , Sultanate of Oman

    ReplyDelete
  8. Aatmik MuchhalOctober 7, 2013 at 5:40 PM

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

    Also Need password for your Module to edit.

    ReplyDelete
  9. AMIT CHOKSIAugust 2, 2014 at 10:04 AM

    Sir,
    How to Convert number into gujarati language ,
    So please give me function to conver into gujarati words. please give module.

    ReplyDelete
  10. Arvind Kumar VermaJune 12, 2016 at 7:40 PM

    Dear all,
    I want to subtract from 5/13/2016 1:20:39 AM to 5/13/2016 12:00:00 AM and result is in min

    ReplyDelete
  11. I am HappyJune 23, 2016 at 9:46 AM

    Help needed...

    I exported a file to Excel. I know how to format the cells and change the currency. However, I can't seem to format the cells to remove nor change the "PhP" sign on all the numbers I have here:

    PhP1,295,679.22
    PhP1,521,356.25
    PhP79,200.00
    PhP2,594,885.60
    PhP124,637.25
    PhP39,000.00
    PhP163,800.00
    PhP9,000.00

    All I want is to remove the "PhP" sign. Please help!

    ReplyDelete
  12. I am HappyJune 23, 2016 at 9:51 AM

    Help needed...

    I exported a file to Excel. I know how to format the cells and change the currency. However, I can't seem to format the cells to remove nor change the "PhP" sign on all the numbers I have here:

    PhP1,295,679.22
    PhP1,521,356.25
    PhP79,200.00
    PhP2,594,885.60
    PhP124,637.25
    PhP39,000.00
    PhP163,800.00
    PhP9,000.00

    All I want is to remove the "PhP" sign. Please help!

    ReplyDelete
    Replies
    1. Avinash kumarDecember 31, 2017 at 11:05 AM

      Use substitute formula
      =Substitute (cell,"PhP","")

      Delete
  13. UnknownDecember 23, 2017 at 6:08 AM

    I want to remove $ value & keep the number value intact How to do

    ReplyDelete
  14. BURHANApril 17, 2019 at 4:52 PM

    thanks for the tips and information.currency converter i really appreciate it..

    ReplyDelete
  15. Video TeravelokaOctober 10, 2019 at 12:41 AM

    Shalom, Om swastiastu, Namo buddhaya, Salam kebajikan, pada artikel kali ini kami akan memberikan kepada kamu Panduan Cara Bet 2D Togel Klik4D Online yang tersedia di S128cash. Berhubung permainan togel adalah game legendaris di Indonesia, tahukah kamu kalau game togel sekarang bisa dimainkan secara online ? (Baca Selengkapnya Disini...)

    ReplyDelete

Subscribe to: Post Comments (Atom)