Excel Chart Interactive Date Range
Select start and end dates, and the Microsoft Excel chart changes to show that date range. This technique uses dynamic named ranges, which are based on Excel tables, or created with OFFSET formulas. Perfect for an Excel dashboard, where space is limited.
Author: Debra Dalgleish
Chart With Interactive Date Range
In this example, there is an interactive Excel column chart, with date drop downs at the top of the worksheet.
When
you add new sales data records, or if you select a different date range at the top of the chart sheet, the
chart is immediately updated.

In the video below, see how to set up an Excel table, and dynamic range names, and then create formulas that summarize the sales for the selected date range. There are written steps below the video, and sample files to download, at the end of this page.
Benefits - Interactive Date Range Chart
This type of interactive date range chart is great for an Excel dashboard, where you need to present key data in a small space.
Compact & Visible Date Range Selection
At the top of the Excel sheet, the chart data's start and end dates are:
- easy to see
- easy to use
- small - only need 4 cells
Other dashboard formulas could be linked to those start and end cells. Then, with a couple of clicks, all the dashboard data updates at once.
Custom Designed Chart Data
With this Interactive Date Range Chart technique, the chart data is custom built, so you can choose key items to include, and omit others. For an Excel dashboard, you could focus on key regions or product lines..
Any type of chart can be built from the worksheet chart data source. Other methods of summarizing Excel data, such as pivot charts, do not allow certain Excel chart types, such as Scatter charts, or Histograms.
Create Sales Data List
Follow the steps below, to create a worksheet list with sales data set for an interactive chart.
OR, to save time, go to the Download section below, and get the "Chart Data Only" sample file.
- In a new Excel workbook, rename Sheet1 as sheet name SalesData
- Add another sheet, and rename the new sheet as Chart
- On the SalesData sheet, in cells A1:D1, add the headings, Date,
Item, Colour, Units, as shown below.
- Enter sample data in each column.
Later, you will use this worksheet data to create summary data for the chart

Create Dynamic Ranges
The Sales Data list is not static, it is going to change size. New records will be added to the table every business day. So, when we build an interactive chart:
- NO: we don't want to use a static range of cells, such as the current range for the list, $A$1: $D$43.
- YES: chart should be based on a dynamic range of cells, that grows automatically the the number of rows in the source data changes.
The sections below show two ways to set up dynamic named ranges, based on the sales data
- Recommended: Format the sales data list as a named Excel Table, then create named ranges based on the table columns.
- Alternative: Use OFFSET formulas to create named ranges, based on the sales data list.
Follow the steps below, using the method that you prefer.
--- 1. Go to Named Table steps
--- 2. Go to OFFSET formula steps
Then, after those setup steps are completed, move on to the steps for setting up the date selection drop down lists.
Create Dynamic Ranges - Excel Table
Follow the steps below, to use the recommended method for setting up dynamic ranges, using Excel Tables.
- First, you'll change the list to an Excel table
- Next, you'll create 3 dynamic ranges, based on the Excel table columns
Change List to Excel Table
Follow these steps to format the sales data list as a named Excel Table:
- Select any cell in the worksheet list with sales data
- On the Ribbon, click the Home tab.
- In the Styles group, click the Format as Table command
- In the pop-up list of Table Styles, click the style that you want to use

Create Table Dialog Box
Next the Create Table dialog box opens, and you can fill in the details:
- range for your data should automatically appear in the range box
- If necessary, you can adjust the range
- Check box for My table has headers option should be checked
- If it's not checked, click the box to select that option
- Click OK to accept these settings

Formatted Excel Table
After you click OK, the sales data list is formatted as an Excel Table

Change Table Name
When it is created, an Excel table is given a default name, such
as Table1. You can change the name to something meaningful, so
it's easier to identify the table later, if it's used in formulas.
To change the table name, follow these steps:
- Select any cell in the table
- On the Ribbon, under the Table Tools tab, click the Design tab
- At the far left of the Ribbon, click in the Table name box, to
select the existing name
- Then, type a new name, such as tblSales, and press the Enter key

Create Dynamic Range for Dates
The next step is to create a dynamic range for the Date column, based on the Excel table columns.
- In the Excel table, point to the top border of cell A1 - the Date column heading
- When the pointer changes to a down arrow, click to select all the data in that column (the heading cell will not be selected)
- NOTE: Do not click on the column "A" button, above the Excel table -- that will select the entire worksheet column
- Next, click in the Name Box, to the left of the Formula bar
- Type a one-word name for the dynamic range -- DateList
- Then, to complete the name, press the Enter key
- WARNING: If you skip this step, the name will not be created

Create 2 More Dynamic Ranges
There are two more dynamic ranges to create, following the same steps
Colour Column
- To start the next name, click at the top of cell C1 - the heading for the Colour column
- Next, click in the Name Box, to the left of the Formula bar
- Type a one-word name for the dynamic range -- ColourList
- Then, to complete the name, press the Enter key
- WARNING: If you skip this step, the name will not be created
Units Column
- To start the next name, click at the top of cell D1 - the heading for the Units column
- Next, click in the Name Box, to the left of the Formula bar
- Type a one-word name for the dynamic range -- UnitsList
- Then, to complete the name, press the Enter key
- WARNING: If you skip this step, the name will not be created
Go to the Next Step
Next, after all 3 dynamic ranges are set up, move on to steps for setting up the date selection drop down lists.
Create Dynamic Ranges - OFFSET
Follow the steps below, to use the alternative method for setting up dynamic ranges, using OFFSET Function formulas.
- First, you'll create a dynamic formula for the Date column in the list
- Next, you'll create 2 more dynamic ranges, based on the dynamic Date range
Create Dynamic Range for Dates
Next, you'll create a dynamic range for the Date column, using the OFFSET function.
- On the Excel Ribbon, go to the Formulas tab
- In the Defined Names group, click Define Name
- In the Define Name dialog box, type a name for the first range -- DateList
- In the Refers To box, enter the following OFFSET formula:
- =OFFSET(SalesData!$A$2,0,0,COUNT(SalesData!$A:$A),1)
- Click the Add button

How the OFFSET Formula Works
This OFFSET function defines the
dynamic range size, based on the count of numbers in the Date column
Here are the OFFSET syntax arguments that are used in this formula:
- Reference cell: SalesData!$A$2 -
- starting cell for dynamic range
- Rows to offset: 0
- Adjust the starting cell by 0 rows
- Columns to offset: 0
- Adjust the starting cell by 0 columns
- Number of Rows: COUNT(SalesData!$A:$A)
- Count numbers in Date column (A)
- In the sample file, there are 42 dates, so range will end in row 43
- Note: To count text entries, use the COUNTA function instead
- Number of Columns: 1
Dynamic Range Adjusts Automatically
Currently, the DateList range address is A2:A43 on the Sales Data sheet. If 2 more dates were added, the dynamic range address would automatically change to A2:A45.
Create 2 More Dynamic Ranges
The remaining 2 dynamic ranges will be based on the DateList range.
- Note: The Item names in column B do not need a dynamic range for the interactive chart.
Dynamic Range for Colour (Column C )
- Type a name for the next range -- ColourList
- In the Refers To box, enter an Offset formula that refers to the
DateList range:
=OFFSET(DateList,0,2)
The arguments used in this Offset function are:
- Reference range: DateList
- Rows to offset: 0
- Columns to offset: 2
- Click the Add button
Dynamic Range for Units (Column D)
- Type a name for the next range -- UnitsList
- In the Refers To box, enter an Offset formula that refers to the
DateList range:
=OFFSET(DateList,0,3)
The arguments used in this Offset function are:
- Reference range: DateList
- Rows to offset: 0
- Columns to offset: 3
- Click the OK button

Go to the Next Step
Next, after all 3 dynamic ranges are set up, move on to steps for setting up the date selection drop down lists.
Create Date Range Selection Cells
Next, you'll set up date selection drop downs on the Chart sheet, using Excel's Data Validation feature. These drop downs will use one of the dynamic ranges -- DateList.
Follow these steps to create drop down
lists for the chart start and end dates.
- On the Chart sheet, type headings for the date selection cells
- Next, select cells C2, then press Ctrl, and select cell E2
- On the Excel Ribbon, go to the Data tab
- In the Data Tools group, click Data Validation
- In the Data Validation dialog box, go to the Settings tab
- From the Allow drop down, choose List
- In the Source box, type: =DateList
- Click OK
Format the Data Validation Cells
Next, format the date selection cells, so they stand out on the worksheet
- Add a border to the cells, to highlight them.
- (Optional) Apply a fill colour to the cell, to indicate that they are data entry cells
- Apply a number format to the cells, using one of the date formats, such as Short Date
Choose Dates from Drop Down Lists
Next, choose dates from the drop downs, so you'll be ready to set up the worksheet formulas
- First, choose any date from the Start cell's drop down list
- This will be the minimum date for the chart's data
- Next, choose an End date that is on or after the Start date
- This will be the maximum date for the chart's data

Create Summary Formulas
Next, you'll create a summary section on the chart sheet, below where the chart will go later.
On the Chart sheet, in cells B15:B18, type the following list of product colours
sold:

After the list is entered, follow these steps to create formulas. These will calculate the total units sold, for each colour, in the selected date range:
- In cell C15, enter the following formula:
- =SUMIFS(UnitsList, DateList,">=" & $C$2, DateList,"<=" & $E$2, ColourList,B15)
- Copy the formula down to row 18, to get the totals for all four colours.

Here's how the SUMIFS formula works in cell C15:
- Amounts in the UnitsList range will be totaled
- IF the order date (DateList) is greater than or equal to the Start date in cell C2
- AND the order date (DateList) is less than or equal to the End date in cell E2
- AND the order colour is the same as the colour listed in cell B15 (Red)
The formulas in cells C16 to C18 work the same way, and get results for the colours listed in those rows.
Tip: To learn more about the SUMIFS function, and to see other examples of summing with multiple criteria, go to the Excel Sum Function Examples page.
Create the Chart
Create a chart from the summary list.
- On the Chart sheet, select data and heading cells B15:C18.
- On the Excel Ribbon, click the Insert tab
- In the Charts group, click the Column chart command
- In the 2-D column chart section, click the first option - Clustered Column
A 2-D column chart is added to the Chart sheet, with the colour names along the horizontal axis (Category Axis).
There are no Legend entries, but there is a generic title at the top - Chart Title

Follow these steps to format the column chart:
- Drag the handles on the chart border, to adjust the chart size, so it fits
in the space between the date selection cells, and the summary list.
- Click on the first column, to select the chart series
- Click on the Red column again, to select that column only.
- Right-click on the Red column, and click the Format Data Point
command
- In the Format Data Point pane, click the Paint bucket icon, to see the Fill and Border setting options
- To the left of the Fill heading, click the triangle, to see the options.
- At the bottom of the Fill section, for Color, click the drop down arrow, and select the red colour

- Click on each of the remaining columns, and choose a colour in the Format Data Point pain, , to match their label.
- Finally, click on the Chart Title, and delete it
- OR, change the chart title text to something meaningful.

Test the Chart
To make sure the chart is working correctly, follow these steps to test the dynamic date range chart:
- From the Date Range selection cells, select different dates.
- The chart should update immediately, to reflect the new date range
Download the Sample Files
1) Chart Data Only: To follow along with the instructions on this page, download the Interactive Chart Data Sample File. The zipped file is in xlsx format, and does not contain any macros
2) Excel Tables: To follow see the completed interactive chart, using an Excel Table, download the Completed Excel Tables Chart sample file. The zipped file is in xlsx format, and does not contain any macros
3) OFFSET Formulas: To follow see the completed interactive chart, using OFFSET formulas, download the Completed OFFSET Chart sample file. The zipped file is in xlsx format, and does not contain any macros
More Tutorials
Naming Ranges
OFFSET Function
Excel Names Macros
Create Dynamic Ranges With a Macro