# Excel Chart for Date Range

Select start and end dates, and the chart changes to show that date range. This technique uses dynamic named ranges

## Introduction

In this example, dynamic range names are used in formulas that summarize a list of sales records, and a chart is based on the result. When new records are added, or a different date range is selected, the chart is immediately updated.

## Create a Table

1. In a new workbook, delete all except two worksheets.
2. Rename Sheet1 as SalesData
3. Rename Sheet2 as Chart
4. On the SalesData sheet, in cells A1:D1, add the headings, Date, Item, Colour, Units, as shown at the right.
5. Enter sample data in each column, or download the sample file and use that.

## Create First Dynamic Range

A dynamic range will be created for the Date column.

1. Choose Insert | Name | Define
2. Type a name for the first range -- DateList
3. In the Refers To box, enter an Offset formula that defines the range size, based on the count of numbers in the Date column:
=OFFSET(SalesData!\$A\$2,0,0,COUNT(SalesData!\$A:\$A),1)
The arguments used in this Offset function are:
1. Reference cell: SalesData!\$A\$2
2. Rows to offset: 0
3. Columns to offset: 0
4. Number of Rows: COUNT(SalesData!\$A:\$A)
5. Number of Columns: 1

## Create Remaining Dynamic Ranges

The remaining dynamic ranges will be based on the DateList range.

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
4. Type a name for the next range -- UnitsList
5. 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
6. Click the OK button

## Create the Date Range Selection Cells

Use Data Validation to create drop down lists for the chart start and end dates.

1. On the Chart sheet, select cells C2 and E2.
2. Choose Data | Validation
3. From the Allow dropdown, choose List
4. In the Source box, type:   =DateList
5. Click OK
6. Add a border to the cells, to highlight them.
7. Select a sample date in each list, to be used in the Summary formulas.
8. Format the dates as desired (Format | Cells, Number tab)

## Create the Summary Formulas

Use the SUMPRODUCT function to total the sales for each colour.

1. On the Chart sheet, in cells B15:B18, type the list of colours sold.
2. In cell C15, enter the following formula:
=SUMPRODUCT(--(ColourList=B15),(UnitsList),--(DateList>=\$C\$2),--(DateList<=\$E\$2))
3. Copy the formula down to row 18.

## Create the Chart

Create a chart from the summary list.

1. On the Chart sheet, select cells B15:C18.
2. On the toolbar, click the Chart Wizard button
3. Create a column chart from the data
4. Remove the chart legend, and adjust the chart size so it fits in the space between the date selection cells, and the summary list.
5. Click on the series to select it
6. Click on the Red column, to select it.
7. Double-click on the Red column, to open the Format Data Point dialog box
8. For Area, select the red colour, then click the OK button.
9. Double-click on each of the remaining columns, and colour them.

## Test the Chart

1. From the Date Range selection cells, select different dates.
2. The chart should update immediately, to reflect the new date range

## Search

Search Contextures Sites

Search Contextures Sites

Last updated: October 4, 2016 7:28 PM