Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

In our daily work, we often encounter data comparison charts. Data trend charts are also charting skills that we must know. I believe many students can complete them. But recently, a classmate’s weird leader suggested: “It needs to have both a comparison effect and a trend effect, and multiple series of charts must be displayed by month throughout the year, and all of this must be done in one chart!”

So, the "difficulty" in our work lies not in lack of skills, but in the boss's imaginative thinking. So today I will give you an idea for making a "multi-series comparison/trend chart".

[Data source]

Before we look at the following article, students can think about it themselves if such data makes If you design it, what will it look like?

【Text】

●If there is only 1 set of data

Nothing to say, just use column chart, bar chart, donut chart, whatever you want.

Even if we need to see percentages and trend lines, we can insert an auxiliary column of percentages in column C in the data, as follows:

STEP1: Select the cell A1:B4 area, insert the toolbar - chart - clustered column chart;

Then you get the first draft of a column chart.

STEP2: Right-click anywhere in the chart area, and in the pop-up menu, left-click and select "Select Data".

In the pop-up setting box, click "Add" series on the left, enter data as follows or select the worksheet area to enter content;

STEP3:Right-click anywhere in the chart area and select "Change Chart Type";

Change the chart type of the percentage series to Scatter with Smooth Line and Data Markers.

This way we got the prototype of the chart for the second step.

STEP4: Select the graph of the proportion series in the figure, right-click the mouse, and select the "Format Data Series" option in the menu;

In the series options, click "Secondary Axis",

Get the chart, as follows:

Let’s beautify the chart interface and add data labels. We won’t go into details. Let’s give an example.

●Let’s look at today’s main data

I have to say that in actual work, the production of charts should still focus on , there is a prominent point to express a certain data. If a lot of information is crowded into one picture, it will appear confusing.

The combination of column chart and line chart (the author uses a scatter chart here) is very commonly used, and this combination has both contrast and trend, which is very convenient to watch. So can our main data today also achieve such an effect? As shown below:

[Initial drawing planning]

If we decide to use a column chart and a line chart to represent this table, then we need to have a rough sketch on paper or in our mind: each set of graphs corresponds to a project team’s 12-month period. data. The difficulty is that the bottom ends of each group of graphics must be aligned; the polyline must appear in the group of graphics for this project.

[Chart type selection]

Obviously, if multiple column charts are superimposed, we should choose "Stacked Column Chart", but if we use If you operate on the original data, it will be the chart below.

The bottom ends of each item group are not aligned, which is not what we need, but if we insert a "placeholder graphic" in the middle of each series, is it okay? What about "raising" each series of graphics? Then set the color and border of the placeholder graphic to "no color" to simulate the effect of each row being a column chart and aligned at the bottom, as follows:

Students can take a look. Here we have removed the colors of the placeholder graphics at points A and B. The green placeholder graphics at point C are still retained. Can you see that the bottom ends are aligned.

After we have space for placeholders, we can add scatter points to these spaces to mark the proportions, and finally connect these scatter points to simulate the effect of a line chart. So for the trend line, we use "scatter chart with smooth lines and data markers".

[Organizing data]

We need to insert a placeholder graphic between the two series of graphics. If considered in the data table, it is to add an auxiliary data . Let’s take a look at the data sorting stage.

"Good charts are often not made from source data." Being good at using auxiliary data is an important sign of advanced chart making, so there is also a saying "Use of Charts" , in the end it all comes down to the use of functions.”

The "red" characters in the above picture are the data label display values ​​of the series, while the "blue" characters are the placeholder graphics of the chart and the marking points of the scatter points. Let's take a look at each data one by one. Function design idea:

● A: Data label value of item A

• ##B8 ​​cell function: =B2

● Point A placeholder: Placeholder graphic of project A

• B9 cell function: =MAX($B$2:$M$4)*2-B8

Explanation: B2:M4 is the source data area. We use twice the maximum value in the data as the highest proportion reference in the graph. Then the graphs made by each project will be evenly proportioned. The principle is: the number of data points The value corresponds to the placeholder point value = the maximum value in the data source * 2, so the corresponding placeholder point value = the maximum value in the data source * 2 - the data point value

● B: The data label value of project B

• B10 cell function: =B3

● Point B placeholder: Placeholder graphic of project B

• B11 cell function: =MAX($B$2:$M$4)*2-B10

● Point C placeholder: placeholder graphic of project C

• B12 cell function: =B4

● Point C placeholder: placeholder graphic of project C

• B13 cell function: =MAX($B$2:$M$4)*2-B12

● Proportions of A, B, and C: items A, B, and C Data points, respectively, account for the proportion of the total data of all projects in this month

• B14 cell function: =B2/SUM(B$2:B$4)

• B15 cell function: =B3/SUM(B$2:B$4)

• B16 cell function: =B4/SUM(B$2:B$4)

● Proportion point A: The trend chart of project A, corresponding to the coordinate position of the scatter point.

• B17 cell function: =B8 B9*1/3

Description: The scatter plot itself requires two horizontal and vertical coordinates Yes, if we do not set the The project occupies one-third of the site.”

● B and C proportion points: trend charts of B and C projects, corresponding to the coordinate positions of the scatter points.

B18 cell function: =SUM(B8:B10) B11*1/3

B19 cell function: =SUM(B8 :B12) B13*1/3
Note: Students should note that because the scattered Y-axis coordinates of each project have "height" requirements, their data sources, It is to accumulate other project data and add one-third of the corresponding proportion point, so that it is closer to the corresponding column chart.

• 【Chart Production】

The most difficult part of data sorting has been completed. Do you still remember how we inserted the chart when we did a single set of data? Hurry up and give it a try!

Blue area: Column chart

Select the A7:M13 area, insert "Stacked Column Chart", and add A , B, and C series add "data labels"

Yellow area: scatter plot

Follow the steps for designing charts for a single set of data at the beginning of the article, and repeat them Steps 2 and 3.

• Select the B17:M17 area and insert "Scatter plot with smooth lines and data markers"

• Select the B18:M18 area and insert "Scatter plot with smooth lines and data markers"

• Select the B19:M19 area and insert "Scatter plot with smooth lines and data markers"

Add "Data Label"

Add the "Data Label" of the scatter plot, and select "Data Label". In "Set Data Label Format", set it to Press the corresponding "value in the cell" to display the content:

In this way, students will get the following chart "blank".

[Post-arrangement]

For post-arrangement work, you can refer to the author’s E diagram of “one deletion, two adjustment, three rows” ” approach to deal with it.

1. Delete the "vertical axis" and "tick marks" in the chart, as well as the legend that does not need to be displayed;

2. Adjust the column shape The color of the graph and the color of the smooth line, set the placeholder columns that do not need to be displayed and the mark points on the smooth line to no line and no fill.

3. Through the above operations, the chart has been basically formed. Select a series of the column chart and adjust the gap width to 5%.

Adjust the data label position on the column chart to the inside of the axis, and adjust the data label position on the smooth line to the center;

Then adjust the position of each element in the chart, and change the font, font color, etc., to complete the effect of our picture above.

【Editor's Note】

Regarding the production of charts, it is recommended that you do not use all the default effects of Excel. A chart is more like a graphic design. Everyone’s aesthetics are different and the results are different. However, since “visualization” is meant to be viewed by others, it must be beautiful. Finally, let me share the color scheme used in today's graphic tutorial. Just enter the RGB value in "Other Colors - Customize".

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution. For more information, please follow other related articles on the PHP Chinese website!