There will be various technical indicators statistics after the football game. The battalion commander found that there is a whirlwind bar chart that is used more often.
The picture below is a World Cup technical analysis chart seen from the CCTV-5 website.
Source: CCTV-5
http://worldcup.cctv.com/2018/match/13246022/index.shtml
Because this kind of bar chart can be used in comparison and benchmarking analysis, the battalion commander's curiosity drove a question: how can we use Excel to make such a chart?
A careful analysis of this chart reveals the following characteristics:
①The axis label is in the middle
②The data bar graph is on the left and right sides
③The bar graph has a gray background
④ The data scale is not the same standard
⑤ The side with large data is marked with color
Now try to simulate with an Excel chart, first prepare the basic data
Make a default bar chart
The default bar graph needs to be "mutated", and the main idea is as follows.
01. Set the secondary axis
Double-click the blue bar, set the data series format to "secondary coordinates", and the new coordinate scale will be displayed above.
02. Set the coordinate scale range
show all axes
Set the major and minor abscissa scale values to be -90 to 70
Set the intersection of the secondary ordinate and abscissa to 0, and reverse the scale values.
The effect after completion is as follows:
03. Reverse ordinate category
It can be found that the display order of the bar chart is opposite to the source data. You can modify the ordinate to set the "reverse order category".
The ordinate labels are repeated, and one ordinate label and the primary and secondary abscissa labels can be deleted.
Adjust the gap width to 50%, and the effect after adding data labels is as follows.
This can basically achieve the effect of key indicators comparison.
That's all for today's tutorial, I hope it helps you.
Wang Zhongchao
Office Practical Trainer/Enterprise Management Consultant
University of Science and Technology Beijing MBA off-campus tutor
Partner of Peking University Zongheng Management Consulting Co., Ltd.
Distinguished Lecturer of Microsoft (China) Employee Skill Improvement Project
Founder of WeChat Official Account Office Workplace Training Camp
Outstanding Author of the 20th Anniversary of the Computer Branch of Machinery Industry Press
20 years of corporate Office training experience
15 years of business management consulting experience
