Pie charts are widely used in Excel and this time I will show you a method to create this type of chart within a cell.
It is important to mention that this is not a default functionality of Excel, but we will use a special type of font that will allow us to use these mini charts in our reports or dashboards.
To achieve this type of report, you must follow 2 steps:
- Download and install on your computer the font that will allow us to display the mini pie charts in an Excel cell.
- We must create a formula that will determine the character to be displayed within the cell.
In the following image you can see the final result after having installed the font and having created the formula that will display the appropriate character:
Install the font for mini pie charts
The first step is download source It contains the little graphics icons. This font is called “”And contains all the necessary icons, starting with the empty circle that corresponds to the letter“ a ”and ending with the filled circle that corresponds to the letter“ u ”.
Once you have downloaded the file, you must install the font on your computer and you can achieve this in the following way:
- Unzip the ZIP file that you have downloaded and access the folder.
- Right click on the PIE4MAP.TTF file and choose the option .
- If a confirmation dialog is displayed, you must click OK.
With these steps you will have installed the necessary font to create mini pie charts in Excel. Next, we will create the formula that will help us to display them correctly within the cell.
Create the formula to display the mini pie charts
Once the font is installed, open the Excel sheet where you have the data you want to graph. In the following example I have a table with the percentage of progress for each month of the year so I will use those percentages to create the appropriate mini chart for each month.
Column C of my sheet will contain the mini charts so I will place the following formula in cell C2:
The value of cell B2 is multiplied by 100 because it is expressed as a percentage and therefore it is a value between 0 and 1. By multiplying it by 100 we will be evaluating an integer between 0 and 100. This same formula must be copied to all the cells in column C where the mini charts will be displayed.
It is important to mention that, after entering the formula, you will not see the mini graphs, but the column will show letters that can go from the letter “a” to the letter “u” as you can see in the following image:
To see the mini graphics you must select the cells in column C and change the font type to “”Which is the font we installed in the previous section.
Ready, at this point you should have the pie charts inside the cells. We could improve the formula a little by evaluating the 100/5 operation in advance and leaving it as follows:
Optionally you could apply conditional format to give it the look you require, such as the color of the mini graphic, according to the percentages they represent.
Explanation of the formula
To understand how the formula we just created works, it is necessary to understand some details of the source “”That we install on our equipment.
If we open the Windows Character Map to view all the characters in that font, you will see that it is a collection of small icons where each one has a different fill percentage.
In the image above, inside the red border, you will find a complete series of icons, that is, starting from an empty circle to a full circle. If you count the number of icons that this series contains, you will realize that there are 21.
The first character has the hexadecimal value 0x61 which is equivalent to the decimal number 97 and which in turn is equivalent to the letter “a” in ASCII code.
Since we have 21 icons available, we can create the following table in Excel starting with the letter “a” and until reaching the letter “u” which will be the equivalent of the filled circle.
Column B shows the characters in Excel’s default font type, which allows us to display the letters “a” and even the letter “u”. In contrast, column C shows the same letters of the alphabet, but after changing the font type to “”.
Column A shows that the 21 icons will allow us to go from the number 0 to the number 100 in intervals of 5, and we will use that behavior to represent values such as percentages in Excel.
This means that, given a number between 0 and 100, our formula should help us find the corresponding letter to represent the percentage.
To understand the construction of our formula, it is necessary to understand the ASCII code that determines a numerical value for each character. In Excel, it will be CODE function the one in charge of obtaining said code number.
In the following image you can see that in column D I have used the CODE function to obtain the code of each of the characters in column C.
The first objective is to obtain the ASCII code of the letter that corresponds to any number between 0 and 100 and for that I will use the following formula:
Assuming that the number that I am interested in evaluating is in cell G1, the formula obtains the code for the letter “a” and the INT function you will get the integer part of the division of the number in cell G1 by 5.
In other words, the result of the INTEGER function will help us to obtain the number of “jumps” that we must take from the letter “a” to reach the equivalent character. If the INTEGER function returns zero, we will stay at the letter “a”, but if it returns the number 20 then we will move to the letter “u”.
The following image shows an example where we have the number 50 in cell G1 and in cell G2 we enter the previous formula to obtain the number 107, which corresponds to the ASCII code of the letter “k” and which at the same time represents the filled circle 50%.
In this way we can obtain the equivalent ASCII code for any value between 0 and 100 and we will only need to convert said code into a letter and for that we will use the CHARACTER function as follows:
The following image shows the formula returning the letter “k” which is the character that corresponds to the 50% filled circle.
With this formula we can evaluate any number between 0 and 100 to obtain the letter of the font type “Which will adequately represent the percentage.
Finally, I must say that a limitation of this method is that, when sharing your Excel file with someone else, it will be very likely that they will not be able to see the pie charts inside the cells because they will not have the required font installed.
In order for the other person to see the mini graphics, they will need to download and install the font on their own computer.