Yes. In Excel for Microsoft 365 on Windows, an XY scatter plot normally treats truly empty cells as gaps, so a row with a missing X or Y value does not appear as a complete point. You can change the chart’s empty-cell setting, but formula results such as an empty string and numeric zeros can behave differently from genuinely blank cells.
What a blank means in a scatter plot
A scatter plot places each observation at a coordinate: one value supplies X and another supplies Y. For example, a row containing an X value of 2 and a Y value of 15 produces a point at (2, 15). If one of those two inputs is missing, Excel does not have a complete coordinate to plot as an ordinary point. With the chart set to show empty cells as gaps, it omits that observation rather than filling in the missing measurement.
That distinction matters because scatter plots use numeric positions on both axes. They are not simply placing values along category labels, as a line chart often does. A blank X value is not a label that Excel can skip while preserving a category sequence; the X and Y values must belong together as a pair. When the chart includes lines between points, a gap can also interrupt the line. With markers only, the missing observation simply has no marker.
Excel’s usual empty-cell behavior is therefore useful for incomplete measurements: it avoids inventing a value for a missing reading. Still, inspect the chart if missing data has a material effect on interpretation. A point at zero may look like a blank at first glance, but it represents a plotted coordinate and can change the apparent pattern.
Check the chart’s empty-cell setting
To see how Excel is set to display empty cells, select the chart and use this Windows path: Chart Design > Select Data > Hidden and Empty Cells. In the dialog, find Show empty cells as. The available choices are generally Gaps, Zero, and Connect data points with line. Choose Gaps when you want missing observations omitted rather than represented by zero. The connect option is relevant to charts with lines; it does not create a marker for a missing scatter-plot observation.
These options change how the chart draws empty cells; they do not change the source data. Choosing Zero can make an empty input appear at coordinate zero, which is only appropriate if zero is a meaningful value in your data. Connecting across a gap can be helpful when the line is intended to show a trend across a missing reading, but it visually bridges information that was not observed. Decide based on what the chart should communicate, not just on which version looks smoother.
Excel for Mac has similar chart-data controls, although the dialog placement and wording can vary by release. Excel for the web may offer fewer chart-editing controls than the Windows desktop app. If you cannot find the empty-cell setting in your version, use the desktop app or prepare helper columns that explicitly mark incomplete pairs as unavailable.
Make sure X and Y ranges stay aligned
A reliable scatter chart uses matching X and Y ranges. For example, if dates or measurements are in A2:A10 and the paired results are in B2:B10, select both ranges when creating the chart. Each row is one observation: A2 belongs with B2, A3 with B3, and so on. If the ranges have different lengths or start on different rows, Excel may pair values incorrectly or leave out data.
- Put the X measurements in one column and the corresponding Y measurements in the next.
- Leave a genuinely missing measurement blank rather than typing a placeholder such as a dash or the word blank.
- Select both columns, including their headers if present, then choose Insert > Insert Scatter (X, Y) or Bubble Chart and select a scatter subtype.
- Use Chart Design > Select Data to check the series’ X and Y value ranges if the plotted points do not match the rows.
For example, if a sensor records temperature in column A and pressure in column B, a missing pressure reading means that row cannot provide a complete temperature-pressure coordinate. Do not shift the later pressure readings upward to close the gap: doing so would pair those results with the wrong temperatures. Keeping the blank in its original row preserves the relationship between observations.
Distinguish empty cells, empty strings, zeros, and errors
Cells that look blank are not always empty. A cell may contain a formula that returns an empty string, such as a formula whose result is "", or it may contain a space, a dash, text, a zero, or an error value. These are different inputs. In particular, a numeric zero is a real value, not missing data. A scatter chart can legitimately plot it on an axis, and the empty-cell setting does not convert it into a gap.
Formula-generated empty strings are a common source of confusion. They look blank in the worksheet, but they are formula results, not truly empty cells, and chart behavior can differ from the behavior for an untouched cell. Depending on the chart and its source, such a result may be treated like zero or may not behave as the user expects. If a visible point appears at zero, check the formula bar and the source cells rather than assuming Excel ignored the blank.
Text placeholders are not a dependable way to create missing points either. A dash or a space is not a numeric X or Y coordinate. Replace placeholders with genuine blanks or use a deliberate helper-column approach. Error values also need thought: Excel charts commonly omit #N/A values, which makes #N/A useful for marking a point that should not be plotted. Other errors can cause chart or data problems, so do not use arbitrary errors as a general blank substitute.
Use helper columns to exclude incomplete pairs
When the source cells contain formulas, placeholder text, or a mix of complete and incomplete rows, helper columns make the intended rule explicit. Assume X values are in A2:A10 and Y values are in B2:B10. Put this formula in C2, then fill it down through C10 to keep only X values for rows where both original cells are numeric: =IF(AND(ISNUMBER(A2),ISNUMBER(B2)),A2,NA()). With A2 equal to 1.5 and B2 equal to 12, C2 returns 1.5. If either source cell is blank or text, C returns #N/A instead.
With the same source layout, enter this formula in D2 and fill it down through D10 to create the matching Y values: =IF(AND(ISNUMBER(A2),ISNUMBER(B2)),B2,NA()). For the example row with A2 equal to 1.5 and B2 equal to 12, D2 returns 12. If a row has an incomplete pair, both helper results are #N/A, so the chart has no valid coordinate for that row. Create or edit the scatter series to use C2:C10 for X values and D2:D10 for Y values. Excel charts generally skip #N/A points; if your build offers a Show #N/A as an empty cell option in the chart dialog, enable it as well.
This technique deliberately excludes text and formula blanks, but it also excludes numeric text such as a value imported as the text 1.5. Convert numeric text to real numbers if those records should count. Because ISNUMBER treats zero as numeric, a legitimate zero remains in the chart. That is often desirable: missing data and a measured zero are not the same thing.
Choose whether to omit, connect, or plot zero
The right display depends on what a missing measurement means in context. For independent observations, such as a collection of paired lab readings, omitting incomplete pairs is usually the clearest choice. For a time-ordered series displayed as a scatter chart with lines, a gap can honestly show that the measurement is unavailable. Connecting points across the missing time may be reasonable when the line is intended as a visual guide, but it should not imply that the intervening values were measured.
- Use Gaps when the value is unknown and you do not want the chart to imply a coordinate.
- Use Zero only when a missing entry is genuinely meant to represent zero for the purpose of the analysis.
- Connect data points with line when showing a continuous visual path is more useful than displaying a break, and readers will understand that the segment crosses missing data.
- Use helper columns with #N/A when you need a consistent rule for paired X and Y data, especially when apparent blanks come from formulas or text.
Do not replace blanks with zero simply to make a chart look complete. Suppose a machine’s output was not recorded on one day. Adding a zero would say that the machine produced nothing, which is a different claim from saying that the output is unknown. Conversely, if the data definition says that an empty entry means no activity and therefore equals zero, document that rule and use a numeric zero consistently.
Common reasons a point still appears
If a point seems to represent a blank, first identify the exact row and inspect both source cells. One may contain a zero, a formula returning zero, or a formula returning an empty string. Also check whether the chart series references the intended columns: an incorrect range can include a different cell that has a value. In a scatter chart, confirm that the X and Y ranges are the same length and aligned to the same rows.
Next, check for values that look empty but are not. A cell containing a space or an imported text placeholder is not a true blank; a number stored as text may also fail a numeric test in a helper formula. Use Home > Find & Select > Go To Special to inspect certain cell types if needed, or click the cell and review its formula bar. Correct the source data or use helper columns that match the actual data rules.
Finally, verify the selected empty-cell option and the chart type. A scatter chart with markers only cannot draw a connecting line across a gap, while a scatter chart with lines can make the treatment of gaps more noticeable. If the chart still looks misleading, add a note explaining that missing records are omitted, or show the incomplete records in a separate table rather than silently converting them to zero.
Scatter plot versus line chart
Both chart types can show points in relation to values, but they treat the horizontal axis differently. A scatter chart uses numeric X values as positions, which is appropriate when the horizontal measurements are quantities such as time in seconds, distance, or temperature. A line chart typically places categories at evenly spaced positions; it can be suitable for ordered categories even when their numeric spacing is not meaningful. Choosing a line chart does not solve missing-data problems, and it can change the meaning of the horizontal scale.
For paired measurements, use a scatter plot when the actual X values matter. For example, measurements at 1, 2, and 10 seconds should have unequal horizontal spacing in a scatter plot. If the chart only needs to compare a sequence of named periods at equal spacing, a line chart may be clearer. In either case, preserve missing values accurately and use the empty-cell options to choose how gaps should appear.
Frequently Asked Questions
Does Excel automatically ignore blank cells in a scatter plot?
Usually, yes: with empty cells set to Gaps, a truly empty X or Y input does not produce a complete plotted point. Check the chart setting and confirm the cell is truly empty, because zeros and formula results that look blank can behave differently.
How do I stop Excel from plotting blanks as zero?
Select the chart and go to Chart Design > Select Data > Hidden and Empty Cells, then choose Gaps. If the source cells contain formulas or placeholders, use helper columns to return #N/A for incomplete X-Y pairs.
Will a formula that returns an empty string be ignored?
Not necessarily. A formula result of an empty string is not the same as an empty cell, and chart behavior may differ. If the row must be excluded reliably, test the source values in helper columns and return #N/A for incomplete pairs.
Can I show a real zero in a scatter plot?
Yes. A numeric zero is valid data and can be plotted at zero on the relevant axis. Keep it as zero when it means a measured zero; do not use zero as a stand-in for an unknown value.
Why is a blank row creating a point at the origin?
Check for zeros, formulas returning zero or empty strings, and incorrect X or Y ranges. If both chart coordinates resolve to zero, Excel can show a point at the origin. Use Gaps for true empty cells or helper columns that return #N/A when either member of the pair is missing.
Related
- Excel Protect Sheet but Allow Data Refresh?
- Excel Protect Sheet but Allow Filtering: How to Set It Up
- Excel Protect Sheet: Can a Table Expand Automatically?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Duplicate a Page in Excel and Keep Its LayoutTo duplicate a page in Excel, right-click its worksheet tab, choose Move or Copy, check Create a copy,…
- Wrap Text in Excel and Google Sheets: Step-by-StepTo wrap text, select the cell or range, then turn on wrapping: in Excel for Microsoft 365 on…
- Google Sheets Distinct Values: Find, Filter, and Count ThemTo list distinct values in Google Sheets, enter =UNIQUE(A2:A100) in an empty cell, replacing the range with your…
- Edit Data Validation in Excel: Lists, Rules, and FixesTo edit data validation in Excel for Microsoft 365 on Windows, select a cell that uses the rule,…
- How to Make Labels in Word: Print Them CorrectlyTo make labels in Microsoft Word, open Mailings > Labels, choose the label brand and product number, enter…
- Plant Inventory Spreadsheet: What to Track and How to UseA plant inventory spreadsheet is a table for recording what plants you own or sell, how many you…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.