Contextures

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.

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.

sample data in each column

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
  4. Click the Add button

define name

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
  3. Click the Add button
  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

define name offset formula

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)

Format the dates as desired

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.

Copy the formula down

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.

colour the columns

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 go to top

Download the Sample File

Download the zipped Chart for Date Range sample file

More Tutorials

Naming Ranges  

Create Dynamic Ranges With a Macro 

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search

Search Contextures Sites

 

Search Contextures Sites

 

Excel Tools Add-in

 

Free Pivot Table Tools

 

 

excel chart tools by peltier tech

 

 

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

Last updated: October 4, 2016 7:28 PM
Contextures RSS Feed