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

Original title: "the timescale of driving people crazy in Excel can be sorted out in 2 steps!" "

Hello, everyone. I'm starting to study the visualization of charts again.

When we visualize data, time-related data is particularly common.

However, sometimes, due to the long date scale and the insufficient width of the chart, the readability and aesthetics are not very good.

For example, I recently saw the following pictures.

The first one:

The second one:

The third one:

Let's complain and see, what's wrong with these time visualization charts?

problem? Of course:

❶ timeline display is incomplete

❷ timeline tilt

❸ Timeline rotated 90 °

No matter which of the above, it will drive you crazy to read, and the chart will look too ugly.

Next, we will provide you with several solutions to these problems.

1. Extract the classification name and put it in the chart title. For the data of the same month, we can refine the same elements into the title.

For example, the following figure is a chart for March, and you can see that the timeline is tilted.

The effect picture after refining the classification title:

Let's take a look at the specific operation methods:

❶ creates a daily secondary column in column C with the formula:

= DAY (A2)

❷ uses the day as the Abscissa by selecting data.

① Click Chart-right mouse button-Click [Select data]

② now appears "Select data Source" dialog box, press and hold [Edit] button

③ Select C2:C14 region

Its common area is March, so we can extract March into the title or Abscissa title.

The effect of the dynamic image is as follows:

Or add the title of the main Abscissa, and write the title of the main Abscissa for March.

Knock on the blackboard:

In the timescale, for those with common elements, we can refine the classification name and put it in the chart title or the corresponding axis title.

In most cases, however, the data are not limited to the same month, but may also have different periods of year, month and day.

At this time, the method of extracting the classification name to the chart title is not particularly practical.

So we need to classify the years, months and days on the timeline.

2. Classify and group the timeline. The following figure is a line chart generated directly from the time data:

There are different years and different months in the timeline of the chart.

The effect of the last time classification grouping, as shown in the following figure:

Let's take a look at how it works:

❶, we need to sort out the data.

First, sort the time series in ascending order, and then sort the data into the form of the following figure:

❷ creates a broken line graph and adds Abscissa classification labels.

① inserts a blank line chart

② selects the data through the right mouse button.

The name of the legend item selection series is: H1 (that is, the cell of sales volume)

The series value selects the H2:H25 area (that is, the data column of sales volume)

The horizontal (classification) axis label selects the E2:G25 area (that is, the data area of the collated year, month and day).

At this time, the classification timeline is complete.

Knock on the blackboard:

When we are faced with the time of different years, we can simply sort out the data first, and then modify the label of the classification axis to achieve the purpose of classifying the time axis.

Of course, there is another case where we do not necessarily need to display the names of all time points, we just need to display the corresponding time names at fixed intervals.

Next, let's move on to the next approach.

3, set the axis, expand the label interval as shown in the following figure, the timeline scale is relatively tight, we need to set the timeline: every 5 days to display a date scale name.

The old rule, let's take a look at the completion effect:

Specific operation methods:

❶ opens the window for formatting axes, click Abscissa, and hold down the shortcut key [Ctrl+1]

❷ sets Units: big to 5 days

After the setup is complete, we can achieve the effect map we want.

Knock on the blackboard:

Timeline timescale too many, we can set the timeline label interval, to achieve our goal.

4. Summarize that when we use timescale for visualization, sometimes due to too much timescale, insufficient width of the chart, incomplete display of the timeline and so on, we can use the following methods to solve the problem:

The ❶ refinement classification name is placed in the chart title.

❷ classifies and groups the timeline.

❸ sets the axis to enlarge the label interval.

Which method to use depends on your actual situation.

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

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.