An understanding of currency formatting is essential for navigating the complexities of financial data in Excel and producing reports that are professional and easy to read. An important function of decimal places is to control the level of detail in the quantities that are shown. To ensure accuracy and industry compliance, currency values are often shown with two decimal places in the financial sector.
Currency formatting in Excel is a useful skill that is necessary for accurate financial reporting. There are a few ways to easily format currency with 2 decimal places. In this Excel tutorial, you will explore 4 such ways.
In the following image, you have an employee dataset consisting of Designation, Years of Service, and Salary. The Salary figures have been converted to Currency format with 2 decimal places. Let’s learn how.
How to Format Currency with 2 Decimal Places in Excel
You can follow 4 ways to format currency with 2 decimal places: using Number Format drop-down, Format Cells dialog box, Increase/Decrease Decimal option, and employing VBA macro.
Using Number Format Drop-down
This section illustrates a quick way to apply currency format with 2 decimal places in Excel using the Number Format drop-down. Here’s how:
- Select your data range.
- Go to Home tab > Number group > Number Format drop-down.
- Choose Currency. Note: Alternatively, you can click the ‘$’ icon to convert the numbers to Accounting format to show currency with your number.
As a result, the numbers in the Salary column will be converted to the currency format with 2 decimal places as below.
Using Format Cells Dialog Box
By default, the Currency format contains two decimal places. But sometimes you may see that the currency format values do not contain 2 decimal digits. In that case, you have to modify the Currency format using Format Cells dialog box. Here’s how:
- Select the data range that you want to modify.
- Go to Home tab > Number group > Dialog Box Launcher. You can also press Ctrl+1 keys. The Format Cells dialog box will appear.
- In the Format Cells dialog box:
- Go to Number tab > Currency category.
- Adjust the Decimal places box to 2.
- Click OK.
As a result, 2 decimal places will be added to the cells in Currency format.
Using Increase/Decrease Decimal Option
You can increase or decrease the decimal places of Currency format using a built-in feature of Excel called Increase Decimal or Decrease Decimal. But, remember, you will need to convert the numbers to Currency format beforehand. Then, to increase/decrease the decimal places of currency format in Excel, follow these steps:
- Select your data range.
- Go to Home tab > Number group > Increase Decimal or Decrease Decimal.
Thus, the decimal places of your data will be adjusted accordingly.
Applying VBA Macro to Add 2 Decimal Places
In this section, you will learn how to automate the currency formatting along with 2 decimal places using VBA macro in Excel. Here’s how:
- Go to Developer tab > Code group > Visual Basic or press Alt+F11 keys. The VBA window opens.
- Select Insert tab > Module.
- Insert 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 the Save icon.
The Save As window appears. - Select Excel Macro-Enabled Workbook for Save as type > Save.
- Go back to the workbook and select your data range.
- Go to Developer tab > Code group > Macros > FormatCurrencyWithTwoDecimalPlaces macro > Run.
You will see the following result after running the code.
Download Practice Workbook
You can download the practice workbook from below:
Conclusion
In conclusion, you have explored 4 methods to format currency with 2 decimal places in Excel: utilizing the Number Format drop-down, Format Cells dialog box, Increase/Decrease Decimal option and employing VBA. By utilizing these methods, raw numerical data can be transformed into a refined and professional currency format, as illustrated in the overview image with our employee dataset.
Frequently Asked Questions
Will formatting currency affect the underlying numerical values in Excel?
No, formatting currency with 2 decimal places does not alter the actual 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’s built-in features like custom cell styles or employ VBA macros to automate the formatting process.
Is there a keyboard shortcut for formatting Currency in Excel?
Yes, you can use the keyboard shortcut Ctrl+Shift+4 to quickly apply the default Currency format to the selected cells.
<< Go Back to Currency Format | Number Format | Learn Excel