Office hacks: the combination of real and imaginary 折线图显示更抢眼

Companies often use line charts to display sales by department and by month. By default, the inserted line chart looks like a jumbled mess. If you make an interactive chart using drop-down lists, the line chart will be more eye-catching and clearer when different selections are made and the line chart can be displayed in real or imaginary form according to the selection (Figure 1).


First, click any cell in the data area to insert a line chart with data markers. Select any of the data series in the chart, right-click, select “Set Data Series Format”, in the “Set Data Series Format” window on the right side of the “Series Options → Fill & Line “tab, set the line to light gray; then click the other data series in turn, press the F4 key, the line color all set to light gray; and then select one of the data series, set the data marker fill color to light gray; and then in turn, respectively, the other data series of the data point fill color and border color are set to light gray. Finally, delete the grid lines and legend of the chart (Figure 2).


Next, select the “Development Tools” tab, click “Insert → Form Controls → Combo Box”, draw a drop-down list box in the appropriate location in the worksheet; right-click the list box, select “Set Control Format “, in the pop-up window of the “Control” tab, “Data Source Area” at the selection of A3:A8, “Cell Link” at the selection of J1. Select the “Formula” tab, click “Name Manager”, click “New” in the pop-up window, enter the name “se “, the reference location, enter “=OFFSET($B$2:$G$2,$J$1,)”, in cell J2, enter “=OFFSET($A$2,$J$1,)&”Sales in the first half of the year” “, for the title of the chart.


Next, right-click the chart, select “Select Data”, click the “Add” button on the right side of the pop-up window, enter “se” for the series name, and enter “=Sheet1!se” for the series value. “=Sheet1!se”. After adding, the bars in the chart are now colored. At this point, you can beautify this series by setting the data series format (Figure 3).

Select the chart title, enter “=” in the edit field, then tap cell J2, enter, so that the chart title will follow the changes in the drop-down list.

Finally, set the fill color for the drawing area and chart area of the chart as needed. Right-click on the drop-down list and drag it to the chart area to combine the chart with the drop-down list. The result is an interactive line chart with the ability to switch between reality and reality.

Leave a Comment