The following image shows an employee dataset consisting of Designation, Years of Service, and Salary. The Salary figures were converted to Currency format with 2 decimal places.
Method 1 – Using the Number Format Drop-down
- Select your data range.
- Go to Home > Number > Number Format drop-down.
- Choose Currency. Note: Alternatively, you can click the ‘$’ icon to convert the numbers to Accounting format.
The numbers in the Salary column will be converted to currency with 2 decimal places.
Method 2 – Using the Format Cells Dialog Box
By default, the Currency format contains two decimal places. Modify the Currency format if it doesn’t show two decimal places using the Format Cells dialog box.
- Select the data range that you want to modify.
- Go to Home > Number > Dialog Box Launcher. You can also press Ctrl+1.
- In the Format Cells dialog box:
- Go to Number > Currency.
- Adjust the Decimal places to 2.
- Click OK.
2 decimal places will be added to the cells in Currency format.
Method 3 – Using the Increase/Decrease Decimal Option
Convert the numbers to Currency format beforehand.
- Select your data range.
- Go to Home > Number > Increase Decimal or Decrease Decimal.
The decimal places will be adjusted.
Method 4 – Applying a VBA Macro to Add 2 Decimal Places
- Go to Developer tab > Code > Visual Basic or press Alt+F11. The VBA window opens.
- Select Insert > Module.
- Use the code below:
Sub FormatCurrencyWithTwoDecimalPlaces() Dim rngCell, rng As Range Set rng = Selection For Each rngCell In rng rngCell.Value = Format(rngCell, "Currency") Next End Sub
- Click Save.
In Save As: - Select Excel Macro-Enabled Workbook in Save as type > Save.
- Go back to the workbook and select your data range.
- Go to Developer > Code > Macros > FormatCurrencyWithTwoDecimalPlaces > Run.
You will see the following output.
Download Practice Workbook
Download the practice workbook.
Frequently Asked Questions
Will formatting currency affect the underlying numerical values in Excel?
No, formatting currency with 2 decimal places does not alter numerical values. It only changes the visual representation of data.
Can I automate the process of formatting currency with 2 decimal places in Excel?
Yes, you can use Excel built-in features like custom cell styles or VBA macros to automate the formatting process.
Is there a keyboard shortcut for formatting Currency in Excel?
Yes, you can press Ctrl+Shift+4 to apply the default Currency format to the selected cells.
<< Go Back to Currency Format | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!