How to Use a Dollar Sign in SharePoint List Calculated Columns
Question details
The user needs to know how to properly display a dollar sign in a SharePoint List calculated column or how to resolve syntax errors when a dollar sign is used inside the formula.
- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Creating or modifying a calculated column formula in a SharePoint List.
- Observed behavior
- Using a dollar sign directly in the formula text box triggers a syntax error, as it does not function as an absolute reference operator in SharePoint.
Determine if your goal is to display a monetary value with a currency symbol, or if you mistakenly used a dollar sign attempting to lock a cell reference like you would in Excel.
Format the Calculated Column Output as Currency
If your goal is to display a monetary value, use SharePoint's built-in column formatting instead of adding a dollar sign into the formula text.
SharePoint handles currency display at the column configuration level rather than within the mathematical formula itself. Concatenating a dollar sign manually converts your number to text, which prevents you from using that column in future calculations.
Navigate to your SharePoint List, click the gear icon in the top right corner, and select 'List Settings'.
Scroll down to the 'Columns' section and click on the name of your calculated column to edit its properties.
In the section labeled 'The data type returned from this formula is', choose 'Currency'.
Pick your desired currency symbol (e.g., $ English) and specify the number of decimal places, then click 'OK' to save.
Remove Dollar Signs Used for Absolute References
SharePoint formulas do not support Excel-style absolute references ($). You must remove them to fix syntax errors.
Manage Complex Formulas Easily with WPS Spreadsheet
While SharePoint lists are useful for basic web-based tracking, handling complex formulas and absolute references is much easier in a dedicated spreadsheet program. WPS Office provides a free, lightweight alternative to Microsoft Office, offering full support for traditional formula syntax, including the use of dollar signs ($) for absolute references.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your device.
- 2. Open WPS Spreadsheet: Launch WPS Spreadsheet and open your exported data or create a new workbook.
- 3. Use Standard Formulas: Type your formulas naturally, utilizing standard Excel-style absolute references (e.g., $A$1) and currency formatting.
Frequently Asked Questions
Can I use an absolute reference in a SharePoint calculated column?
No, SharePoint calculated columns do not support Excel-style absolute references (using the $ symbol). Calculations are strictly performed using other columns within the exact same row.
How do I combine text and a dollar sign in a SharePoint formula?
If you absolutely must output a text string that includes a dollar sign, use the concatenation operator (&). For example, use the formula: ="$" & [ColumnName].
Why do I get a syntax error when adding a dollar sign to my SharePoint formula?
A syntax error occurs because SharePoint does not recognize the dollar sign as an operator within mathematical formulas. If you are trying to format the output as money, remove the dollar sign from the formula box and change the column's return data type to Currency.
