Chart & Visualization Issues

Fix Excel Chart Displaying Cell Numbers Instead of X-Axis Values

Rana Garcia Oct 10, 2026 869 views

Question details

The user needs the Excel chart to display actual numeric values on the horizontal x-axis rather than sequential cell numbers.

How to Fix Excel Charts Displaying Sequential Cell Numbers Instead of X-Axis Values

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating an XY Scatter chart where some data points are generated by formulas that return empty strings ("") for missing data.
Observed behavior: The chart plots sequential sequence numbers (1, 2, 3...) on the horizontal axis instead of the intended numeric values because Excel interprets empty strings as nonnumeric text.

Before you start

Before troubleshooting, verify that you are using an XY Scatter chart rather than a Line chart, and ensure that your x-axis data range does not contain any accidentally typed text or spaces.

Solution 1Recommended

Replace Empty Strings ("") with the #N/A Error in Formulas

Modify your formulas to output the #N/A error instead of an empty string, which allows Excel charts to gracefully ignore missing data without breaking the numeric axis.

Excel does not currently support returning a true NULL value from a formula. When a formula returns an empty string (""), Excel treats it as text. Since an XY Scatter chart requires all x-values to be numeric, finding even one text value causes Excel to default to sequential numbers.

By replacing the empty string with the NA() function, the chart will recognize the #N/A error and simply not plot the point, keeping the x-axis numeric.

Locate the formula

Select the first cell in the column that generates your x-axis values.

Modify the formula

Change the output for missing data from "" to NA(). For example, change =IF(A2>0, A2, "") to =IF(A2>0, A2, NA()).

Apply to the entire range

Drag the fill handle down to apply this updated formula to all cells in the data column.

Refresh the chart

Check your chart. Excel will now maintain the correct numeric scale on the x-axis and leave a gap for the #N/A values.

Replace Empty Strings ("") with the #N/A Error in Formulas

Pro Tip: If you do not want the #N/A errors visible in your spreadsheet, you can use Conditional Formatting to hide them by setting the font color to match the cell background.

Solution 2

Filter Out Nonnumeric Data using Helper Columns

Create a clean data range that extracts only valid numbers for your chart, avoiding the empty strings generated by your main formulas.

Create Data Charts Easily

Create and Troubleshoot Charts Seamlessly with WPS Spreadsheet

WPS Spreadsheet offers an intuitive charting interface that perfectly handles complex data ranges and formula outputs, ensuring your X-axis displays exactly what you need without formatting frustrations.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your charting data.
2. Select your data: Highlight the numeric data range you want to visualize, ensuring the x and y columns are paired correctly.
3. Insert the chart: Go to the 'Insert' tab on the top ribbon, click 'Chart', and select 'XY (Scatter)' from the menu.
4. Customize axis settings: Right-click the horizontal axis and select 'Format Axis' to adjust numeric scaling and handle hidden/empty cells via the properties panel.

Easily handle hidden cells, blank values, and errors in chartsFully compatible with Microsoft Excel (.xlsx) formats and formulasLightweight application with high performance for large datasetsExtensive built-in chart types including advanced XY Scatter and Line variants

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel switch my x-axis to 1, 2, 3 instead of my data?

This happens when Excel encounters nonnumeric values (like text, spaces, or an empty string "" returned by a formula) in your x-axis data range. Because a Scatter chart requires purely mathematical values to space out the axis correctly, any text causes Excel to fail over to counting sequential numbers.

Can I use a Line chart instead of an XY Scatter chart to fix this?

A Line chart treats the horizontal x-axis as categorical text labels by default, meaning it places data points evenly apart regardless of the numeric difference between them. If you need the x-axis to represent a continuous mathematical scale, you must use an XY Scatter chart and ensure the data is strictly numeric.

Is there a way to return a true NULL value in an Excel formula?

No, Excel formulas currently do not support returning a true NULL or blank value that behaves identically to an empty cell. Returning an empty string ("") is treated as text. For charting purposes, using the NA() function is the standard workaround.

Excel & Data Problems