Office hacks: Excel control bar bar chart can also be made in this way

The use of ordinary bar charts to display data seems a bit too rustic, so someone had a sudden idea, whether the Excel bar chart into a dynamic form of control bar? After adding the control bar on the ordinary bar chart, the presentation will indeed look different (Figure 1). So, how to make such an effect of the control bar bar chart? In fact, the menu, functions, charts and other techniques used in combination to create a control bar form of the bar chart is not difficult.


(Figure 1 Column chart with dynamic control bar)

1. Build the drop-down menu

First, enter the desired year in a blank column (column F) of the original data table. Select the “Development Tools” tab and click “Insert→Form Controls→Combo Box” to draw a combo box in the blank space of the worksheet. Right-click this combo box, select “Set control format”, in the pop-up window, “Control” tab, “Data Source Area”, select the continuous year cells, such as $F$ In this way, when clicking the drop-down menu in the combo box, the value of cell G1 will be changed (Figure 2).


(Figure 2 Select the year from the drop-down menu)

2. Build the chart data

Switch to the “Formulas” tab, click “Name Manager”, click “New” in the pop-up window, type “Done” in the “Name” of the new pop-up window, and type “=OFFSET(Sheet1!$)” in the “Reference Location”. “Enter “Done” at “Name” in the new pop-up window, and enter “=OFFSET(Sheet1!$B$2,(Sheet1!$G$1-1)*8, 1,8,1)”; likewise, then create a new name of “unfinished”, “reference position”, enter “=OFFSET(Sheet1!$B$2,( Sheet1!$G$1-1)*8,2,8,1)”, so that the data needed to build the chart is complete (Figure 3).


(Fig. 3 Data required to build a chart with the OFFSET() function)

3. build a chart

Select a blank cell, switch to the “Insert” tab, click “Insert Bar Chart → 2D Bar Chart → Percentage Stacked Bar Chart”, insert a blank percentage stacked bar chart in the worksheet; right-click this chart, select Select Data”, click “Add” in the pop-up window at “Legend Item”, and enter “Series Name” in the new pop-up window. “In the new pop-up window, type “Done” at “Series Name”, type “=Sheet1! horizontal (category) axis label” click “Edit”, in the pop-up window “axis label area” select “=Sheet1!$B$2:$B$9 “; similarly, click “Add” again, enter “Incomplete” at “Series Name”, and enter “=Sheet1!$B$2:$B$9” at “Series Value Unfinished” at the “Series Value”, the axis labels do not need to be changed; then click “Add”, enter “Unfinished” at the “Series Name”, enter “Finish”, and enter “=Sheet1!Finish” at “Series Value”, without changing the axis labels. In this way, the dynamic chart is basically built successfully (Figure 4).


(Figure 4 Select the data source and build the chart)

4. Change the chart

Right-click the chart, select “Change Chart Type”, and change the first and second items to “Percent Stacked Bar Chart” in the “Combination → All Charts” tab of the pop-up window. Change the third item to “Line Chart with Data Marker” and check the “Sub-Axis” after the third item. Right-click the right side of the chart “axis”, select “Set Axis Format”, in the “Axis Options → Boundary → Maximum” to modify the data to “1”, and then delete the “right axis”, “grid lines”, etc.; right-click the chart of the “line graph “, select “Set Data Series Format”, and set the line to “No Line” in the “Fill and Line” tab. At this point, the “line chart with data markers” will be left with only the dots (Figure 5).


(Figure 5 to transform the line drawing into a dot drawing)

Next, draw a rounded rectangle, design a button effect through its “Shape Effect → Prism”, and then copy it, and then select the dot in the line chart, and paste it in. Right-click the chart in the “unfinished” part of the data chart, select “Set Data Series Format”, set the fill color to “white”, “Effect “tab,” “Shadows → Preset” at the selection of “Internal: Medium” effect, and then set the color to “dark gray”; then select the chart in the “Finish” series, set its effect, the same operation as above, the color is selected as needed (Figure 6).


(Figure 6 Design and beautification of control buttons)

5. Combination of charts and menus

Right-click the combo box control, select “Sort→Top”, drag the combo box to the appropriate position in the chart; the chart can be adjusted in detail, beautify the font, background, etc.. Finally, hold down the Ctrl key, select the chart and combo box controls to combine it.


Leave a Comment