How to Remove Dates with No Data in Excel Graph

Seeing a line chart dip to zero or break apart because of missing dates is a common and frustrating Excel issue. You've prepared your data, created a graph to track performance over time, and now there are ugly gaps or drops that misrepresent your trends. This article walks you through several simple and advanced methods to remove dates with no data, giving you a clean, accurate, and easy-to-read Excel graph.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Excel Shows Gaps for Missing Dates in the First Place

Understanding why Excel creates these gaps is the first step to fixing them. When you plot data against dates, Excel's default behavior is to use a "Date Axis." This type of axis is intelligent, it understands the chronological and proportional nature of time. It knows that January 15th comes after January 14th and treats the entire period as a continuous timeline.

This is extremely useful when you want to show the true passage of time. For example, if you're tracking sales and you had zero sales over a weekend, a Date axis will correctly show a gap for Saturday and Sunday, even if they aren't in your data. However, if your data is only collected on business days, or if specific dates are missing due to glitches or other reasons, this "intelligent" axis creates gaps you don't want.

The chart is showing an empty space because it has reserved a spot on the continuous timeline for that missing date but has no corresponding data point to plot. Most of the solutions below involve telling Excel to ignore this continuous timeline and instead handle the data in a different way.

Method 1: The Quickest Fix – Change the Axis Type

The simplest and fastest way to remove empty date gaps is to switch your horizontal axis from a Date Axis to a Text Axis. A Text Axis doesn't try to understand the chronological order or spacing of your dates, it simply treats each date label as an independent, categorical label. It will plot only the specific dates present in your source data, placing them side-by-side with equal spacing.

This method works perfectly when the exact spacing between dates isn't critical to your analysis and you just want to show the trend of the data points you actually have.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step Instructions:

1. Right-click on the horizontal axis (the one with the dates) of your chart.
2. From the context menu, select Format Axis. This will open the Format Axis pane on the right side of your Excel window.
3. In the Format Axis pane, under the Axis Options tab (it looks like a small bar chart), locate the section titled Axis Type.
4. You will see three options: "Automatically select based on data," "Text axis," and "Date axis." Your chart is likely set to either "Automatically select" or "Date axis."
5. Select Text axis.

As soon as you select "Text axis," you should see the gaps disappear from your chart. Your data points will now be plotted right after one another, creating a continuous line.

Pros and Cons of This Method

• Pros: It’s incredibly fast, requires no changes to your source data, and is easy for anyone to implement.
• Cons: You lose the scale and proportion of a true time-series axis. If you have big time gaps in your data (e.g., jumping from January to March), the chart will visually suggest it was a single day's change because it places the points right next to each other. This can be misleading if the chronological scale is important.

Method 2: Use the “Hidden and Empty Cells” Setting

Sometimes, the issue isn't a completely missing date row but a date that exists in your data with a blank cell or a zero for its value. By default, Excel plots these as a dip to the zero line. You can change this setting to connect the line over the empty point, making the zero-value date seem to disappear.

This method is for scenarios where you want the line chart to ignore intermittent zero-value entries and show a continuous trend between the non-zero points.

Step-by-Step Instructions:

1. Select your chart by clicking on its border.
2. Two new tabs will appear in the Excel ribbon: "Chart Design" and "Format." Click on the Chart Design tab.
3. In the "Data" group, click a button called Select Data. This opens the "Select Data Source" dialog box.
4. In the bottom-left corner of this box, click the Hidden and Empty Cells button.
5. A small settings window will appear. Under the "Show empty cells as:" section, you will have three choices:
6. Select the third option: Connect data points with a line.
7. Click OK on this window, and then OK again on the "Select Data Source" window.

Your line chart will now bridge the gap, skipping over the empty cell and connecting the point before it to the point after it. It's important to note this only works for empty cells. If your cells contain a 0, you'll first need to find and replace those zeros with blanks (select the column, press Ctrl+H, find 0, leave Replace With blank, and click Replace All).

Method 3: Filter Your Data to Remove Unwanted Dates

If changing the axis type isn't a good fit and your issue is entire rows of missing data, the most robust solution is to filter out those rows from your source data. By only telling the chart to look at rows that contain valid data points, you ensure no gaps can appear. The best part is, you can do this non-destructively without deleting any of your original data.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 1: Apply a Filter to Your Source Data

First, we’ll use Excel’s built-in filter functionality to hide the rows with no data.

1. Select your entire data range, including the headers.
2. Go to the Data tab in the Excel ribbon.
3. Click the Filter button (it looks like a funnel). Small dropdown arrows will appear in each of your header cells.
4. Click the dropdown arrow for the column that contains your values (e.g., "Sales," "Users," etc.).
5. In the filter menu, uncheck the boxes for (Blanks) and 0. This will tell Excel to hide any rows where the value is blank or zero.
6. Click OK.

Your table will now visually shrink to show only the rows with valid data. However, if you check your chart, you might find the gaps are still there. This is because, by default, Excel charts often still try to plot data from hidden cells.

Step 2: Tell the Chart to Ignore Hidden Cells

The crucial second step is to adjust a chart setting to ignore the data you just filtered out.

1. Follow the exact same steps from Method 2 to open the Hidden and Empty Cells dialog box (Select Chart > Chart Design > Select Data > Hidden and Empty Cells).
2. At the very bottom of this window, you will see a checkbox labeled Show data in hidden rows and columns.
3. Uncheck this box.
4. Click OK, then OK again.

With this setting disabled, your chart will now truly reflect your filtered data, dynamically redrawing itself to exclude any rows hidden by the filter. This combination gives you complete control over what appears in your graph.

Method 4: The Automated Approach with Power Query

For those who deal with this problem regularly or work with large, updating datasets, using Power Query is a game-changer. Power Query is Excel’s data transformation engine designed to automate cleaning and filtering tasks.

You can set up a simple query that filters out dates with no data. The main benefit? You only have to set it up once. The next time you add new data to your source table, a single click on "Refresh" will run your query and update your chart instantly.

How to Use Power Query:

1. First, make sure your data is in a formatted Excel Table. If it's not, select your data and press Ctrl+T.
2. With a cell in your table selected, go to the Data tab.
3. In the "Get & Transform Data" group, click From Table/Range.
4. This will open the Power Query Editor in a new window, displaying your data.
5. Find the column containing your numerical values. Click the filter dropdown arrow in the header of that column.
6. Uncheck null and 0 from the list.
7. Click OK. The preview will now show only the rows you want.
8. In the top-left corner, click the arrow on the Close & Load button and select Close & Load To...
9. In the "Import Data" window, choose to load it as a Table into a New worksheet and click OK.

Excel will create a new worksheet containing a clean, filtered version of your data. Now, build your line chart using this new green table as the source. Whenever your original data changes, just go to your Power Query table, right-click, select Refresh, and both your table and chart will update automatically, saving you loads of time on recurring reports.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Advanced Tip: Use the #N/A Error to Create Intentional Gaps

Finally, what if you don't want to connect the line or squash the timeline? In some cases, you might want to explicitly show a break in data without the line crashing to zero. There's a special way to do this in Excel using the #N/A error.

When an Excel chart encounters an #N/A value, it simply skips plotting that point, creating a clean break in a line chart. You can generate this error using a simple IF formula in a helper column:

Suppose your dates are in column A and your values are in column B. In column C, enter this formula and drag it down:

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

This formula says: "If the value in cell B2 is greater than 0, then show the value from B2. Otherwise, return the #N/A error."

Now, create your chart using the date column (A) and your new helper column (C) as the data source. You'll get a line chart that has distinct breaks wherever you had a zero or blank, which can be a more honest way to represent weeks where data wasn't collected.

Final Thoughts

Fixing gaps in your Excel charts is a matter of choosing the right tool for the job. You can switch to a Text Axis for a quick fix, use the built-in chart settings to skip over empty cells, filter your data for a clean chart source, or use the #N/A trick for intentional line breaks. The best approach depends entirely on how you need to tell the story behind your data.

Wrangling charts to look just right is a classic source of Excel frustration, something we’ve spent hours on ourselves. This is exactly why we built Graphed - to eliminate these manual steps entirely. We automate the connection to your data sources, like Google Sheets or other platforms, and let you create charts by simply describing what you want to see. For example, instead of filtering out zero-value days, you can just ask for "weekly revenue for the last three months" and get a perfectly clean, analytics-ready chart in seconds without any extra fuss.