Buzz
Creating dynamic charts in Excel provides you with the most visually intuitive view of evaluation tables and reports during Excel charting. Simply put, drawing dynamic Excel charts will enable you to see multiple reports, data with just one chart.
And especially, creating dynamic charts in Excel will greatly benefit you when using it for Word reports. This article will guide you through the Office 2016 version. You can also use Office 2013 and follow along, but download Office 2016 for the most support.
Guide to Creating Dynamic Charts in Excel
Step 1: Let's take an example where we have a table with years from 2010 to 2020 and revenue divided into 3 parts for each year. Now, we will create a dynamic chart in Excel based on this table.
First, you need to copy the entire table and paste it right next to it, calling it Table 2.
Step 2: Then, on Table 2, proceed to draw a chart by clicking on Insert and selecting stacked Column.
Step 3: Once selected, click on the chart, then click on the Data icon and choose Select Data.
Step 4: Here, you remove the year section, then click on Edit on the right-hand side and choose the year column values.
Step 5: Adjust the colors for each column to create a complete column. Now, I want revenue 3
Step 6: Click on the revenue 3 section, then choose Change Chart Type.
Step 7: Here, you further select revenue 3 and switch to Stacked Column, then click on the Line section.
After selecting Line, confirm with OK.
And the result will be a table like this.
Step 8: Next, you need to go to File > Choose Excel Options.
Step 9: In the Customize Ribbon section, check the Developer option to activate it.
Step 10: Then, back in Excel, you will see the Developer tab. Here, select Insert > Checkbox and place it on the screen.
Place a total of 3 checkboxes and rename them to Revenue 1, 2, 3.
Step 11: Right-click on each checkbox, select Format Control.
Step 12: For each checkbox, choose Cell link under Control.
And correspondingly, link each checkbox to the first empty value in the respective column.
For example, the doanhthu1 checkbox will select the corresponding value in the Revenue 1 column in Table 1.
Step 13: To check if it's accurate, simply tick the checkbox, if it shows TRUE or FALSE, it means the linkage is successful.
Step 14: Now, input the code at the first value of Table 2 with the structure:
=if ($B$1,B3,NA()).
With B1 representing the empty value displaying True or False of Revenue 1 column in Table 1.
B3 represents the first value of Table 1 if the result is true. Otherwise, it will display NA as in the formula.
After obtaining the result, repeat the formula for all 3 revenue tables.
The result will be as shown below, if you haven't checked the checkbox, the result will display #NA.
Experiment by checking 1 and 2 but leaving out 3.
Next, check 1 and 3 but leave out 2.
Finally, removing 1 but checking 2 and 3 will yield the result below.
And here, Mytour concludes the article. We have completed the guide on creating dynamic charts in Excel for 3 values. You can also apply this method to more than 3 values.
With the combined display when creating charts, Mytour believes that readers can combine various types of charts to create the most beautiful and intuitive Excel charts for their reports. If you use Google Sheets, you can refer to how to create charts in Google Sheets here.
One very useful trick in Excel is removing duplicate data in Excel. When you remove duplicate data in Excel, you will reduce the number of duplicate data in the spreadsheet, thereby reducing data processing.
