How to Use the NA Function in Microsoft Excel to Prevent Gaps and Zero Drops in Charts

Plotting dynamic time-series data—such as monthly sales trends, operational telemetry, project milestones, or temperature readings—in Microsoft Excel often presents visualisation challenges when data is incomplete. By default, when an Excel line or scatter chart encounters an empty cell or a formula that evaluates to zero or blank text (""), the chart either plummets straight down to the zero baseline or introduces jagged, awkward disconnects.

A plunging line chart misleadingly suggests that revenue, temperature, or production dropped to zero, rather than showing that the data point simply has not yet occurred. Microsoft Excel provides a smart mathematical solution through the NA() function. When a formula returns the official #N/A (Not Available) error code, Excel’s charting engine recognizes that data is missing and intelligently bridges the gap or interpolates between adjacent valid points. In this guide, we explore how to use the NA function in Microsoft Excel to build professional, presentation-ready charts that handle missing data cleanly.

Why Empty Cells and Blank Strings Distort Excel Charts

Understanding how Excel treats missing data reveals why conventional formulas cause visual errors:

• Literal Zero (0): A line chart plots a physical marker at zero on the vertical Y-axis, resulting in sudden, unrealistic downward spikes.
• Empty Strings (""): Many users write formulas like =IF(B2="", "", B2). Unfortunately, Excel treats empty text strings as text, which chart engines often plot as zero values rather than blank space.
• The #N/A Value: Excel line and scatter charts are uniquely programmed to ignore #N/A errors. Instead of dropping to the bottom axis, the chart draws a continuous connecting line between the previous valid value and the next known data point.

Syntax and Behaviour of the NA Function

The syntax for the NA function in Microsoft Excel is simple:

=NA()

The function requires no arguments. It evaluates directly to the system error value #N/A. While displaying error codes in a final data table might look unconventional, it is the standard and correct approach for professional financial modeling and technical charting.

How to Use NA with IF to Handle Future or Unrecorded Dates

The most common application of the NA function is in forecasting models where future months have not yet occurred. Suppose you have a table where Column A contains Months and Column B contains actual Revenue:

1. In Column C (which your chart will read), enter the following formula starting at row 2:

=IF(ISBLANK(B2), NA(), B2)

Alternatively, if your source cells contain zero when unrecorded, write:

=IF(B2=0, NA(), B2)

When you drag this formula down Column C, past and current months display their actual numeric values, while future unrecorded months display #N/A.

Configuring Chart Hidden and Empty Cell Settings

To ensure your Excel chart interprets #N/A and empty cells according to your visual preference:

1. Click on the chart to select it.
2. Navigate to the Chart Design tab on the Excel ribbon.
3. In the Data group, click Select Data.
4. In the lower-left corner of the Select Data Source dialogue box, click the Hidden and Empty Cells button.
5. Under Show empty cells as, you will see three distinct options:
   • Gaps: Leaves a clean break in the line between valid points.
   • Zero: Forces missing points to drop to the zero axis (usually undesirable).
   • Connect data points with line: Seamlessly bridges across missing points, drawing a straight trendline between surrounding valid points.
6. Select Connect data points with line or Gaps depending on whether you want an interpolated trendline or an explicit pause in recording.
7. Click OK twice to apply the settings.

How to Hide #N/A Errors from Table View Using Conditional Formatting

If you need your worksheet table to look polished for executive presentations while retaining #N/A for your charts, you can mask the error text without altering formula calculations:

1. Select the cell range containing your formulas.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Format only cells that contain.
4. Under Format only cells with, change the first dropdown from Cell Value to Errors.
5. Click the Format button.
6. In the Font tab, set the font colour to White (or matching your worksheet’s background colour).
7. Click OK twice.

The #N/A values will become invisible to viewers reading the spreadsheet, while remaining fully active behind the scenes so your charts render cleanly without plunging lines or erroneous zeros.