What are the 3 ways to implement dynamic charts in Excel?

When using Excel for data analysis, you often need to draw data charts. Good charts can make people’s eyes brighter, and dynamic charts can better reflect the style and let your data display. Take it up a notch!

There are the following 3 methods for drawing dynamic charts in Excel.

1. Create an auxiliary area through sumifs to implement dynamic charts

2. Implement dynamic charts through offset and custom names

3. Implement dynamic charts through offset and development controls

Introduced below.

Create an auxiliary area through sumifs to realize dynamic charts

The effect is as follows:

Drawing method:

1. Set the selection sequence

Select cell C8, select the menu: Data-Data Verification-Data Verification, open the "Data Verification" window:

Allow : Selection sequence, source: Select the area indicated by the red box in the upper left corner

After the setting is completed, the selection sequence appears in cell C8:

2. Define the data source - use the sumifs formula

Copy the month to the bottom and define the data source through the formula sumifs.

Data source:

##sumifs formula: =SUMIFS(C3:C6,$B$3:$B$6,$C$8)

After writing the formula in the cell at the bottom of January, drag to the right to find the data for other months.

After the setting is completed, the data source will change when different amounts are selected.

3. Insert a line chart, reference the data source, and beautify the chart

Insert a line chart and select the data source defined in step 2.

Insert-line chart, select the chart area, Chart Tools-Settings-Select Data

Click the "Add" button and select the data source

Select the month for the horizontal axis label

After confirmation, you will get the preliminary completed chart. When you select different amounts, the chart will follow Changes, all that remains is to resize the chart so that it covers the auxiliary area and beautify the chart.

Related recommendations: "

excel basic tutorial"

The specific operations to beautify charts are as follows:

(1) Select a chart style

(2) Enter view, trend, and set the cell color to blue to get the final rendering.

Implement dynamic chart through offset formula and custom name

The effect is as follows:

##Drawing method:

1. Set the selection sequence

The method is the same as step 1 of "Establishing an auxiliary area through sumifs formula to implement dynamic charts".

2. Define the data source - through the offset formula and custom name

Idea: First obtain the downward offset through match, and then reference the data source area through offset.

Select menu: Formula-Name Manager

Create a new name "Case 2 Data Source" and enter the following formula ：

=OFFSET(Case 2!$B$2,MATCH(Case 2!$C$9,Case 2!$B$3:$B$6,0),1,1,8)

3. Insert a bar chart, quote the data source, and beautify the chart

After inserting the bar chart, quote the data source method:

=Excel Dynamic drawing preparation. Renderings.

Achieving dynamic charts through offset formulas and development controls

The effect is as follows:

Drawing method ：

1. Insert a radio control and set the control cell link

Select menu: Development Tools-Insert-Form Control, insert a radio control, and change the name to Recharge Amount

#Right-click the control and set the control cell link. Here, select cell C8.

Insert 3 more radio controls and modify the names: cash withdrawal amount, investment amount and repayment amount.

After the setting is completed, you can see that when you select different radio controls, the numbers in cell C8 will change, corresponding to 1, 2, 3, and 4 respectively.

2. Define the data source - through the offset formula and custom name

Idea: Use the offset formula and the number in the C8 unit to reference the data area.

Specific formula: =OFFSET(Case 3!$A$2,Case 3!$C$8,1,1,8)

##3. Insert Bar chart, quote the data source, and beautify the chart

This step has been introduced before. The operation is the same as before, just insert the bar chart, so I won’t go into details.

Summary

This article introduces three ways to draw Excel dynamic charts:

(1) Create an auxiliary area through sumifs to implement dynamic charts

(2) Implement dynamic charts through offset and custom names

(3) Implement dynamic charts through offset and development controls

The above is the detailed content of What are the 3 ways to implement dynamic charts in Excel?. For more information, please follow other related articles on the PHP Chinese website!