How to Create a Dynamic Line Chart with Checkboxes in Your Excel Worksheet

If the line chart in Excel will refer to a lot of data and information, this chart will totally be in a mess. Hence, you can use the checkboxes to create a dynamic line chart in your Excel worksheet.

Sometimes you will analyze a lot of data and information by just one line chart. However, all the information in one chart will interfere with your analysis. The image below shows a line chart that contains all the information.Oridinary Line Chart

The effect of this line chart is actually very terrible. Thus, you can follow the steps below and create a dynamic line chart for your data.

Arrange the Area

Here we will first insert some checkboxes into the worksheet. And if you have already added the tab “Developer” in the ribbon, you can directly move to step 5.

  1. Right click any of a tab in the ribbon.
  2. And then click the option “Customize the Ribbon” in the new menu.Customize the Ribbon
  3. In the “Excel Options” window, check the option “Developer”.Developer
  4. Next click the button “OK”. Thus, you have added the tab “Developer” in the ribbon.
  5. Now click the tab “Developer” in the ribbon.
  6. And then click the button “Insert” in the toolbar.Check Box
  7. In the drop-down menu, click the option “Check Box”.
  8. Then you need to use your mouse and click anywhere in the worksheet. Thus, you have inserted a new checkbox into the worksheet.
  9. Now right click this new checkbox.
  10. In the new menu, choose the option “Format Control”.Format Control
  11. In the “Format Control” window, link a cell for this checkbox. You can also directly click the cell and the cell reference will appear in the text box automatically.Cell Link
  12. And then click the button “OK”.
  13. Now repeat the step 6-12 to insert other checkboxes and link cells. In this example, we will insert 5 checkboxes in total. In addition, the linked cells are in range B14: F14. And in your actual worksheet, you can use other cells as linked cells for the checkboxes.
  14. After that, modify the texts of all the checkboxes. Thus, all the checkboxes are in corresponding with a specific product.Checkboxes
  15. In this step, input the same column header and row header into another area in the worksheet. In order to see those data clearly, here we input in rows below the original range.Same Header
  16. And then input the formula into cell B17 in this example.

=IF(B14,B2,NA())

This formula will refer to the linked cell of the first checkbox. Therefore, when you check the checkbox, the linked cell will show “True”, and the corresponding sales volume will also appear in cell B17.

  1. Next click fill handle of the cell and drag rightwards to fill other cells in the same row.Fill Cells
  2. You will see that in those formulas, all the references are relative references. And in this step, you need to change the references of the linked cells into absolute references. Below is the example of the cell B17:

=IF($B$14,B2,NA())

You need to change in other cells.

  1. After that, double click the fill handle of the cell B17 to fill the whole column.
  2. And then repeat the step 19 and fill other columns. Thus, when you check one checkbox, the corresponding numbers will appear.Show Numbers

Until now, you have finished the arrangement.

Create a Dynamic Line Chart

In this part, you will know how to create such a dynamic line chart.

  1. Click one cell within this new range.
  2. And then click the button “Insert” in the ribbon.
  3. Next click the button “Line” in the toolbar.
  4. In the drop-down menu, choose the line chart.Insert Line Chart

Therefore, you have inserted a line chart into the worksheet. And now you can check the certain checkbox if you need to show the trend in the chart.Show Certain Trend

  1. Now you can also make adjustment to make the chart better. In this step, change the size of the chart according to your need.
  2. Next press the button “Ctrl”.
  3. And then use your mouse to select all the checkboxes.
  4. Next right click in those checkboxes.
  5. In the new menu, move your cursor to the option “Bring to Front”.
  6. Next choose the option “Bring to Front”.Bring to Front
  7. And then move all the checkboxes into the chart area.
  8. If the checkboxes are mixed up with the legend, you can move them to make the chart clear.
  9. In this step, still select all the checkboxes.
  10. After that, click the tab “Format” in the “Drawing Tools” part.
  11. Next click the button “Align”.
  12. In the sub menu, click the options according to your need and arrange those checkboxes. In this example, we will click “Align Left” and the option “Distribute Vertically”. Therefore, the chart now is very clean.Align
  13. After that, don’t click any element in this worksheet. Keep those checkboxes selected. Press the button “Ctrl” on the keyboard.
  14. And then use your mouse to click the line chart.
  15. Still click the button “Format” in the ribbon.
  16. Next click the button “Group”.
  17. And then in the drop-down menu, click the option “Group”.Group

Thus, the checkboxes are linked with the line chart. The next time when you need to move the chart, you can drag the border of the chart. Therefore, the checkboxes will also move accordingly. Hence, they will always stay in the chart.

Result

Delete Useless Files in Your Computer

As time goes on, there will certainly be more and more files in your computer. Those useless files will take up many storage spaces, which will lower the speed of the computer. You may also find that the performance of Excel will become worse if there exist many useless files. Hence, it is necessary to delete them. If not, the next time you will meet with Excel corruption. And to repair Excel file problem, you can invest a powerful tool. This tool can solve all the problems in Excel no matter what the reasons behind those corruptions are.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.