We have already created an advance Excel Chart that shows the progress of the task in an Excel doughnut chart. In this article, we will learn how to create a creative fragmented circular chart in excel. By fragments, I mean a circular chart (doughnut or pie) that is divided into equal parts and progress is shown by highlighting these fragments, as shown in the gif below.
So how do we create this circular chart in Excel? Let's learn by doing it.
To create such an advanced excel chart, here, I have a percentage of survey completion in cell B3. It is currently 20%. In cell C3, I have the remaining percentage which is of course 1-B3.
Now to create the progress chart follow these steps:
Step 1: Insert a blank doughnut chart.
Click anywhere on the sheet and insert a donut chart. Make sure that you have not selected any data.
To insert the blank Doughnut Chart in Excel, go to Insert --> Charts --> Doughnut Chart.
Step 2: Add 10 equal data values to create a fragmented doughnut.
To create an equally divided doughnut chart, we need to add a data series that have equal values. To do so follow these steps:
--> Right-click on the chart area and click on Add Data Option.
--> Click on the Add button in the legend entries segment.
--> In Series name, write "Frags". In Series values, add 10 equal values to divide the donut into 10 equal parts. I write 1 ten times in curly braces. See the image below.
Step 5: Color the doughnut in one color. Reduce the hole size, if you like.
Currently, all the fragments of the chart have different colors. We need them to be the same.
Right-click on the doughnut chart and click on the format data series option.
From fill and line option, choose solid fill. Choose the color you like. I choose Green.
Optionally, from Series Option, reduce the whole size to 50%.
Step 6: Remove Chart Title and Legends. Change the chart area into a square.
Since the chart title and legends are not required here, remove them.
Since we only need the area of the doughnut, make the chart area into a square by setting the width and height of the chart area to the same size.
Select the chart area --> Go to Format menu --> Size. Set height and width to 2.
Half the work is done. Half is remaining.
Step 7: Add Data series of the progress percentage.
We have the fragments ready. Now we need to add the actual data series. To do so,
Right-click on the chart --> click on Select Data option --> In Legend Entries, click on Add button. In Series values, select range B3:C3. Hit the OK button.
It adds a new doughnut to the chart. The blue part shows the 20% completion of the survey and orange shows the remaining part.
Step 8: Make the percentage series a secondary series.
Right-click on any data series on the chart, and click on the Change Series Chart Type option. Here, on series 2, check the secondary series option. It will make the secondary series doughnut cover the first doughnut chart.
Step 8: Make the Completion Percentage Invisible:
Double click on the blue part (survey completion percentage) of the chart. Go to format --> fill and border. Here select No Fill. It will make the series below (frags) visible. We can now see 20% of frags.
Step 9: Color the Remaining Series with Translucent White Color:
Double-Click on the Orange Part (remaining percentage). Go to Fill --> Solid Fill. Select White or Black color and adjust the transparency to 50%. You can play with color and transparency.
The chart is ready. Now we need to add a numeric percentage to show the exact percentage of completion of the process in the center of the chart.
Step 10: Add a Text Box in The Center of The Chart and Show the Percentage there.
Select the chart. Go to Format --> Insert Shapes --> Select Text Box. Drag it in the center of the doughnut chart.
While having selected the text box, click in the formula bar and give the reference of cell B3 (survey completion percentage). This makes the text box dynamic. It will show the values of Cell B3.
And the advanced excel progress chart is ready to rock the business dashboards and percentage. As the percentage of completion changes the chart will change. This advanced donut chart will visualize the progress of the task that can easily be understood and look visually professional at the same time.
In the gif of the beginning of the article, I have used an Excel Map Chart as the background. Made the background of these charts invisible. You can download the exact advanced excel chart below:
Fragmented Progress Chart in Excel.
So yeah guys, this how you can make a cool and professional excel advanced chart that shows the progress of the process. I tried to be explanatory. I hope it helped you. If you have any doubt regarding this article or have any other
Excel/VBA related article query, ask in the comments section below.
Related Articles:
How to Create a Circular Progress Chart in Excel | A circular progress chart is easily digested by the management. If you can have an impressive progress bar in your presentation you can leave a good impression on your clients. But the regular progress bar is so mainstream. How about a dynamic circular fill-in progress infographic in Excel.
How to Create Color Changing Thermometer Chart in Excel | You can have a normal bar chart to visualize information but how cool will it be if you can show it on a Thermometer like Excel Graph. Even cooler, if the chart changes color when the value of risk or goal increases or decreases.
How To Create Speedometer (Gauge) Chart in Excel | Excel Speedometer or Gauge graph is one of the most popular chart types that attract managers. It’s easy to understand. It visualizes achievement or growth rate creatively.
How to Create Milestone Chart in Excel | A milestone chart shows the date or time when a milestone achieved in a graphical way. This graph must be easy to read, Explanatory, and visually attractive.
Creative Column Chart that Includes Totals | To include the total of the clustered column in the chart and compare them with another group of the columns on the chart is not easy. Here, I have explained, how to smartly include totals in the clustered column chart.
4 Creative Target Vs Achievement Charts in Excel |Target vs Achievement charts is a very basic requirement of any excel dashboard. In monthly and yearly reports, Target Vs Achievement charts are the first charts the management refers too and a good target vs Achievement chart will surely grab the attention of management.
How to Highlight When Line Drops or Peaks in Comparison Excel Chart | To compare two series in Excel chart and highlight the low and peak areas, we can use this advanced excel chart.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to Use The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to Use COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
