Excel chart learning: making a combination of line chart and column chart

How to make a combination chart of line chart and column chart? The following article will take you to talk about how to make a combination chart of line chart and column chart. I hope it will be helpful to everyone!

Please use Excel 2013 or 2016 version to be consistent with the tutorial for easy learning.

At the end of the year, the general manager of the company needs to know the profit situation of each area throughout the year, including the profit amount and year-on-year growth compared to last year. The following is the data obtained from the sales department.

Chart ideas:

The table contains two sets of data of different natures. The annual profits vary from region to region. In order to be able to To intuitively display data, it is suitable to use the volume of the column chart to enhance the amount. For year-on-year growth or decrease, it is suitable to use the climb or decline of the line segment of the line chart to enhance the fluctuations in the data. We can make two separate graphs to express this, or we can cleverly combine a column graph and a line graph.

Let’s take a look at the production process of combined expressions.

Production process:

Part 1: Generate chart

Step 01 Select the plot data and click " Insert tab"-"Chart Dialog", click the dialog box launcher in the lower right corner.

Step 02 In the [Insert Chart] dialog box that opens, compared to 2010 and previous versions, the 2013 version and the 2016 version will automatically be given based on the selected data Recommended chart types. Then we use the recommended first chart and just double-click on the right side.

At this time, Excel will generate a chart. The following is the layout and beautification of the charts.

Part 2: Preliminary Beautification of Charts

Step 03 The legend is displayed at the bottom by default. Double-click on the legend below and click on the top.

Step 04 The distance between the columns in the chart is too wide. Double-click the column chart and set the [Gap Width] to 50% in the Format Data Series.

Step 05 Enter "Profit amount and year-on-year growth of various regions across the country" in [Chart Title], and then set the font, font size and color in [Start Tab] . Here I set it to bold, the font size is 12, and the color is black.

Step 06 Beautify the lines. Double-click the line chart in the chart, set the mark to [Built-in], the type to dot, the size to 8, and the fill to white.

The effect after setting is as follows:

Step 07 In order to make the abscissa axis look better, let’s set the abscissa The line color of the axis. Double-click the abscissa axis, set the line to [solid line], the color to black, and the width to 1 point.

Step 08 Add data labels. Click the column chart, then click the cross in the upper right corner, check the data label, and you can add data to the chart. Then insert a text box to write the data source, copy-paste it into the chart, and drag it to the lower left corner.

Use the same method to add data labels to the line chart. The final chart effect is as follows.

Are you satisfied with this effect? People who work delicately may have questions: The data labels of the line chart and the data labels of the column chart overlap and interfere with each other. Can they be separated? Indeed, this is a question. If you present it to the manager, you will definitely be criticized: Who is this data? Let's put it this way, the current chart is said to be anti-human, which is overstated, but it is definitely anti-eyes and anti-brain efficiency.

Part 3: Improve the usability of charts

In order to solve the above problem, we can put the line chart on the secondary axis and then modify the secondary axis The maximum and minimum values ​​of the vertical axis of the coordinate axis are achieved.

Step 09 Select the line chart, right-click the mouse, select "Change Series Chart Type", and check the secondary axis in the dialog box that opens.

Step 10 Modify the secondary vertical axis scale value, the maximum value is 0.3%, the minimum value is -0.3%; modify the primary vertical axis Axis scale value, the maximum value is 150, the minimum value is 0.

Step 11 Hide the secondary vertical axis. Double-click the secondary vertical axis and set the axis label position to None.

The same operation hides the main vertical axis.

Step 12 Delete the grid lines.

The current table is sufficient in terms of visual efficiency, and it better expresses the profits and year-on-year growth rates of each region that the manager wants to see. However, if you want to get better recognition, you need to go one step further. How to go? Two tricks:

(1) Supplement the key information that the chart expresses is not yet prominent. Key information should be concise. For example, the key information related to this chart is who has the highest profit and who has the largest growth rate.

(2) Allocate colors according to the manager’s preferences.

Part 4: Ultimate Adjustment

Step 13 The manager is a capable woman with a bit spicy personality, so she chose a combination of black and pinkish red. The source of information is secondary content. The font size has been changed to a smaller size and the color has been changed to gray to make it less prominent. Key information is also added. The final effect is as follows.

The above colors are not the only ones. Different colors can be matched according to different leaders. You can refer to the following colors:

Ok, the correctness of column chart and line chart Have you learned the combination?

Related learning recommendations: excel tutorial

The above is the detailed content of Excel chart learning: making a combination of line chart and column chart. For more information, please follow other related articles on the PHP Chinese website!