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.

1. In a new Excel workbook, rename Sheet1 as sheet name SalesData
2. Add another sheet, and rename the new sheet as Chart
3. On the SalesData sheet, in cells A1:D1, add the headings, Date, Item, Colour, Units, as shown below.
4. 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

1. Recommended: Format the sales data list as a named Excel Table, then create named ranges based on the table columns.
2. 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:

1. Select any cell in the table
2. On the Ribbon, under the Table Tools tab, click the Design tab
3. At the far left of the Ribbon, click in the Table name box, to select the existing name
4. 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
  • Range size is one column

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 )

1. Type a name for the next range -- ColourList
2. 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:
   1. Reference range: DateList
   2. Rows to offset: 0
   3. Columns to offset: 2
      
3. Click the Add button

Dynamic Range for Units (Column D)

1. Type a name for the next range -- UnitsList
2. 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:
   1. Reference range: DateList
   2. Rows to offset: 0
   3. Columns to offset: 3
      
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.

1. On the Chart sheet, type headings for the date selection cells
• C1: Start
• E1: End
2. Next, select cells C2, then press Ctrl, and select cell E2
3. On the Excel Ribbon, go to the Data tab
4. In the Data Tools group, click Data Validation
5. In the Data Validation dialog box, go to the Settings tab
6. From the Allow drop down, choose List
7. In the Source box, type:   =DateList
8. 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:

• Red
• Yellow
• Green
• Blue

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

Format the Column Chart

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:

1. From the Date Range selection cells, select different dates.
2. 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