How to make Dynamic range for Excel Charts.


Sample video clip


Below are the steps to create a bar chart in Excel using the dynamic ranges named (date_range and data_range). These steps are designed for Excel 2013 and later versions:

Step 1: Select Data and Create Named Ranges



  1. Select the data range including dates (Column A) and data (Column B).
  2. Go to the "Formulas" tab, click on "Name Manager."


  4. Click "New" to create a new name. Enter "date_range" for the name and use the formula =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1) for the "Refers to" field.


  6. Repeat the process to create another name. Enter "data_range" for the name and use the formula =OFFSET(Sheet2!$B$2,0,0,COUNTA(Sheet2!$B:$B)-1).


Step 2: Create a Bar Chart

Select the range of data (including dates and values).

Go to the "Insert" tab and click on "Bar Chart" in the Charts group.

Choose the "Clustered Bar" chart.


Step 3: Edit Data Series

Click on the chart to select it.

Go to the "Chart Tools Design" tab.

Click "Select Data" in the Data group.

In the Select Data Source dialog, click "Add" to add a new series.


For the Series values, use =Sheet2!date_range for the dates and =Sheet2!data_range for the values.




Step 4: Save and Update

Save your Excel file.

When you add more data to Columns A and B, the chart will automatically update based on the named ranges.

Step 5: Optional - Add Titles and Labels

Add chart title, axis labels, and other elements to enhance the chart.

These steps should help you create a dynamic bar chart that automatically updates when you add more data to your worksheet.


Earn money by survey (Trusted and relay able site)