Shulou(Shulou.com)11/24 Report--

Original title: "Let colleagues look silly, super handsome Excel dynamic chart!" "

Hello, everyone. I'm Xiaolan who knows a little bit about Excel.

I believe that many friends have worried that their charts are not innovative, intuitive, or beautiful enough, such as the following:

To this end, everyone has pursued the production of "high difficulty and high pressure grid" chart, but …... Is it a little bit more difficult?

In fact, a simple chart can be very "awesome". Just make it move, like this:

Like this:

And like this:

So what should we do with these moving charts? -- just use the Excel control!

Here it is:

(there is no [Development tools] tab, find and add in [File]-[options]-[Custom Ribbon]. )

Check box chart first, we need to prepare a set of data, as shown in the following figure:

In dynamic charts, check boxes are often used to control the hiding and display of data indicators, such as the display or hiding of averages.

👉 procedure:

❶ insert check box. Click the [Development tools] tab-[insert]-"check box"

Change the control text "check box" to "average"; set the properties of the control, right-click-"format the control", and link it to any cell (link to A9 cell here).

❷ adds secondary columns-- averages. Enter the following formula in the C2 cell, and then drop down to fill:

= IF ($Downs 1 million trust averge ($B$2:$B$8), # Nplink A)

Formula description: if the E1 cell is "TRUE" (that is, when the check box is checked), the average will be displayed; if not, it will be displayed as a # N / An error (the average line will be hidden).

❸ inserts the chart. Select the data area A1:C8, and click the "insert" tab-"Chart"-"Cluster column Chart".

Select the chart, right-"change the chart type", select "combination chart", and change the "average" to a line chart.

❹ chart beautification. Right-click, set the control to "put on top", and drag to the upper right corner of the chart

Press [Delete] key to delete extra chart titles, gridlines, etc.; modify color matching.

The dynamic chart of the check box is made, and the method is very simple. If you are interested, you can try it yourself.

Next, let's look at how to make a drop-down menu chart.

Drop-down menu chart can be used to show sales performance by year-to-month, by discipline, and so on.

Here, in order to facilitate the demonstration, we choose the previous source data to see how to display the box office on a daily basis.

It's like this:

👉 procedure:

❶ inserts the combo box. Click the [Development tools] tab-[insert]-"ComboBox"

; right-"format control":

Data source: $A$2:$A$8

Cell link: $Apati9

Number of items displayed in the drop-down: 7 (set according to the actual number of items)

After the setting is complete, left-click the control, and the optional option will be displayed:

❷ uses the name Manager to create a new custom name. Click the "Formula" tab-"name Manager":

New name ①: date

= OFFSET combo box type $Amal1 combo box type $Amal9jin1) take A1 as the reference, move down 1.

New name ②: box office

= OFFSET combo box type $Bog1 combo box type $Aguilar 9pr 0P 1) take B1 as the reference, move down 1.

❸ inserts a "clustered column chart" to modify the data source. Select the chart, right-[Select data source]

In the pop-up dialog box, modify the data series and axis labels.

❹ beautifies the chart. Press the [Delete] key to delete extra chart titles, gridlines, etc.

The second kind of chart has also been made. We mainly use it.

❶ control-combo box

❷ function-- OFFSET

❸ function-name manager.

And the third kind of scrolling chart will also use these. Oh, move on!

Scrolling chart effect display:

👉 procedure:

❶ inserts a scroll bar control. Click the [Development tools] tab-[insert]-"Scroll Bar"

Right-"format the control":

Minimum value selection 1

The maximum value is determined according to the data (total number of rows)

The cell links to a blank cell, such as D1.

❷ uses the name Manager to create a new custom name. New name ①: date 1

= OFFSET Scroll $Agg1 Roll $Dump10Jet 3) use A1 as a reference, move down 3.

New name ②: box office 1

= OFFSET Scroll $Back1 Roll $Dump10Jet 3) use B1 as a reference, move down 3.

❸ inserts a "clustered column chart" to modify the data source. Select the chart, right-click-[Select data Source], and modify the data series and axis labels in the pop-up dialog box.

❹ beautifies the chart. Press [Delete] key to delete redundant chart titles, gridlines, etc.; add data labels; modify color matching.

The last kind of dynamic chart, just do it.

To sum up, we talked about a total of three methods of making dynamic charts using controls today, all of which are relatively simple (the second and third methods are the same).

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.