How to Remove Decimal Places from a SharePoint Calculated Column
Question details
The user needs to remove unnecessary decimal places from a SharePoint calculated column that returns a week number.
- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Formatting a calculated column in a SharePoint list to display a whole number instead of a decimal.
- Observed behavior
- The calculated column displays week numbers with unwanted decimal places.
Ensure you have the necessary list permissions in SharePoint to edit column settings and update calculated formulas.
Adjust the Number Format in Column Settings
The simplest way to remove decimals is by changing the number format directly within the calculated column's configuration settings.
If your calculated column is set to return a number, SharePoint allows you to specify exactly how many decimal places to display without altering the formula.
Navigate to your SharePoint list, click on 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.
Look for the 'Number of decimal places' option and change it from 'Automatic' to '0'.
Click 'OK' at the bottom of the page to save your changes and return to the list view to verify the week numbers.
Use the TEXT Function in Your Formula
If you prefer the column to return a text string or if the standard number formatting options are unavailable, you can wrap your formula in the TEXT function.
Manage and Format Your Spreadsheets with WPS Office
While SharePoint handles online lists, complex data formatting and calculations are often easier to manage in a dedicated spreadsheet application. WPS Office provides a free, lightweight, and fully compatible alternative to Microsoft Excel for offline and online data processing.
- 1. Download WPS Office: Download and install the free WPS Office suite from the official website.
- 2. Import SharePoint Data: Export your SharePoint list to a spreadsheet and open it using WPS Spreadsheet.
- 3. Format Cells: Select the column, right-click to choose 'Format Cells', and easily set decimal places to zero under the 'Number' tab.
Frequently Asked Questions
Why does my calculated column default to showing decimal places?
When a calculated column is set to return a 'Number' data type, SharePoint often defaults the decimal places setting to 'Automatic', which frequently displays trailing zeros for whole numbers (e.g., 2.00 instead of 2).
Can I use the INT function to remove decimals in SharePoint?
Yes, you can wrap your formula in the INT function (e.g., =INT([YourFormula])) to round the result down to the nearest whole number. This will effectively strip any decimal values from the output.
Why did changing the decimal places to zero in settings not work?
If the data type returned from the formula is set to 'Single line of text' instead of 'Number', the numerical decimal places setting will not apply. You must either change the return type to 'Number' or use a formatting formula like the TEXT function.
