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.


Download Practice Book

You can download the practice book from here.


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

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-by-Step Procedures to Create Excel Chart Using Data Range Based on Cell Value


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.

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

  • After that, select the range B6:C10.

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

  • 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.

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

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

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


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.

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

  • 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))

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

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.

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

  • 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))

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

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-by-Step Procedures to Create Excel Chart Using Data Range Based on Cell Value


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.

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

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

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

  • 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.

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

  • 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.

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

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

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


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.

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

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

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

  • 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.

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

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.

Read More: How to Change Chart Data Range Automatically in Excel (2 Easy Ways)


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. Visit the ExcelDemy website for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo