Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create Square Pie Chart in Excel (2 Easy Ways)

Square Pie Chart is a kind of synchronized grid with an equal number of rows and columns. Usually, it shows the dataset value in percentage. Suppose the total work of a project is 100% which requires 100 cells (10 rows & 10 columns). Here, one cell represents 1% of the total amount of work to be done in that project. It is also known as a Waffle Chart. The focus of this article is to explain how to create a Square Pie Chart in Excel.

square pie chart excel


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


2 Easy Ways to Create Square Pie Chart in Excel

In this article, we will cover 2 easy ways of creating a Square Pie Chart in excel. To create a Square Pie Chart, we applied Conditional Formatting and the Developer tab. Let’s explore the methods.


1. Use Conditional Formatting to Create Square Pie Chart in Excel

The first one is a basic Square Pie chart that shows only one value. Here, we applied a new rule to the cells with the help of Conditional Formatting. So, let’s imitate the steps to create a square pie chart in excel.


Step-01: Make Square Chart Within Grids

In this step, we will create the Square Grid Chart by applying a formula instead of writing the values manually.

  • Initially, it requires a grid of 100 So, take 10 rows and 10 columns and write the formula in the first cell of the last row which is Cell B13 here.

=(COLUMNS($E13:E$13)+10*(ROWS($E13:E$13)-1))/100

Applying a formula to create square pie chart in excel

  • Secondly, press Enter, and you will get the value which is 01 as it will start from 1 %.

Output from Formula

🔎 How Does the Formula Work?

  • ROWS($E13:E$13): In this portion, the ROWS function returns the number of rows of the array.
  • 10*(ROWS($E13:E$13)-1))/100: Now, we subtracted 1 from the previous result and then multiplied it by 10. Finally, the result is divided by 100.
  • COLUMNS($E13:E$13): Here, the COLUMN function returns the number of columns in array $E13:E$13.
  • (COLUMNS($E13:E$13)+10*(ROWS($E13:E$13)-1))/100: Finally, this formula returns the summation of the previous two portions.
  • Afterwards, drag the Fill Handle to copy the formula through the width and height of the grid. Follow the image below.

Dragging Fill Handle to create the grid

  • As a result, we got the results for the entire grid.

Using AutoFill feature to create the grid

  • To make your grid look more prominent, add a thick border outside.
  • To do this, hit on the Border icon pointed below.
  • Then, select Thick Outside Borders from the options.

Inserting thick border outside the grid

  • Consequently, you can see a thick border outside the grid.

Grid used to create square pie chart in excel

Read More: How to Format a Pie Chart in Excel


Step-02: Link Cell with Square Pie Chart

As we will be using Conditional Formatting, create a linked cell on your sheet.

  • First, select any cell beside your chart. And this cell will be further used in Conditional Formatting.
  • Then, add a header above the linked cell for better understanding.

Creating a Linked cell to apply conditional formatting

Read More: How to Make a Pie Chart in Excel [Video Tutorial]


Similar Readings


Step-03: Change of Font Color

To make the chart more professional we will hide the percentage values.

  • In the beginning, click on the font color icon.
  • Next, change the font to white.

Changing the font color to hide the value in square pie chart in excel

  • Accordingly, all the values of the grid are hidden.

Change of Font colour

Read More: How to Change Pie Chart Colors in Excel (4 Easy Ways)


Step-04: Employ Conditional Formatting

This is the most important step of the entire process. You have to apply a new rule to the grid by conditional formatting to make it look like a square pie chart.

  • Select the entire grid.
  • Then, Select the Home From this tab, click on conditional formatting.
  • Among the options, choose New Rule.

Applying conditional formatting to create square pie chart

  • Consequently, a dialog box will open like the snapshot below.
  • Next, select Format only cells that contain from the options.
  • Then, insert cell value 0 and $M$5. Here, M5 is the linked cell we inserted previously. The value you will put down in this cell will be shown in the chart.
  • Lastly, click on the Format option so that we can format the font and fill color.

 Inserting new formatting rule

  • Now, the Font color and Fill color need to be the same.
  • Select the Font color of your preference.

Changing font color in conditional formatting

  • Then, select the Fill Color same as the Font Color.
  • In conclusion, hit on the OK button after checking the preview.

Changing Fill color in conditional formatting

  • We are done with formatting.
  • Lastly, select OK to see the result.

Preview after formatting

  • After that, write down a value in the linked cell.
  • As a result, you will get the desired square pie chart.

A square pie chart in excel

To increase the readability of the chart, follow the next steps. This results in showing up the value of the percentage in the last cell. It will be easy to detect the value just by looking at the chart.

  • First, select the 100 cells.
  • Then, click on the Home tab.
  • Next, choose Conditional Formatting from the options.
  • After that, select Highlight Cells Rules from the options.
  • Lastly, pick the Equal To option.

Highlighting cells rules in conditional formatting

  • Set the linked cell M5 as Format cells that are equal to.
  • After this, pick out the Custom Format option.

Formatting cells that are equal to highlighted cell

  • Next, change the Font color to white.
  • Then, hit on the OK.

Changing font color to white

  • Finally, you can see the percentage value on the last cell.

Output of highlighted cell

Read More: How to Show Percentage in Excel Pie Chart (3 Ways)


Similar Readings


Step-05: Add Label

You can insert a Text Box to show the percentage at the top of the chart.

  • First, Select the Insert option.
  • Then, Insert a Text Box.

Inserting text box

  • In the text box, insert the linked cell M5. It means the text value will take the value of the linked cell.

Editing the text box

  • Accordingly, the text box shows the value of M5.

Final output of a square pie chart in excel

Read More: Excel Pie Chart Labels on Slices: Add, Show & Modify Factors


2. Create Interactive Square Pie Chart Using Developer Tab in Excel

Let’s move forward to the second method. It is the interactive one that expresses multiple values in a single sheet. For this, we will introduce the Developer tab to add options. Follow the steps carefully to create an interactive square pie chart in excel.

Suppose we have a dataset containing some products and the percentage of the quantity which is ready to deliver. If we want to express these values into a single dataset, then we can utilize the Developer tab.

Dataset to create an interactive square pie chart in excel

Steps:

  • First and foremost, you must create a square pie chart within Grids.
  • To do this, follow Step-01 of Method 1.
  • Secondly, Change the Font colour of the cells of the grid. For this, go after Step-03 of Method 1.

Creating the grid chart

  • In order to represent the square pie chart for multiple values you should apply some formulas according to your need.
  • To do so, put down the formula in the marked cell. The INDEX function is used here.

="Quantity ready to deliver for "&@INDEX(B5:B9,P6)

Applying Excel INDEX function to create an interactive square pie chart

🔎 How Does the Formula Work?

  • INDEX(B5:B9,P6): This part returns the value of array B5:B9 to the reference cell P6.
  • “Quantity ready to deliver for “&@INDEX(B5:B9,P6): This formula will bring the values from column B. If the P6 cell remains empty, it will show an error message. That’s why we have put the value 1 here to avoid the error.
  • We added some formatting by changing the Fill color to make the sheet look good. You can also do this.

Interactive square pie chart before applying rules

  • Now, insert a Text Box at the bottom of the grid to express the value in a detailed way. Follow Step-05 of Method 1 to insert text.
  • Then, edit the text according to your information on the percentage value.

Adding text box in interactive square pie chart in excel

  • Finally, apply Conditional Formatting to create the square pie chart. Perform Step-04 of Method 1 to do this. Here, we selected Cell P4 as a linked cell.
  • Now, the interface is ready now to use the Developer tab.

Output of text box

  • In the first place, click on the Developer tab.
  • Afterwards, Select the Insert option.
  • Here, select the Option Button from From Control.

Introducing Developer tab

  • Edit the option. The name of our first product is Monitor.
  • Next, right-click on the text, and a box will appear like the image.

Adding option from developer tab

  • In the control option insert the cell link as Cell P6. This cell will show the number of options.
  • Put a tick on 3-D shading.

Format Control Dialog Box

  • Consecutively, add the 5 options buttons just repeating the same actions.

Inserting 5 Option buttons

  • At this point, apply this formula to the marked cell which is the linked cell of the conditional formatting. This formula will bring the quantity from column B.
=INDEX(C5:C9,P6)

Applying INDEX formula for creating an interactive square pie chart in excel

Here, in the INDEX function, we selected C5:C9 as array and P6 as row_num. The formula returns the value of the cell in the function.
  • So, we have successfully created an interactive square pie chart in excel.
  • Then, click on different options and see the result in the grid.

An interactive square pie chart in excel

Read More: How to Make a Pie Chart in Excel (5 Suitable Examples)


Things to Remember

  • You have to remember that you can only present data in percentages.
  • Making a Square Pie Chart is a bit time-consuming compared to other charts in Excel.

Practice Section

Here, we have provided a practice sheet for you to practice.

Practice Section for Creating Square Pie Chart in Excel


Conclusion

We have tried to show creating a square pie chart in excel step by step. Small details have been shown so that you can create a square pie chart at least possible time by following the article. If you face any problem regarding the article, please comment so that we can help.


Related Articles

Era

Era

Hello! Welcome to my Profile. Recently I have been graduated from Bangladesh University of Engineering and Technology in Civil Engineering. Being a fresh graduate, I want to build up my skill in article writing about Microsoft Excel and VBA. I am also interested in research and development. I believe in learning something new every day and implementing my knowledge more effectively.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo