Excel Donut Chart: Display Total in Middle

Ever found yourself wishing you could display the total of your Excel data right in the middle of a donut chart? While Excel doesn't natively support this, we can achieve this with a bit of creativity and the use of some helper columns. Let's dive into how you can create an Excel donut chart with the total in the middle.

Before we begin, ensure you have your data ready. For this example, let's assume we have a simple dataset with categories (A, B, C) and their respective values (10, 20, 30). We'll use this data to create a donut chart with the total (60) in the middle.

Preparing Your Data

To create a donut chart with the total in the middle, we first need to prepare our data. We'll use a combination of SUMIF and INDEX functions to achieve this.

Let's start by creating a new column for the total. In cell D2, enter the following formula: `=SUM(B2:B4)`. This will calculate the total of your data (60 in our case).

Creating a Helper Column for the Donut Chart

Next, we'll create a helper column to make the donut chart creation easier. In cell E2, enter the following formula: `=IF(D2=0, "", D2)`. This will return an empty string if the total is 0, otherwise, it will return the total.

Now, drag this formula down to copy it for the rest of your data. This column will help us create a donut chart with the total in the middle.

Formatting the Data for the Donut Chart

With our helper column ready, we can now format our data for the donut chart. Select your data (A1:E4) and go to the 'Insert' tab. Click on 'Pie' or 'Donut' to insert a chart. Excel will automatically create a pie chart based on your data.

To convert this into a donut chart, right-click on the chart and select 'Format Selection'. In the 'Format Selection' pane, under 'Chart Style', choose a donut chart style.

Adding the Total to the Middle of the Donut Chart

Now that we have a donut chart, let's add the total to the middle. Right-click on the chart and select 'Add Chart Element' > 'Text'. This will add a text box to your chart.

In the text box, enter the total from cell D2 (or wherever you've placed it). To center it, right-click on the text box and select 'Format Text Box'. In the 'Format Text Box' pane, under 'Position', adjust the 'X' and 'Y' values to center the text box.

Formatting the Total in the Donut Chart

To make the total stand out, you can format it. Right-click on the text box and select 'Format Text Box'. In the 'Format Text Box' pane, under 'Fill & Line', you can change the color and border of the text box. Under 'Font', you can change the font, size, and color of the text.

You can also add a background shape to the text box to make the total more prominent. Right-click on the chart and select 'Add Chart Element' > 'Shape'. Choose a shape (like a circle or a rectangle), and place it behind the text box.

And there you have it! You've successfully created an Excel donut chart with the total in the middle. This technique can be applied to any dataset, allowing you to display the total right where you want it.