You may need to display numbers with more or fewer decimal places in your Excel sheets. This is controlled by number formatting, which changes how a value looks without altering its underlying value. This article explains how to adjust decimal places using the ribbon, keyboard shortcuts, and the Format Cells dialog.
Key Takeaways: Controlling Decimal Places in Excel
- Home > Number group > Increase/Decrease Decimal: Quickly adds or removes one decimal place from selected cells.
- Ctrl+1 (Format Cells dialog): Opens the full menu for creating custom number formats with precise decimal control.
- Custom Number Format code (e.g., 0.0 or #,##0): Defines exactly how many decimals to show, including conditional hiding of zeros.
Understanding Excel Number Formatting
Number formatting in Excel is a display rule applied to a cell. It does not change the actual number stored in the cell, only how it appears. For example, the value 123.456 can be shown as 123, 123.5, or 123.46 depending on the format. The most common formats for decimal control are General, Number, Currency, Accounting, and Custom. The General format shows numbers as you type them, while the Number format lets you set a fixed number of decimal places. The Currency and Accounting formats are similar but add a currency symbol. The Custom format gives you complete control using special codes.
How Formatting Affects Calculations
Excel always calculates using the full, stored value, not the formatted display. If cell A1 contains 1.456 and is formatted to show 1.5, a formula like =A1*2 will use 1.456 and return 2.912, which may then be displayed as 2.9 based on its own formatting. This ensures accuracy in your data analysis.
Methods to Adjust Decimal Places
You can change decimal places using quick buttons, keyboard shortcuts, or detailed dialog boxes. The method you choose depends on whether you need a simple, one-click change or a permanent, custom format.
Using the Home Tab Ribbon Buttons
This is the fastest way to increase or decrease visible decimals for the Number, Currency, or Accounting formats.
- Select the cells
Click and drag to select the cell or range of cells you want to format. - Go to the Home tab
On the ribbon, find the Number group. - Click the decimal buttons
Click the Increase Decimal button (with a right-pointing arrow and .0) to add one decimal place. Click the Decrease Decimal button (with a left-pointing arrow and .0) to remove one decimal place. Each click adjusts the display by one place.
Using the Format Cells Dialog
This method offers more precision and access to all number format categories.
- Select your cells and open the dialog
Select the cells and press Ctrl+1. You can also right-click the selection and choose Format Cells. - Choose a number category
In the Format Cells dialog, click the Number tab. Select a category like Number, Currency, or Accounting from the list on the left. - Set the decimal places
On the right, you will see a Decimal places spin box. Use the up and down arrows or type a number to set the exact count of decimal places to display. A preview is shown at the top. Click OK to apply.
Creating a Custom Number Format
Custom formats are powerful for conditional display, like hiding decimal places only if a number is whole.
- Open the Format Cells dialog
Select your cells and press Ctrl+1. - Go to Custom category
Click the Number tab and select Custom from the category list on the left. - Enter or edit a format code
In the Type field, you will see the current format code. To always show two decimal places, use the code 0.00. To show a minimum of one decimal but up to three if needed, use 0.0##. To hide decimals for whole numbers, use a format like #,##0.##. This shows up to two decimals only if they are non-zero. Click OK to apply.
Common Mistakes and Limitations
Adjusting decimals is simple, but a few pitfalls can cause confusion in your data presentation.
Formatting Does Not Round the Stored Value
If you use the Decrease Decimal button to show 1.23 as 1.2, the cell still contains 1.23. Sums may appear incorrect because the displayed numbers do not add up to the displayed total. The total is calculated with the full precision. To actually round the value, you must use the ROUND function.
The Increase Decimal Button Shows Trailing Zeros
Clicking Increase Decimal on the value 5 will show 5.0, then 5.00. These are zeros added by the format. If you want to avoid showing decimals for whole numbers, you need a custom number format like #,##0.## instead of the standard Number format.
Pasting Values Can Overwrite Formats
If you copy a cell with a specific number format and paste it using Paste Special > Values, you paste only the number, not the format. The pasted value will adopt the destination cell’s existing format. To copy the format, use Paste Special > Formats or the Format Painter tool.
Quick Buttons vs. Format Cells Dialog
| Item | Home Tab Decimal Buttons | Format Cells Dialog (Ctrl+1) |
|---|---|---|
| Speed | Fast, one-click changes | Slower, requires dialog navigation |
| Precision | Changes by one decimal per click | Set exact decimal count directly |
| Access to Formats | Limited to current format type | Full access to all number categories |
| Custom Format Creation | Not available | Full control with custom codes |
| Best For | Quick, visual adjustments | Permanent, precise formatting setup |
You can now control exactly how many decimal places appear in your Excel data. Use the Increase Decimal and Decrease Decimal buttons on the Home tab for quick fixes. For permanent and precise control, use the Format Cells dialog with Ctrl+1. Try creating a custom number format like #,##0.0_);(#,##0.0) to align positive and negative numbers with one decimal. This format also adds a space equal to a parenthesis for proper alignment.