Search Contextures Sites

Excel Names -- Use Range Names in Formulas

You can use a dynamic formula to define a range. As new items are added, the range will automatically expand.

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 use the data in the zipped Excel Names sample file.

Create the 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

Create the 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

Create the Date Range selection cells

Use Data Validation to create dropdown 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.

 

Download the Sample File

Download the zipped Names sample file

More Tutorials

1. Naming Ranges  
2. Using Range Names in Formulas    
3. 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

 

 

 

Related Tutorials

Naming Ranges  

Create Dynamic Ranges
With a Macro

 

 

Contextures
Excel news
by email

 

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

Learn how to create Excel dashboards.

 

 

 

Learn how to create Excel dashboards.

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

 

 

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: August 9, 2014 3:03 PM