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

Hello, Hi, I am the uncle Mr Zhao, who prefers function formulas and likes to use Excel charts to manage warehouses.

Today I would like to share with you a very interesting chart-waffle pie chart.

What is a waffle chart?

A waffle chart is named because it is similar to a waffle, also known as a right-angled pie chart, and is often used to show percentage data.

As shown in the figure below, if you want to show that a product accounts for 45% of sales, it is represented by 45 red squares.

So what's the advantage of using a waffle chart?

Compared with the traditional pie chart, it can display the percentage data more accurately and intuitively.

Using the waffle chart, we can present different percentages of categories, as shown in the following figure:

In addition, you can use a waffle chart to show the dynamic progress of a task.

As shown in the following figure, it shows red when the completion rate is less than 60%, yellow at 60%, and green at 80%.

All these are done by using the conditional format in Excel.

Next, let's talk about how these cool charts are realized.

1. Classified percentage ❶ first constructs a set of values with 10 rows and 10 columns.

Enter the formula in cell B2:

= SEQUENCE (1010) the effect is as follows: "B2:K11" cell range.

The SEQUENCE function, a new function of Microsoft version 365, can generate a series of consecutive numbers in an array.

If you are a small partner in an earlier version, you can enter the following formula in cell B2, and then drag the fill down to the right.

= 10*ROW (A1) + COLUMN (A1)-10

❷ conditional formatting.

Select "B2:K11" cell range-[conditional formatting]-[Management rules]-[conditional formatting Rule Manager]-[New Rule]-[use formulas to determine the cells to be formatted]-enter the formula and set the corresponding formatting color.

= B2=$N$2*100=B2=SUM ($N$2:$N$3) * 100=B2SUM ($N$2:$N$3) * 100

The effect is shown in the following figure:

❸ chart beautification

The next step is to adjust the row height and column width of the 100 cells to a square style.

As shown in the following figure, select the "BRV K" column and adjust the width to 20 pixels.

Then select the "2:11" line and adjust the height to 20 pixels.

Then add a white border to the cell range, remove the checked gridlines, customize the cell format, and enter three semicolons ";;" to hide the value.

The final effect is shown in the following figure:

2. Dynamic progress ❶ first constructs a set of values with 10 rows and 10 columns:

Enter the following formula in cell B2:

= 100-ROW (A1) * 10+COLUMN (A1)

❷ select "B2:K11" cell range-[conditional formatting]-[Management rules]-[conditional formatting Rule Manager]-[New Rule]-enter a formula and set the corresponding formatting color.

= B2IFFESTEN 10.6 ($EOB1YOBLING 100L0)

❸ select the "B2:K11" area, customize the formatting of the cell, enter three semicolons ";;", and hide the value.

At this point, even if the production steps are introduced, is it very simple?

3. To sum up, this article explains other tricks of the waffle chart, which is often used to show the percentage of data. Compared with the pie chart, the waffle chart (10 ⅹ 10) is more accurate in displaying the percentage of data, so it is also used by many people.

Main steps:

To make a waffle chart in a conditional format, ❶ first needs to construct a set of 100 ordered values with 10 rows and 10 columns.

❷ then writes the formula in the conditional format and sets the format of the corresponding condition

❸ adjusts the row height and column width to a square shape to beautify the chart.

Ladies and gentlemen, have you mastered the waffle chart made in conditional format? Remember to use it at work.

If my article is useful to you, remember to forward it, like it, and watch it again. Next time I'll talk about other cool charts!

Learn some Excel every day, work efficiency up~up~. I'll see you tomorrow!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhao Jiaoyang, Editor: 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.