# How to Create Excel Chart Using Data Range Based on Cell Value

In this article, we will learn to create an excel chart using data range based on cell value. Here, we will use the 2-D Column Chart to plot some data. You can use the same method on different types of charts. Today, we will demonstrate the step-by-step procedures to create a chart using data range based on cell value. So, without any delay, letâ€™s start the discussion.

## How to Create Excel Chart Using Data Range Based on Cell Value: Step-by-Step Procedures

To explain the steps, we will use a dataset that contains information about the Sales Amount on different Dates. We will make an excel column chart to represent this data. But the interesting fact is that the user can enter the starting date and ending date on a cell and the chart will contain information about the dates between them.

### STEP 1: Create Excel Column Chart

• In the first step, we need to create a column chart. You can create other types of charts using the same method.
• To do so, create a section where you will type the dates.
• We have created From and To sections like the picture below.

• After that, select the range B6:C10.

• Then, go to the Insert tab and select the Insert Column or Bar Chart icon. A drop-down menu will occur.
• Select Stacked Column from there.

• As a result, you will see a Sales Chart like the screenshot below.

### STEP 2: Generate Necessary Formulas for Name Manager

To complete the procedure, we will take help from the Name Manager. For that purpose, we need to create the necessary formulas that will give our desired results. It is very helpful to generate the formulas in the excel sheet first and then, copy-paste them into the Name Manager.

• We will try to generate formulas for the below dataset. Here, we want to select the range from the date 1/10/2021 to the date 4/21/2021. It denotes the range B6:B10.

• Now, select any cell in the dataset and type the formula below:
`=INDEX(\$B\$5:\$B\$14,MATCH(\$F\$4,\$B\$5:\$B\$14,0)):INDEX(\$B\$5:\$B\$14,MATCH(\$F\$6,\$B\$5:\$B\$14,0))`

In this formula, we have used the INDEX and MATCH functions together. This formula returns the range B6:B10. Here, we have two formulas separated by the colon (:) sign.

• INDEX(\$B\$5:\$B\$14,MATCH(\$F\$4,\$B\$5:\$B\$14,0))

This is the first part of the formula that returns Cell B6. The MATCH function looks for Cell F4 in the array B5:B14 and returns the row number in that array. So, the output of MATCH(\$F\$4,\$B\$5:\$B\$14,0) is 2. Ultimately, the formula becomes INDEX(\$B\$5:\$B\$14,2) and returns the date 1/10/2021 stored in Cell B6.

• INDEX(\$B\$5:\$B\$14,MATCH(\$F\$6,\$B\$5:\$B\$14,0))

This is the second part of the formula that returns Cell B10. The MATCH function looks for Cell F6 in the array B5:B14 and returns the row number in that array. So, the output of MATCH(\$F\$6,\$B\$5:\$B\$14,0) is 6. Ultimately, the formula becomes INDEX(\$B\$5:\$B\$14,6) and returns the date 4/21/2021 stored in Cell B10.

• Press Enter to check the results.

• After that, select another cell and type the formula:
`=INDEX(\$C\$5:\$C\$14,MATCH(\$F\$4,\$B\$5:\$B\$14,0)):INDEX(\$C\$5:\$C\$14,MATCH(\$F\$6,\$B\$5:\$B\$14,0))`

In this formula, we have again used the INDEX and MATCH functions together. But in this case, the formula will look for the range C6:C10. That means it will return the desired sales amount.

• INDEX(\$C\$5:\$C\$14,MATCH(\$F\$4,\$B\$5:\$B\$14,0))

This part of the formula returns Cell C6. The MATCH function looks for Cell F4 in the array B5:B14 and returns the row number in that array. So, the output of MATCH(\$F\$4,\$B\$5:\$B\$14,0) is 2. Ultimately, the formula becomes INDEX(\$C\$5:\$C\$14,2) and returns the sales amount of \$3200 stored in Cell C6.

• INDEX(\$C\$5:\$C\$14,MATCH(\$F\$6,\$B\$5:\$B\$14,0))

It is the second part of the formula that returns Cell C10. The MATCH function looks for Cell F6 in the array B5:B14 and returns the row number in that array. So, the output of MATCH(\$F\$6,\$B\$5:\$B\$14,0) is 6. Ultimately, the formula becomes INDEX(\$C\$5:\$C\$14,6) and returns the sales amount of \$3800 stored in Cell C10.

• Finally, hit Enter to see the results.

### STEP 3: Paste Formulas in Name Manager

• In step 3, you need to copy the formula below first:
`=INDEX(\$B\$5:\$B\$14,MATCH(\$F\$4,\$B\$5:\$B\$14,0)):INDEX(\$B\$5:\$B\$14,MATCH(\$F\$6,\$B\$5:\$B\$14,0))`
• After that, go to the Formulas tab and select Name Manager. It will open the Name ManagerÂ window.

• Select New from the Name Manager. It will open the New Name message box.

• In the New Name message box, type a name for the formula and paste the formula in the â€˜Refers toâ€™ field.
• We have named the formula MyDate.
• Click OK to proceed.

• Now, copy the formula below:
`=INDEX(\$C\$5:\$C\$14,MATCH(\$F\$4,\$B\$5:\$B\$14,0)):INDEX(\$C\$5:\$C\$14,MATCH(\$F\$6,\$B\$5:\$B\$14,0))`
• Then, navigate to the Formulas tab and select Name Manager.
• Click on New in the Name ManagerÂ icon.
• Type the name in the â€˜Nameâ€™ box and paste the formula in the â€˜Refers toâ€™ box.
• Click OK to move forward.

• After completing the above steps, the New Manager window will look like the below picture.
• Click on Close and apply the next step.

### STEP 4: Add Formulas in Select Data Source Window

• In the fourth step, we need to add the formulas in the Select DataÂ Source window that we created in the previous step in Name Manager.
• For that purpose, rightclick on the chart and click on the Select DataÂ option.

• Now, click on Edit in the Legend Entries section of the Select Data SourceÂ window.

• In the Edit Series dialog box, type =â€˜Chart Based on Cell Value!â€™\$C\$4 in the Series name box. Or you can simply type â€˜=Sales Amountâ€™.
• Also, type =â€˜Chart Based on Cell Value!â€™SalesAmount in the Series valuesÂ box.

Note: In the Series values box, you need to type =â€˜Sheet Name!â€™Formula Name that is given in Name Manage. Because excel will show a warning message if you just enter the formula name. The sheet also must be entered like the above-mentioned pattern.

• After that, click on Edit in the Horizontal Axis Labels section of the Select Data SourceÂ window.

• In the Axis Labels dialog box, type â€˜Chart Based on Cell Value!â€™MyDate and click OK to proceed.

• Finally, click OK in the Select Data SourceÂ window.

### STEP 5: Create Chart Using Data Range

• In this step, we will know if our method works or not.
• In order to do that, type 2/12/2021 in Cell F4 and 6/8/2021 in Cell F6.

• As a result, you will see an update in the Sales Chart. It will show the sales amount from 2/12/2021 to 6/8/2021.

Read More: How to Expand Chart Data Range in Excel

### STEP 6: Insert Excel Table to Make Dynamic Range

• In the last step, we will show how you can add more sales amount information in the range and update the chart automatically.
• To do so, select any cell in the dataset and press Ctrl + T on the keyboard.
• A Create Table window will appear.
• Click OK to proceed.

• Now, add another date and sales amount in Cell B15 and C15Â respectively.
• Also, type that in Cell F6.

• As a result, the dataset will be updated and you will see the sales amount of 7/8/2021 on the Sales Chart.

## Related Articles

<< Go Back to Data for Excel Charts |Â Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF