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 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:

• Press Enter to check the results.

• After that, select another cell and type the formula:

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

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

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

• 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

• Excel Chart by Month and Year (2 Suitable Examples)
• How to Add Multiple Trendlines in Excel (With Quick Steps)
• Excel Chart Not Updating with New Data (2 Suitable Solutions)
• How to Plot Time over Multiple Days in Excel (With Easy Steps)
• Excel VBA: Get Source Data Range from a Chart (with Quick Steps)
• How to Change Data Source in Excel Chart (3 Useful Examples)