DOLLAR Function in Excel: Explained
In this article, you will learn about the DOLLAR formula and its uses in Excel.
What is the DOLLAR formula in Excel?
The DOLLAR function, belonging to the TEXT function family, transforms a number into text representation using currency format, with the option to round the decimal places as desired. DOLLAR employs the $#,##0.00_);($#,##0.00) number format, but the currency symbol applied will depend on the local language settings configured in your Excel.
Syntax of the DOLLAR function in Excel
The syntax of the Excel DOLLAR function is as follows:
number: (required) The input for the parameter can be either a numerical value, a reference to a cell that holds a number, or a formula that results in a numerical value.
decimals: (optional) The value specifies the number of decimal places to be displayed. If the value is negative, rounding occurs to the left of the decimal point. If no decimal places are specified, it is assumed to be 2.
How to use the DOLLAR function in Excel?
Understand the DOLLAR function better with the help of an example case. The below dataset contains computer accessories and their selling prices. The objective is to convert the numbers into text formatted as currency.
The syntax of the DOLLAR function is as follows:
number: (required) This argument requires a number, so cell B2 should be referred for Monitor.
decimals: (optional) We will let the function adapt the default setting, which is two decimal places; hence we ignore this argument.
The complete function would look like below:
We insert this function in cell C2 and press enter to generate the function result.
As you can see, the returned value is converted to text and formatted in currency format. You can use the Excel function to quickly copy and paste the formula to the remaining cells and view the result.
Note: The appearance of $ or other currency symbols is by default as per the regional format settings on your computer.
Look at another example where you need to convert the number to text and currency format with specified decimals.
Now as you can observe the conditions mentioned in the sample dataset, we shall insert the DOLLAR functions accordingly.
Two decimal places to the right of the decimal point :
Four decimal places to the right of the decimal point :
One decimal place to the right of the decimal point :
One decimal place to the left of the decimal point :
Three decimal places to the right of the decimal point:
Round off:
Once you apply the above functions to the dataset, you will obtain the following outcomes.
When should you use the DOLLAR function in Excel?
The DOLLAR function in Excel serves various purposes when you require number formatting as currency. Here are the key scenarios where the DOLLAR function proves helpful:
- Price or cost display: If you manage a product catalog, sales records, or pricing information, the DOLLAR function assists in displaying prices or costs in a currency format that users can easily understand. It ensures consistency and professionalism in presenting the values.
- Reporting and presentations: When creating reports, presentations, or dashboards involving monetary values, utilizing the DOLLAR function enhances the visual representation of the data. It signifies to the audience that the numbers represent currency amounts, thereby improving readability and comprehension.
- Financial statement formatting: Financial statements such as balance sheets, income statements, or cash flow statements require numbers to be formatted as currency. By utilizing the DOLLAR function, you can ensure that financial statements adhere to the appropriate formatting conventions.
Important note about DOLLAR formula in Excel
It's crucial to be aware that the DOLLAR function in Excel is primarily used for formatting purposes and does not perform currency conversion or handle exchange rates. It simply converts a numerical value to a text string with a currency symbol, thousand separators, and decimal places as specified.
It is recommended to utilize the Format Cells dialog (Ctrl+1) or the Home > Number > Accounting Number Format option when applying currency formatting to a cell. This is because the DOLLAR function returns the number as text. Storing numbers as text may lead to spreadsheet errors, as many functions, such as SUM, AVERAGE, MIN, MAX, etc., disregard text values. Use the DOLLAR function to convert numbers into text strings with currency symbols.
When using the DOLLAR function, it's important to ensure that the underlying data is in the correct numerical format. If you attempt to apply the DOLLAR function to a non-numeric value, it will result in a #VALUE! error. Therefore, make sure that the data you're formatting with the DOLLAR function is numeric
