Excel chart learning: highlighting the maximum and minimum values ​​in the line chart

The line chart is a relatively simple and widely used chart. But as the saying goes, the simpler something is, the more attention it pays to details. Only when these details are in place can the boss be truly satisfied! Today, the editor will teach you how to grasp these two details - the maximum and minimum values, so that your line chart will impress your boss.

# Line chart is a type of basic chart that is often used in our daily work. Its function is to reflect the changing trend of data. For example, if you display monthly sales through a line chart, the numerical changes will be very intuitive:

Put aside the beautification of the chart, basically the line charts everyone makes are It looks like the picture above, and the line chart made by the veteran rookie looks like this:

There is only a slight difference from our line chart: there are two more Points, maximum and minimum values; one missing line, the 0-value grid line of the coordinate axis. Don't underestimate this difference. Because of this small difference, the veteran's bonus is always a little bit more than that of his colleagues.

The specific operation steps are introduced below, children's shoes will follow the operation together:

1. Add an auxiliary column in the data source: maximum value, and use the formula to extract the maximum and minimum values.

Formula: =IF(OR(B2=MIN($B$2:$B$13),B2=MAX($B$2:$B$13)),B2,"" )

Formula analysis:

OR(B2=MIN($B$2:$B$13),B2=MAX( $B$2:$B$13))'s function is to return TRUE when the sales volume is equal to either the maximum or minimum value. If they are not equal, it returns FALSE;

Use IF to judge. , when the result of OR is TRUE, the sales amount is returned, and when the result of OR is FALSE, a null value is returned.

2. Add a line chart

3. Modify the chart type of the auxiliary column

Change the chart type of the maximum value series to a scatter chart and uncheck the secondary axis.

4. Modify the data point format of the maximum and minimum values ​​

Operation points: When selecting a data point, the first time Click to select all, and click again to select individually; the colors of the maximum and minimum values ​​should be selected with greater contrast.

5. Adjust the coordinate axis format

Adjust the minimum value of the coordinate axis according to your own data.

After adjustment, you can see that the horizontal line corresponding to the 0 value is gone, and the blank part below the chart is hidden, as well as the dots that do not need to be displayed.

At this step, some friends may have questions. If your data needs to start from the 0 value and the grid lines cannot be hidden, what should I do with those extra dots?

is actually very simple. Replace the "" in the auxiliary column formula with #N/A. The modified formula is:

=IF(OR(B2=MIN( $B$2:$B$13),B2=MAX($B$2:$B$13)),B2,#N/A)

The effect is as shown in the figure:

The principle is also very simple. The error value #N/A cannot be recognized by the chart, but changing it to other error values ​​may not have this effect. Interested friends can test it by themselves.

6. Modify the chart title and delete the legend

At this point, the chart is actually completed, it’s not difficult, right? .

Seeing this, some friends may be wondering: You can directly modify the data point format of the maximum and minimum values ​​directly on the sales line. Why do you have to add an auxiliary column and use scatter? Clicking pictures is so troublesome.

The reason is this: If you have only one data source and it will not change, that is to say, if you only need to make a line chart once, it is no problem to modify it directly. But if there are multiple data sources, for example, each branch needs such a graph, you have to manually find the maximum and minimum values ​​on each graph. The significance of using auxiliary columns is that it can automatically find the required annotations. point, and avoid having to deal with it manually every time.

Sometimes it seems like a small detail in a chart, but it often takes a lot of effort to achieve. This is also one of the joys of making charts. Have you got this little tip today?

Related learning recommendations: excel tutorial

The above is the detailed content of Excel chart learning: highlighting the maximum and minimum values ​​in the line chart. For more information, please follow other related articles on the PHP Chinese website!