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, right–click 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.

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.

Download Practice Book

You can download the practice book from here.

Chart Using Data Range Based on Cell Value.xlsx

Conclusion

In this article, we have demonstrated step-by-step procedures to create Excel Chart Using Data Range Based on Cell Value. I hope this article will help you to perform your tasks easily. Moreover, we have also added the practice book at the beginning of the article. Furthermore, you can download it to test your skills. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.

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

Get FREE Advanced Excel Exercises with Solutions!