Shulou(Shulou.com)11/24 Report--

The original title: "have you ever seen such a beautiful line chart?" Do it in 3 moves and let your colleagues be dumbfounded! "

Recently, Xiaozhu has been analyzing the company's annual sales from 2008 to 2019.

When I passed her station when I got off work, I found that her data map was not intuitive at all. No wonder everyone came home from work, and she was still struggling.

"how can we make the data more intuitive? "

A simple trick is to add maximum, minimum, and average values to the chart.

The maximum and minimum values had better be marked on the chart and can be seen at a glance.

By adding averages, you can clearly see the annual sales, which are above the average and which are below, which years are better and which are lagging behind.

Highlight the key points and facilitate the analysis of the data.

Having said that, let's take the annual sales of a company from 2008 to 2019 as an example to teach you how to quickly add maximum, minimum and average lines to a line chart.

1 👉 Raw data:

▋ add secondary columns: maximum, minimum, average ❶ check cell C2 and enter the formula:

= IF (B2=MAX ($B$2:$B$13), MAX ($B$2:$B$13), NA ()) enter!

Formula meaning: if B2 cell is equal to the maximum value in B2:B13, this value is returned, otherwise it is # N / A.

❷ check the D2 cell and enter the formula:

= IF (B2=MIN ($B$2:$B$13), MIN ($B$2:$B$13), NA ()) enter!

Formula meaning: if B2 cell is equal to the minimum value in B2:B13, this value is returned, otherwise it is # N / A.

❸ select the E2 cell and enter the formula:

= ROUND (AVERAGE (B$2:B$13), 0) enter!

The meaning of the formula: first use the AVERAGE function to calculate the average value of the B2:B13 region, and then use the function ROUND to round its average value.

▋ insert line chart select the data area, click [insert]-"Chart"-"Line Chart".

At this point, the average has already appeared, and next, let's take a look at how the maximum and minimum values are displayed on the diagram.

▋ displays maximum and minimum valu

❶ select the drawing area, right-click, open [format drawing area], click the small triangle under [drawing area options], and select [Series maximum].

❷ check "wireless bar".

❸ under the "Chart tool"-"Design" tab, click "add Chart elements" on the far right, select "data label"-"other data label options", and open "format data label".

❹ clicks label options, that is,

Then check "Series name" and "value", and adjust the label position to "up".

❺ sets the minimum value in the same way.

Add maximum, minimum and average values to the line chart, and this is done, which is clear and intuitive!

Speaking of which, by the way, add a little knowledge about the chart.

If you insert a line chart, the data that is supposed to be the horizontal axis title also becomes a broken line.

It's like this:

It is most likely because the data in column 1 is numeric.

Solution ❶:

① converts numeric data into text data.

Select A2 cell, add English single quotation marks in front of the number, enter, drop-down fill.

Then insert the line chart on it!

Solution ❷:

① select the data except column 1, and click "insert"-"Line Chart".

② select the horizontal axis, right-click-[Select data], and edit the horizontal axis label.

③ select the data in column 1 except the title, and click OK twice!

In addition to line charts, bar charts and bar charts can also be inserted into average lines.

For example, the source data is still the company's sales in 2008-2019.

Procedure:

❶ adds secondary column "average"

❷ selects all, inserts the chart "cluster column chart"

❸ select the chart area, click the [Chart tools]-[Design] tab, find [change Chart Type], change the chart type to "combination Chart", change the average to a line chart, and make sure!

Such a bar chart with an average line is done, and then you can beautify it according to your own needs.

Speaking of beautification, let's take the previous line chart as an example to teach you how to make beautiful charts in simple steps.

👉, first take a look at the effect image:

Here, we need to use the following features:

❶ [format chart area]

❷ [format data series]

❸ [format major gridlines]

❹ [format axes]

❺ [font color].

As these are relatively basic, they will not be explained in detail, but will briefly talk about the areas that need to be adjusted.

❶ [format Chart area].

The main thing is to set the fill color, here I choose a custom color, RGB value: 64jue 64pr 64.

❷ [format data series].

Check smooth Lines in fill and Lines and set the Glow effect in effects.

❸ [format major gridlines].

Increase transparency.

❹ [format axes].

Check the "wireless bar".

❺ adjusts [font color].

Select "White, background 1, Dark 15%" here.

At the same time, modify the Chart title.

A beautified chart is complete!

👉 summed up:

Add an average to the chart, whether it's a line chart, a column chart, a bar chart, etc., if you learn the skills, you can do it!

Practice makes perfect. I believe you will be able to produce both beautiful and intuitive data charts.

In addition to the line chart beautification techniques described above, there are many kinds of charts in Excel, such as area chart, bar chart, pie chart, circle chart, scatter chart, waterfall chart and so on.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.