WPS Spreadsheet: Display a Value and Its Change in One Cell
Question details
The user needs to display a current numeric value together with its increase or decrease in one visible cell.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains an original value and a current value, and the user wants a combined display such as $150 (+$50) without changing the underlying numeric cells.
- Observed behavior
- The combined result must be displayed as text in a separate cell because a single cell cannot simultaneously contain concatenated formatted text and remain a numeric value.
Identify the cells containing the original value and the current value, such as A1 and B1. Reserve a separate result cell, such as C1, because the combined display formula returns text.
Use a TEXT formula in a separate WPS Spreadsheet cell
Use this method when you need to preserve the original numeric values for calculations while showing the value and its change together.
The formula formats the current value and calculates the difference between the current and original values. The result cell is text, while the source cells remain numeric and usable in calculations.
In WPS Spreadsheet, select the empty cell where the combined result should appear, such as C1.
Type `=TEXT(B1,"$0")&" ("&TEXT(B1-A1,"+$0;-$0;$0")&")"` in the formula bar or directly in C1, then press Enter. Here, A1 is the original value and B1 is the current value.
Select the cell containing the formula and drag its fill handle downward, or use Home > Fill > Down, to apply the same pattern to additional rows.
Edit the TEXT format codes in the formula to use decimals, percentages, or another currency, such as `"$0.00"` or `"0.0%"`.
Create combined value-and-change displays in WPS Spreadsheet
WPS Spreadsheet lets you combine formatted values and calculated changes with TEXT and concatenation formulas, while preserving the original numeric cells for sorting, calculations, and analysis.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the original and current values.
- 2. Choose a result cell: Click an empty cell beside the source data, such as C1, to hold the combined display.
- 3. Enter the formula: Enter `=TEXT(B1,"$0")&" ("&TEXT(B1-A1,"+$0;-$0;$0")&")"`, replacing A1 and B1 with the appropriate source cells.
- 4. Confirm and copy: Press Enter to create the display, then drag the fill handle or use Home > Fill > Down for additional rows.
Frequently Asked Questions
Can the same cell remain numeric after showing text such as $150 (+$50)?
No. A cell containing concatenated text is treated as text. Keep the original and current numbers in separate cells and use another cell for the combined display.
Which cells does the formula use for the value and change?
In the example, B1 is the current value and A1 is the original value. The difference is calculated with B1-A1, so replace these references with the cells used in your worksheet.
How can I show cents in the combined result?
Change the current-value format from "$0" to "$0.00" and the difference format from "+$0;-$0;$0" to "+$0.00;-$0.00;$0.00" inside the TEXT functions.
How do I display percentages instead of dollar amounts?
Use percentage formats in both TEXT functions, for example `=TEXT(B1,"0.0%")&" ("&TEXT(B1-A1,"+0.0%;-0.0%;0.0%")&")"`, assuming A1 and B1 contain percentage values.
