Formatting Issues

How to Change Excel Decimal Places Based on a Reference Cell

Kushani Nimanthika Oct 22, 2026 868 views

Question details

The user needs to dynamically adjust the number of decimal places displayed for values in specific ranges based on a numerical value entered in a reference cell.

How to Change Excel Decimal Places Based on a Reference Cell

Product: Excel
Device & OS: not provided
Scenario: Designing a dynamic spreadsheet or form where precision requirements (such as 0 to 3 decimal places) change automatically based on user input in a designated control cell.
Observed behavior: Standard number formatting is static by default. A dynamic method is needed to link the decimal formatting of multiple ranges, and potentially multiple worksheets, directly to a single reference cell's value.

Before you start

Identify the exact reference cell (e.g., D11) that will dictate the decimal places, and clearly outline the specific data ranges that require dynamic formatting.

Solution 1Recommended

Use Conditional Formatting to Adjust Decimal Places

Best for simple scenarios where decimal places range from 0 to a small fixed number without needing to write code.

You can create custom conditional formatting rules that evaluate the reference cell. By applying different custom number formats for each possible value of the reference cell, the selected range will update automatically.

Select the target data range

Highlight the cells or ranges (e.g., C29:C38) where you want the decimal places to change.

Create a new formatting rule

Navigate to the Home tab, click on 'Conditional Formatting', and select 'New Rule'. Choose 'Use a formula to determine which cells to format'.

Enter the control formula

In the formula box, enter the logic for zero decimal places, such as `=$D$11=0` (assuming D11 is your reference cell).

Set the custom number format

Click 'Format', go to the 'Number' tab, select 'Custom', and type `0` to represent zero decimal places. Click OK to save.

Repeat for additional decimal places

Create additional rules for other decimal counts: `=$D$11=1` with custom format `0.0`, `=$D$11=2` with `0.00`, and `=$D$11=3` with `0.000`.

Use Conditional Formatting to Adjust Decimal Places

Cross-Sheet Compatibility: To apply this logic across different worksheets, reference the control cell by including its sheet name in the formula, such as `=Sheet1!$D$11=0`.

Solution 2

Apply VBA Macros for Dynamic Decimal Formatting

Ideal for complex workbooks with multiple control cells or when formatting rules span extensive ranges and varied worksheets.

Advanced Spreadsheet Editor

Dynamically Format Numbers with WPS Spreadsheet

WPS Spreadsheet fully supports custom conditional formatting rules and robust VBA/Macro environments, allowing you to easily build highly dynamic forms and reports that adjust decimal places based on cell inputs.

1. Open your workbook: Launch WPS Spreadsheet and open the file where dynamic decimal formatting is needed.
2. Apply formatting rules: Select the target cells, navigate to 'Home' > 'Conditional Formatting', and use formula-based rules to assign custom decimal displays.
3. Utilize VBA tools: For more advanced logic, access the 'Developer' tab to write and run Worksheet_Change event macros seamlessly.

Fully compatible with Microsoft Excel (.xlsx) formatsSupports advanced conditional formatting with cross-sheet formulasBuilt-in VBA/Macro support for automated formatting tasksLightweight, fast, and free to use

microsoft office alternative - wps office

Frequently Asked Questions

Can I reference a cell on another worksheet for conditional formatting?

Yes. You can include the sheet name in your conditional formatting formula, such as `=Sheet1!$D$11=2`, to successfully link formatting rules across different worksheets.

Why is my conditional formatting custom number format not working?

Ensure that the reference cell in your formula is locked with absolute references (like `$D$11`). If relative references are used, the formula will evaluate incorrectly for other cells within your selected range.

How do I handle multiple reference cells using VBA?

Combine them into a single `Worksheet_Change` event. Use separate `If Target.Address = "$C$3" Then` and `If Target.Address = "$G$3" Then` conditional blocks within the same procedure to handle each specific reference cell independently.

Document & File Issues