Excel2016 Chart How to add a drop-down menu sort

you may be surprised to see the problem. The usual table sort, we always sort on the data in the table, is the chart can also be sorted? In fact, in order to better chart the data, we can also add a drop-down menu to the column chart, which enables you to sort the chart by the Drop-down menu (Figure 1). The following actions are completed in Excel 2016.

First insert a column chart and add a title as needed, and double-click the chart to beautify it as necessary. Then enter "Normal", "ascending", "E6" in the cell, E7, E8, descending, select insert → form control → combo box on the Developer tab to draw the combo box and resize it in the appropriate position of the chart; Right-click the combo box, select "Format Control", and in the pop-up window, "control" tab, the data source range selects E6:e8, cell link selects E9. When the settings are complete, click on the combo box to make a selection to see if the desired effect is achieved (Figure 2).

Next, set the interaction effect. In fact, for a chart to sort, you sort its data source according to the selection of the Drop-down list. Therefore, you need to reset the data source for the chart. First, click Name Manager in the Formula tab to create a new 6 name in the pop-up window, as shown in the following table (table).

Small tip:

The function of the large function is to return the nth maximum value in the array, and the small function is to return the nth minimum value in the array. The T function is to detect whether the return value of the offset function is text, if it is returned as it is. In fact, the role of JXXL is to return the order of the sales from large to small, jxyf to generate the corresponding month according to the JXXL return value; Similarly, the role of SXXL is to return sales of small to large sorting, SXYF is based on SXXL return value to generate the corresponding month.

Right-click the chart, select "Select Data", click "Edit" in the legend item on the left, enter "=SHEET1!XL" in the pop-up window's series value, click "Edit" at the horizontal axis tab on the right, and enter "=SHEET1!YF" at the Axis label area (Figure 3).

Finally, the chart and Drop-down list are selected and grouped, and the grouped objects are dragged to block the E6:e9 cell. After this setting, the chart can be sorted.