How to Skip Dates in Excel Graph

Creating a line chart in Excel to track data over time often reveals an annoying problem: empty gaps for dates you never recorded, like weekends or holidays. This happens because Excel automatically tries to create a continuous timeline, leaving space for Saturday and Sunday even if your data only covers weekdays. This article will show you exactly how to remove these unwanted gaps and create a clean, continuous graph that only shows the dates you care about.

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

Understanding the Cause: Date Axis vs. Text Axis

The root of this issue lies in how Excel interprets the data on your chart's horizontal (X) axis. By default, when Excel detects dates, it uses a Date axis (also called a time-scale axis), an intelligent but sometimes unhelpful feature.

A Date axis treats your dates like points on a calendar, understanding the chronological space between "Friday, October 25th" and "Monday, October 28th" includes a Saturday and Sunday. To represent this passage of time accurately, it leaves a gap on the chart. This is useful if you want to highlight periods of inactivity but problematic when you only want to visualize data from active days, like business days.

The solution is to tell Excel to stop thinking about a timeline and instead treat each date as a simple label. You do this by switching to a Text axis (or Category axis). A Text axis plots each data point sequentially, one after another, at equal intervals. It doesn’t care if a date is a Monday or a Friday, it just sees it as the next category in line. This removes any gaps from weekends, holidays, or any other missing dates.

When to Use Each Axis Type

• Use a Date Axis (Default) when: The chronological gaps are meaningful. For instance, if you're tracking website traffic and there was a two-day server outage, the gap on the chart correctly visualizes that period of zero activity.
• Use a Text Axis when: You only want to see the progression of data points you have, without illustrating inactive periods. This is perfect for daily sales reports (Monday-Friday), stock market data (trading days only), or project task updates on workdays.

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

How to Skip Dates by Changing the Axis Type

The most direct way to remove date gaps is by manually changing the axis settings for your existing chart. Let’s say you have a simple data set of daily sales, but you have no entries for weekends.

Here’s the step-by-step process:

1. Create Your Chart: Select your data, including your date and sales columns. Go to the Insert tab on the ribbon and choose a line chart from the Charts section. You will immediately notice the gaps where the weekends should be.
2. Select the Axis: Right-click directly on the horizontal axis of your chart (the one displaying the dates).
3. Format Axis: From the context menu that appears, select Format Axis... This will open a settings panel on the right side of your Excel window.
4. Change to Text Axis: In the "Format Axis" panel, make sure you are in the "Axis Options" tab (the icon looks like a small bar chart). Under the heading Axis Type, you will see three options: "Automatically select based on data," "Text axis," and "Date axis." Select Text axis.

The moment you click it, your chart will instantly update. The empty spaces for the weekends will disappear, and your data points from Friday to Monday will now appear right next to each other, creating a smooth, connected line. Your chart is now focused solely on the days you recorded data for.

Practical Examples of Skipping Dates

This technique is incredibly useful across various business scenarios. Here are a few common situations where switching to a Text axis makes your data story clearer.

Example 1: Analyzing Weekday Sales Performance

Imagine you run an e-commerce store and you're plotting daily revenue for the past month. Your data only includes entries for Monday through Friday, as that's when your team processes orders. With the standard Date axis, your chart shows significant dips and flat lines every weekend, which can be visually distracting and make it harder to spot weekday trends. By switching to a Text axis, you get a continuous line that clearly shows the sales flow from one business day to the next, helping you more easily identify patterns like Wednesday slumps or Friday spikes.

Example 2: Visualizing Stock Prices

Stock market data is a perfect use case for a Text axis. Markets are closed on weekends and public holidays, so there's no data for those days. A chart that plots a stock's closing price using a Date axis will be riddled with horizontal gaps. This not only looks messy but can also be misleading, as the gaps might suggest periods of price stability rather than market closures. Converting to a Text axis presents an uninterrupted view of the stock's performance across trading days, which is exactly what a financial analyst would want to see.

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

Example 3: Tracking Project-Related Metrics

If you're a project manager tracking daily metrics like "tasks completed" or "bugs resolved," you're likely only concerned with progress made during the workweek. A chart cluttered with empty weekend spaces doesn't add value and can make a simple burndown or progress chart harder to interpret. By showing only the active workdays, the Text axis gives a much cleaner and more motivating visualization of the team's momentum.

An Alternative Method: Using PivotCharts

If you're working with larger, more complex datasets, creating your chart via a PivotTable (a PivotChart) is often a more efficient approach. PivotCharts are designed to summarize and categorize data, and as a result, they use a category-based (Text) axis by default, automatically skipping any missing dates for you.

Here's how to create a PivotChart that ignores weekends:

1. Select Your Data: Click anywhere inside your data table.
2. Insert PivotChart: Navigate to the Insert tab and click PivotChart. Confirm the data range and that you want it placed in a new worksheet, then click OK.
3. Build the Chart: A PivotChart Fields pane will appear. Drag your date field into the Axis (Categories) area and your numeric data (like "Sales" or "Revenue") into the Values area.
4. Ungroup Dates (If Necessary): Excel sometimes automatically groups dates by months, quarters, or years. If this happens, your individual dates won't be visible. To fix this, right-click on one of the date labels in the PivotTable or directly on the chart's axis and select Ungroup.

Once ungrouped, the PivotChart will display each individual date from your source data as a distinct point along the axis, with no spaces for an unlisted Saturday or Sunday. It requires no manual axis formatting.

Troubleshooting Common Problems

Sometimes things don't go as planned. Here are solutions to a couple of common issues you might encounter.

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

Problem: The "Text Axis" Option is Grayed Out and Unclickable

This typically occurs in Scatter (XY) charts. Scatter charts are specifically designed to plot numeric values on both the X and Y axes to show correlations. Since dates are stored as serial numbers in Excel, a scatter chart requires a true numerical scale - meaning it must use a Date axis. You cannot change a scatter chart to use a Text axis.

Solution: Make sure you are using a Line chart, not an XY Scatter chart. If you created a scatter plot by mistake, you can easily change it. Click on your chart, go to the Chart Design tab, click Change Chart Type, and select one of the Line chart options.

Problem: The Axis Has Too Many Labels and Looks Cluttered

After switching to a Text axis, if you have a lot of data points (e.g., several months of daily data), the date labels on the axis can overlap and become unreadable.

Solution: Adjust the label frequency. In the "Format Axis" pane, under "Labels," find the option for Interval between labels. Instead of "Automatic," select "Specify interval unit" and enter a number. For example, entering 7 would display a label only every 7th day, cleaning up the axis while still providing reference points.

Final Thoughts

Creating clear and accurate charts in Excel is all about telling the right data story. By understanding the difference between a Date axis and a Text axis, you gain control over how your timeline is presented, allowing you to easily remove unwanted gaps and focus your stakeholders' attention on the trends that actually matter.

Mastering these details is how you create professional reports, but dealing with axis formatting and endless chart settings can be a frustrating time-sink. At Graphed, we built a tool to eliminate this busywork entirely. Instead of clicking through formatting panes, you just connect your data sources and describe the chart you need in plain English. For example, you could ask, "Show me my daily Shopify sales over the last month as a line chart," and our AI handles building the visualization for you instantly, connecting your data and presenting it cleanly, with no extra manual work required.