WPS Spreadsheets: Display Negative Values on a Logarithmic Axis
Question details
The user needs to display negative-looking values on a logarithmic chart axis.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The chart requires a logarithmic axis, but the intended labels represent negative values.
- Observed behavior
- A standard logarithmic axis cannot plot zero or negative values because logarithms are undefined for those inputs.
Check that the values you will plot are all greater than zero, and decide how many decimal places the axis labels should show. If the source data is negative, prepare a helper column containing the corresponding positive magnitudes.
Plot positive values and format the logarithmic axis labels with minus signs
Use this method when the chart must remain logarithmic while the displayed labels need to look negative.
A standard logarithmic axis can only use positive numeric values. Plot positive magnitudes, then change the axis number format so each displayed label includes a minus sign; the underlying plotted values remain positive.
This changes the appearance of the labels only. It does not make zero or negative values valid on the logarithmic scale.
In the worksheet, enter positive values in the chart's source range. If the original values are negative, add a helper column and use a formula such as =ABS(A2), then fill it down and use that helper column for the chart.
Select the positive plotting range, choose the Insert tab, select a chart type such as Scatter, and click the chart to edit its axes.
Right-click the value axis, choose Format Axis, open Axis Options, and enable the Logarithmic scale option. Keep the minimum value greater than zero.
In the Format Axis pane, expand Number, choose Custom or enter a custom format code, and place the cursor in the Format Code field.
Enter a format such as `"-"0.00` for two decimal places, or `"-"0` for whole numbers, then click Add or Apply. The positive plotted values will be shown with minus signs on the axis labels.
Use a different axis scale for true negative values
Choose this option when the chart must preserve the actual numeric meaning and positions of negative or zero values.
Format Logarithmic Axis Labels in WPS Spreadsheets
WPS Spreadsheets lets you create a chart from positive magnitudes, enable a logarithmic axis, and apply a custom number format that displays the labels with minus signs while preserving the logarithmic scale.
- 1. Prepare positive data: Use positive source values or create a helper column with =ABS(cell) for the chart range.
- 2. Create the chart: Select the positive data, open the Insert tab, and choose a Scatter or other suitable chart.
- 3. Set logarithmic scaling: Right-click the value axis, select Format Axis, open Axis Options, and turn on Logarithmic scale.
- 4. Format the labels: Open Number in the Format Axis pane, choose Custom, enter `"-"0.00` or another suitable format code, and click Apply.
Frequently Asked Questions
Why can’t I enter negative values directly on a logarithmic axis?
Logarithms are undefined for zero and negative numbers, so a standard logarithmic axis accepts only values greater than zero. Use positive magnitudes with a display format, or switch to a linear axis for true negative values.
How do I make positive axis values display with minus signs in WPS Spreadsheets?
Right-click the chart axis, choose Format Axis, expand Number, select Custom, enter a code such as `"-"0.00`, and click Add or Apply.
Will custom negative-looking labels change the chart’s actual data?
No. The custom number format changes only the text shown on the axis. The chart still plots the positive values used by the logarithmic scale.
Can a logarithmic axis display zero values?
No. Zero cannot be plotted on a standard logarithmic axis. Remove zero from the plotting range, use a positive helper value only when appropriate, or change the axis to a linear scale.
What should I do if the negative values need their true positions on the chart?
Disable Logarithmic scale in Format Axis and use a linear axis. This allows WPS Spreadsheets to plot negative values, zero, and positive values according to their actual numeric positions.
