Excel "pot-like" contrast chart how to make

in order to show the contrast between the data, we often use the graph to express, for example, you can often see in various occasions in the column chart. Although the column chart performance data Contrast effect is also more clear, but the form is too common, lacks the creativity. If you make a "pot-like" chart style, different from the ordinary column contrast, it will look more intuitive (Figure 1). In order to make it easy for everyone to understand, we choose to produce a product requirement plan and a production sales comparison chart in the Excel 2016 environment, as an example to illustrate the specific method, other areas of use similar to this.

1. Data collation

Chart making, data advance. To make the above-style chart, you need to deal with the data in the worksheet in advance. First, the completion rate is constructed, such as the completion rate of the planned production = planned manufacturing/market demand, the actual production completion rate = Actual production/planned production, etc. Constructs 3 secondary columns, and the 1th auxiliary column is used to construct a set of invisible bar data. The data in this column is the sales of each line minus the market demand and then divided by 2, such as the formula for the D3 column is "= ($B $2-b3)/2", and two columns are constructed to simulate a set of scatter-chart data for placing data labels in the bar. The value of the X column is the largest value in the sales divided by 2, that is, the 390,y column input sequence number from large to small (Figure 2).

2. Structure Chart

When data processing is complete, hold down the CTRL key and select the A1:b6 and D1:D6 data regions. Switch to the Insert tab, insert the stacked bar in a two-dimensional bar, right-click the bar in the chart, select Select Data, and adjust the secondary column to the top of sales in the legend key on the left side of the window (Figure 3).

Right-click the ordinate, select Format axis, and in the Axis options on the right, check the reverse category. Also, right-click the horizontal axis, select format axes, and at the top of the Axis option boundary on the right, enter 100 (the maximum value in the Sales column). When you are finished, remove the horizontal and ordinate (Figure 4).

Select the bar that the secondary column in the chart represents, and set its fill to no fill on the right, and the border to "Wireless bar." Right-click the selected bar chart, select Add Data Label, and in the right label option, tick "category name", remove "value", select "Inside Data Label" at the label location, and adjust the plot area size (Figure 5).

3. Add tags

The outline of the "pot-shaped" chart is basically complete, and the next step is to add the unfinished rate and the sales label. Select the X, Y columns of data and copy, and then select the bar in the chart, and click Paste "Selective paste", in the pop-up window, add cells to select New series, the data (Y) axis selects columns at, check the first behavior series name, the category in the first column (x label); Select the newly added data point (that is, the green bar), right-click to select Change series chart type, and change it to a scatter chart (Figure 6).

Right-click the scatter chart, select "Add Data Label", select the added digital label, click "Label Options" on the right, remove the "Y-value" tick, tick "value in cell", select C2:c6 cell (that is, completion rate), select a scatter chart (that is, a green dot), set its fill to "No fill", and the Border is " Wireless bar; Select the completion rate data label again and set the label on the Tab tab to center. and manually adjust the single completion rate label on the top and bottom position, delete the right Y axis labels and legends; Right-click the bar in the chart, select Add Data label, and manually add the word "million" to each numeric label (Figure 7 )。

4. Add Series line

Select the bar chart, and in the Design tab, click Add chart element → line → series line. Select the added series line, set the dash type to "dash" in the format on the right, the width of 1.5 points, and set its glow effect in the Effects tab (Figure 8).

Finally, change the title and background of the chart. In this way, a different data comparison chart is done.