Office hacks: making charts in the bar chart column

Representing multiple data in one chart is the most common form of daily office work. For example, if you want to present the sales of each product for each month in the product total sales bar chart, how can you do it? In fact, you can simply add a scatter chart to the bar chart to form a combination chart to present the sales of each product for each month on each bar (Figure 1).

1813A-TZTB-1

First, the existing data are organized, the annual sales of each product in a separate column (column Y), and add a good auxiliary column (column X), the auxiliary column from 1 to fill down in order, between the two types of products separated by an empty line, delete the corresponding auxiliary column values. In this way, the original data area A1:A8 and F1:F8 can be used as the data source of the bar chart, and I1:J35 can be used as the data source of the scatter chart (Figure 2).

1813A-TZTB-2

Next, select the A1:A8 and F1:F8 data areas and insert a bar chart. Right-click the bar, select “Select Data”, click “Add” at the legend item on the left side of the pop-up window, enter “Sales per year” at the series name in the pop-up window, and select “J2:J35” at the series value. Click “Edit” at the horizontal axis labels, and select I2:I35 at the axis label area of the pop-up window. Select “Combined” in the “All Charts” tab, select “Scatter Chart with Smoothed Lines and Data Markers” in the Scatter Chart series, and check its “Sub-Axes” (Figure 3). (Figure 3).

1813A-TZTB-3

Right-click on the bar series in the chart, select “Set data series format”, and in the “Series options”, set the series overlap to 0% and the series width to 20%. Right-click on the horizontal axis of the secondary coordinate, select “Set Axis Format”, and in the “Axis Options” tab, set the maximum value of the axis boundary to 35 (because 7 The maximum value of the axis boundary is set to 35 (because there are 7 commodities (columns), each with 4 points and 0.5 position in front and back, so (4+0.5+0.5)*7=35). Select the vertical axes of the primary and secondary coordinates separately, and also set the maximum value of their boundaries to 35 (because the maximum value of the total does not exceed 35) (Figure 4).

1813A-TZTB-4

Finally, select the chart and use the “Design” tab to choose the style of the chart you need. Of course, you can also select the scatter chart series, and you can customize the style of the scatter chart. Select the horizontal axis of the secondary coordinates and choose “None” at the “Label position” of the labels in the axis options. Then add the required elements to the chart as needed.

This way a unique chart is created.

Leave a Comment