Ever found yourself wishing you could display your Excel data in a more engaging and informative way? A doughnut chart with a total in the middle might just be the solution you're looking for. This type of chart not only presents your data in an appealing manner but also allows you to highlight the total at a glance. Let's dive into how you can create an Excel doughnut chart with a total in the middle.
Before we get started, ensure you have a dataset ready. For this example, let's assume we're working with sales data for different regions: North, South, East, and West. Our goal is to create a doughnut chart that shows the percentage of total sales each region contributes, with the total sales figure prominently displayed in the center.
Preparing Your Data
To create a doughnut chart with a total in the middle, you'll first need to prepare your data in a specific way. You'll need two sets of data: one for the percentages and one for the total.
In our example, let's say the total sales are $100,000. The regional sales are as follows: North ($25,000), South ($30,000), East ($25,000), and West ($20,000). First, calculate the percentage of total sales each region contributes. Then, add a row for the total sales at the bottom.
Calculating Percentages
In a new column, use the formula `=sales/100000` to calculate the percentage of total sales for each region. For instance, in the North cell, the formula would be `=25000/100000`.
Now, your data should look like this:
| Region | Sales ($) | Percentage of Total |
|---|---|---|
| North | 25000 | 0.25 |
| South | 30000 | 0.3 |
| East | 25000 | 0.25 |
| West | 20000 | 0.2 |
| Total | 100000 | 1 |
Creating the Doughnut Chart
Now that your data is prepared, it's time to create the doughnut chart. Select your data, including the header row, and go to the 'Insert' tab. Click on 'Pie' or 'Other Charts' and select the 'Doughnut Chart' option.
Excel will create a basic doughnut chart. To display the total in the center, right-click on the chart and select 'Add Data Labels'. Then, right-click on the data labels and select 'Format Data Labels'. In the 'Format Data Labels' pane, under 'Label Options', uncheck 'Value From Cells'. In the 'Label' field, enter the total sales figure (e.g., 100000). Adjust the position and size of the label as needed.
Customizing Your Doughnut Chart
With the total now prominently displayed in the center, you can further customize your doughnut chart to make it more engaging and informative.
For instance, you can change the color scheme to match your brand or the theme of your presentation. You can also add a title to the chart and format the data labels to display percentages instead of values. To do this, right-click on the chart and select 'Add Chart Element' > 'Chart Title' or 'Data Labels', respectively.
Formatting the Data Labels
To display the data labels as percentages, right-click on the data labels and select 'Format Data Labels'. In the 'Format Data Labels' pane, under 'Label Options', check 'Value From Cells'. In the 'Value' field, enter the formula `="%"&SERIESNAME()`. This will display the data labels as percentages (e.g., North - 25%).
You can also adjust the font size, color, and other formatting options to make the data labels more readable and visually appealing.
Adding a Chart Title
To add a title to your doughnut chart, right-click on the chart and select 'Add Chart Element' > 'Chart Title'. Enter your desired title in the 'Has Title' field. You can also format the title to match the rest of your chart.
For example, you might title your chart "Regional Sales Performance (2022)" to provide context for your data.
And there you have it! A customized Excel doughnut chart with a total in the middle, ready to impress your colleagues or clients. Don't forget to experiment with different color schemes, data labels, and titles to make your chart truly unique. Happy charting!