How to Change the Date Range in Excel Chart

Your Excel chart looks great, but it's showing everything - every day, week, and month since you started tracking data. To get a real insight, you need to zoom in on a specific period, like the last quarter or the dates of a recent marketing campaign. This article will show you several ways to change the date range in an Excel chart, from simple filtering to creating interactive, dynamic dashboards.

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

First, Understand Why You Should Use Excel Tables

Before diving into the methods, let's cover a foundational best practice: always format your source data as an Excel Table. This isn't just about the alternating row colors. When your data is in a Table, any chart you create from it will automatically update when you add new rows of data. This saves you from constantly having to redefine your chart's source data.

If your data isn't in a Table yet, it's a one-click fix:

1. Click any cell inside your data range.
2. Go to the Insert tab on the Excel ribbon.
3. Click Table.
4. Excel will automatically detect your data range. Make sure the "My table has headers" box is checked if you have them, and click OK.

That's it! Now your data is in a structured Table, which makes the following methods more powerful and reliable.

Method 1: The Quick and Easy Way with Filters

The simplest way to change the date range on your chart is by using the built-in filters on your data Table. When you filter out rows in your Table, they temporarily disappear from the chart. This method is perfect for quick, on-the-fly analysis.

Here’s how to do it:

1. Create your chart: Select your data Table, go to the Insert tab, and choose the chart type you want (e.g., Line Chart).
2. Open the filters: In your data Table, click the small dropdown arrow in the header of your date column.
3. Choose a date filter: Hover over "Date Filters" in the menu. You’ll see a list of incredibly useful preset options like "This Week," "Last Quarter," or "Next Month." You'll also find logical filters like "Before...," "After...," and "Between...".
4. Set your custom range: To select a specific date range, click Between.... A new window will appear where you can enter a start and end date. You can type them in or use the calendar icon to select them.

As soon as you click OK, your Table will be filtered to show only the data within that range, and your chart will instantly update to match. To clear the filter and show all your data again, just click the dropdown arrow in the date header and select "Clear Filter from [Date Column Name]."

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

A Note on Hidden Data

For filtering to work, your chart needs to be configured not to show data in hidden rows. Most of the time, this is the default setting. If your chart doesn't change after filtering, you may need to adjust this setting. Right-click your chart, choose "Select Data...", click the "Hidden and Empty Cells" button in the bottom left, and make sure the "Show data in hidden rows and columns" checkbox is unchecked.

Method 2: Interactive Control Using Helper Cells and Formulas

Manually opening the filter menu every time can be a drag, especially if you want to quickly compare different periods. A more efficient setup is to create dedicated "Start Date" and "End Date" cells that control the chart's range. When you change the dates in those cells, the chart updates automatically.

This approach uses a "helper column" with a simple formula to determine which rows should be included.

Step-by-Step Guide:

1. Set Up Your Input Cells

Find some empty cells on your spreadsheet (either next to your chart or at the top of the sheet). Label two cells: one for "Start Date" and one for "End Date." Let's say these are cells F1 and G1. Enter the starting and ending dates for your desired range into these cells.

2. Create a Helper Column

In your data Table, add a new column. A good name is "Include in Chart?" or "Date Filter." In this new column, we’ll use a formula to check if the date in each row falls between our Start and End dates.

3. Write the Formula

In the first cell of your new helper column, enter the following formula. (Assuming your date column is named "Date" and your input cells are F1 and G1):

=AND([@Date]>= $F$1, [@Date]<= $G$1)

Let’s quickly break this down:

• [@Date] is a structured reference in Excel that means "the date in this current row."
• $F$1 and $G$1 are absolute references to your "Start Date" and "End Date" cells. The dollar signs mean that even as the formula is filled down, it will always point to F1 and G1.
• The AND function checks if both conditions are true: Is the date greater than or equal to the start date, AND is it less than or equal to the end date?

When you press Enter, Excel will fill this formula down the entire column. The column will now show TRUE for dates within your selected range and FALSE for all others.

4. Filter the Helper Column

Now, just like in Method 1, click the dropdown arrow in the header of your new "Include in Chart?" column and filter it to show only the TRUE values. Voila! Your chart will instantly adjust based on the dates in your input cells. Now you can just change the dates in F1 and G1 to update your chart instantly, without ever touching the filters again.

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

Method 3: Go Dynamic with Named Ranges

This method is the most advanced but also the most seamless, as it doesn't require any visible filtering on your data Table. Instead, you create dynamic named ranges that automatically contract and expand based on your chosen date range. You then tell your chart to use these named ranges as its data source.

What Are Dynamic Named Ranges?

In Excel, you can give a range of cells a friendly name (like "MonthlySales"). A dynamic named range uses formulas (typically with the OFFSET function) to automatically adjust the size of this range as your data changes. By pairing this with your date inputs, we can create named ranges that only include the data for the time period you want.

This may seem complicated, but we'll walk through it.

1. Set up and name your input cells: Like in the helper column method, have cells for your "Start Date" and "End Date." Go a step further and name them for easier use. Click on your start date cell, then go to the Name Box (just to the left of the formula bar) and type StartDate and press Enter. Do the same for your end date cell, naming it EndDate.
2. Open the Name Manager: Go to the Formulas tab on the ribbon and click Name Manager.
3. Create the date range:

=OFFSET(Sheet1!$A$2, MATCH(StartDate, Sheet1!$A:$A, 1)-2, 0, COUNTIFS(Sheet1!$A:$A, ">="&StartDate, Sheet1!$A:$A, "<="&EndDate), 1)

1. Create the data range:

=OFFSET(Sheet1!$B$2, MATCH(StartDate, Sheet1!$A:$A, 1)-2, 0, COUNTIFS(Sheet1!$A:$A, ">="&StartDate, Sheet1!$A:$A, "<="&EndDate), 1)

1. Update your chart's data source:

• Right-click on your chart and choose "Select Data...".
• On the left side (Legend Entries), select your data series (e.g., "Sales") and click "Edit."
• In the "Series values" field, you'll see something like =Sheet1!$B$2:$B$100. Replace that entire reference with:

=YourWorkbookName.xlsx!chart_Values

• Now, on the right side (Horizontal Axis Labels), click "Edit."
• Replace the range in the "Axis label range" field with:

=YourWorkbookName.xlsx!chart_Dates

Click OK. Now, your chart is running on these intelligent, dynamic ranges. When you change the StartDate and EndDate cells, the named ranges automatically shrink to just the right data, and the chart updates instantly without any filtering needed.

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

Method 4: The Best User Experience with a Timeline Slicer

If you want to create a truly professional and user-friendly dashboard, the best way to handle date ranges is with a Timeline Slicer. Slicers are user-friendly buttons that filter your data. A Timeline is a special type of slicer designed specifically for dates.

This method requires using a PivotChart instead of a standard chart.

1. Create a PivotTable: Click anywhere inside your data Table, go to the Insert tab, and click PivotTable. Add it to a new or existing worksheet.
2. Set up the PivotTable fields: In the "PivotTable Fields" pane, drag your date field into the "Rows" area and your main data field (e.g., "Revenue") into the "Values" area.
3. Insert a PivotChart: With your PivotTable selected, go to the PivotTable Analyze tab and click PivotChart. Choose your preferred chart type.
4. Insert a Timeline Slicer: Now for the magic. Click on your new PivotChart to select it. Under the PivotChart Analyze ribbon, click Insert Timeline.
5. Connect it to your date field: In the small window that pops up, check the box next to your date field and click OK.

A beautiful, interactive timeline will appear on your sheet. You can now use it to filter your PivotChart. You can click on specific months, drag a window to create a custom date range, or use the dropdown to switch between filtering by days, months, quarters, or years. It’s an intuitive experience for anyone viewing your report.

Final Thoughts

Dealing with date ranges in Excel charts doesn't have to be a constant struggle. From quick filters for quick analysis to interactive timeline slicers for building professional dashboards, you have multiple ways to show exactly the data you need for the time period that matters.

While mastering these Excel techniques is a powerful skill, we know it can be time-consuming to set up and maintain, especially when dealing with data from multiple sources. We built Graphed to remove this friction entirely. Instead of creating helper columns or writing complex formulas, you connect your data sources (like Google Analytics, Shopify, or Salesforce) in a few clicks. Then, you can simply ask for what you want in plain English, like "Show me a line chart of my Shopify revenue from January to March," and the report is built for you in seconds with live, auto-updating data.