Clang! Hello everyone~ I am Satellite-chan who is studying charts!
In data analysis, visualization is a very important step.
Advertisement
Normally, we put a lot of effort into beautifying charts.
But sometimes, “Choose the right chart“Far more important than beautification
For example, if you need to display amount and proportion at the same time; or if you need to display data in multiple columns of amounts, what kind of chart should you use?
Today Wei will use two examples to share the super useful ones.Multiple column bar chartBar!
Advertisement
1. Amount & proportion
Encountered this kind of data:
What chart will you choose to visualize it?
Bar chart + pie chart?
This requires two pictures, and the connection between the two columns of data is also severed;
Tree?
The tree diagram prefers to show the proportion relationship, and fails to make an obvious comparison of the amounts…
In fact, using a multi-column bar chart, you can combine the two:
Drawing points:Use auxiliary columns to create a[Stacked Bar Chart].
First, sort the data in ascending order and insert 2 auxiliary columns:
Among them, the data filled in auxiliary column 1 is “maximum sales value – sales volume + 200”:
=MAX($C$3:$C$11)-C3+200
(200 is an artificially set difference to prevent the two columns of data from being close together when making a bar chart.)
Auxiliary column 2 is used as the alternative data of “proportion” and is set to 1000.
Select B2:E11 and insert[Stacked Bar Chart].
Select the bar of “auxiliary column 1”, which is the orange section.
Right-click and select[No Fill]in[Fill]so that the gap between “Sales” and “Proportion” is completed.
Then we “cook” the percentage:
Select auxiliary column 2, the green part;
Right-click,[Add Data Label]select the data label, right-click[Set Data Label Format];
Check[Value in Cell]and select the “Percentage” column as the data source.
Uncheck[Value]and[Show Guide Lines].
Add details:
Select the bar representing the “Sales” column, similarly[Add Data Label]-[Set Data Label Format]and select[Inside Data Label]for[Label Position].
beautify:
Change font and fill color:
I found that the data labels were a bit uneven:
Change the numeric format:
It’s much more pleasing to the eye now
Adjust sales[gap width]to 65%.
Set a separate data label for the middle bar:
[Label position]also select[Inside data label]check[Value in cell]and select F2.
Finally, let’s beautify the little brand we disguised:
In this way, a bar chart that can display quantity relationships and percentage relationships at the same time is ready!
2. Multiple column amounts
When encountering the need to display multiple column amounts at the same time, such as the following picture:
Inserting the bar chart directly would be very crowded:
Slicers cannot be displayed at the same time;
Therefore, in this case, you can also use the above method to create a multi-column bar chart!
As we know from the previous article, multi-column bar charts are made by using auxiliary columns to create complementary stacked bar charts, and then hiding the auxiliary columns.
Therefore, based on the same principle, the data in this section can be processed into:
Does anyone know what kind of auxiliary columns are used in this picture?
Welcome to discuss and exchange in the comment area~
3. Write at the end
Well, today I will show you how to make a multi-column bar chart. Interested students can follow along and give it a try!