Excel chart learning: bar chart comparing actual and target

Speaking of charts, the images that pop up in everyone’s mind should be the styles that come with Excel. Not to mention that they are not good-looking, and these built-in templates cannot display data intuitively. It is obvious to hand over such charts to leaders. is inappropriate. The following article will share with you an Excel stacked column chart tutorial to teach you how to make different contrasting column charts, which will amaze everyone when making work reports.

Everyone knows that EXCEL charts have many types, such as column charts, bar charts, line charts, pie charts, etc. Everyone makes the same charts at work, so how do you use the simplest data to make a high-end chart? Today I will share with you an Excel stacked column chart comparison case.

As shown below, this is a sales data of the company's salesmen, listing the sales volume of each salesperson and the indicators that need to be completed.

The picture below is the final effect of the chart we are going to learn to make today. The chart is equipped with arrows that clearly list whether each salesperson has completed the target and the gap between the target and the target. The downward arrow indicates that the indicator is below the indicator, and the upward arrow indicates that the indicator is above the indicator. How to make this comparison histogram? Hurry up and learn!

1. Add auxiliary column

How to use Excel to make a histogram? First we need to make several auxiliary columns based on the source data. Here we will explain to you how to make the auxiliary columns of each series based on the color of the chart series.

##Dark blue=IF(B4>=C4,C4,B4) means if the sales is greater than or equal to the indicator, return the indicator otherwise return the sales.

Red=IF(B4

means if the sales is less than the target, return the difference otherwise return empty. ,c4-b4,na()) >

Cyan=IF (B4>=C4,B4-C4,NA()) means that if the sales is greater than or equal to the indicator, the difference will be returned, otherwise it will be empty.

How do we make the arrow? It can be set to the data label of the line chart, minus 10 is the position of the data label.

Red arrow=IF(B4

means if the sales is less than the target, return the sales minus 10 otherwise return empty. ,b4-10,na())

cyan Arrow =IF(B4>=C4,C4-10,NA()) means that if the sales volume is greater than or equal to the indicator, return the sales volume minus 10, otherwise it will return empty.

2. Insert chart

Then you can insert the chart based on the auxiliary column. Hold down the Ctrl key, select cells A3:A9 and D3:H9 respectively, click under the Insert tab, insert column chart - stacked column chart.

The chart results obtained are as follows.

3. Modify the chart type

Now you need to modify the chart type of the red arrow and cyan arrow series to "with Line chart with data markers."

When you click on the chart, the chart tool will appear on the upper tab. Click "Change Chart Type" in the design tab below the chart tool.

Make changes in the lower group of the "Change Chart Type" window.

4. Format settings

4.1 Modify the line chart without lines

Modify the line chart in the chart with connecting lines to wireless strip.

Select the data label with the connecting line, press CTRL 1 or double-click, the "Format Data Series" window will pop up on the right. Select Lines under Series Options—No Lines.

##4.2 Data series fill color

Now modify this data series to the color you want . Double-click the data series, select Fill-Solid Color Fill under the series options in the "Format Data Series" window on the right, and select the corresponding color.

Change the color as follows:

4.3 Add arrow

The next step is to change the data markers into arrows. Click on the Insert tab, then Shape - Arrow:.

Double-click the arrow, in the "Format Shape" window on the right, change the arrow color to be the same as the red series color, and the line to "No Line".

Click the arrow to copy, and then click the red series data label to paste. In the same way, you can click the red arrow to copy the cyan arrow, rotate it 180 degrees, modify the color without lines, and then copy and paste it on the cyan series data label. Complete as follows:

4.4 Add data label

Then click on the red series and cyan series, and click the plus sign on the upper right side of the chart respectively , add data label—centered.

4.5 Other modifications

Finally make other modifications, click on the legend below and press Delete to delete.

Double-click the chart area, set the color in "Format Chart Area" on the right, and fill it with a solid color.

Modify the chart title. The final result is as follows.

How about it, have you learned it? Download the courseware quickly and practice by yourself!

Related learning recommendations: excel tutorial

The above is the detailed content of Excel chart learning: bar chart comparing actual and target. For more information, please follow other related articles on the PHP Chinese website!