How to change currency format in Excel? How to have Pakistani Rupees (Rs) currency sign in excel?

In almost every office of the world when it comes to money accountants come to excel. Although excel can handle money matters but when it comes to currency by default we have Dollar ($) as currency and not every country have this currency nor have the same currency sign. Accountants who are new often struggle with this fix.

To solve this problem we have to approaches:

• Temporary / limited to just one workbook
  • The easier way
  • The not so needed way
• Permanent / for every excel workbook

Permanent way

This involves changing the currency format on windows (operating system) level and that will push every application running on windows to use the currency symbol you so select.

Step 1: Click start button and head to control panel

Step 2: Click Clock Language and Region

Step 3: Just under Region and Language click Change the date, time or number format

Step 4: In the format tab from format drop down menu find and select Urdu (Islamic Republic of Pakistan). Note: this will change each and every bit regarding days, dates, times and currency etc. If you want to change just the currency continue with step 5

Step 5: Click Additional settings button

Step 6: Select currency tab from the dialogue box that appear

Step 7: In the currency format field type Rs or whatever currency symbol you desire.

Bonus Tip:

In the same dialogue box you can make changes like:

• how decimals should be displayed in numbers. In different countries , (comma) is used to represent decimals and . (period or dot) is used to represent thousand’s separator.
• You can also change how numbers grouped i.e. thousands, millions etc or lacs, crores etc.

So make your desired changes and the same will have effect in Excel directly.

Temporary – Limited

By temporary and limited I mean that this effect will be temporary and limited to workbook. If you open another workbook then you will have to repeat these steps again.

Easier way is that you can choose from different currency format within Excel. Options are Rs and PKR. To use the built in feature You can follow any of the following ways:

Way 1: Using ribbon

Step1: In the Home tab > Number group > Click the drop down arrow with $ sign. On the menu you are presented with five options if you require any of these five then make your selection. If you currency you desire is not on the list, which is in our case as well, then click “More Accounting Formats…”

Step 2: In the dialogue box that appears click the symbol drop down menu and find “Rs (urdu)” or “PKR”. You will have to scroll slowly as list is a bit long and small movement makes the list to jump many lines.

Way 2: Using shortcuts

Step 1: Hit Ctrl+1, a shortcut to get the number format dialogue box up. You can also have it done by clicking the dialogue box icon at the bottom right corner of numbers group.

Step 2: Once the dialogue box is open from the left window click “Accounting”

Step 3: Same as step 2 in Way 1.

Custom number formatting – The not so needed way

Although the change can be brought easily using the methods discussed above but if for some reason the above provided solutions are not feasible then one can resort to custom number formatting feature of Excel. In the temporary method discussed above, the method actually involves changing the format of number in the cell. Remember that by changing format, most of the time you are only changing the look of the number but at the back end it is still old number. To know what exactly is stored in the cell after formatting the best way is to look at the formula bar having the cell selected in the editing mode.

To custom format cell, have the cell selected or activated and hit Ctrl+1. From a dialogue box in the left window select “Custom”. On the right side you can see three things:

• Sample: that gives the live preview of how number will appear as you change format
• Type: this is where you can type in your custom format. By default it is General which you can replace with your own format.
• List: Just under type field you have a list of predefined or already used custom formats. You can select from one of them if it suits you. Alternatively you can use one of the examples discussed below.

Examples: Custom Currency format

• “Rs ” 0 – Simply put the Rs symbol before amount at left of the number. Negative numbers will show up with minus sign before Rs symbol
• “Rs ” 0;”Rs ” (0) – Adds Rs symbol to numbers and negative numbers are displayed with round brackets around them
• “Rs ” 0.00;”Rs ” (0.00) – Adds Rs symbol to numbers. Numbers are shown correct up to two decimal places. Negative numbers are shown in brackets
• “Rs “* 0.00;”Rs “* (0.00) – Rs symbol added to number, however, symbol will be aligned left and number will be aligned right to the cell (just like accounting format). Numbers will be correct up to two decimal places and negative numbers are shown in brackets.
• “Rs “* 0.00_);”Rs “* (0.00) – Rs symbol added to number, however, symbol will be aligned left and number will be aligned right to the cell. By adding “_)” to positive numbers we ensured that even positive number take the same space in cell as negative numbers. Because of brackets negative numbers takes more space and because of that decimals don’t align properly with positive numbers. So we added a padding (space) to the right of positive numbers equal to one bracket i.e. “)”, Numbers will be correct up to two decimal places and negative numbers are shown in brackets.