How To Add Percentages to Pie Chart in Excel

by admin

Find out how to add percentages to a pie chart in Excel by following along with this effortless Excel tutorial.

When you use the Insert Pie Chart tool, you often find that the data labels don’t show the percentage values. To highlight key data points in your pie chart and represent data accurately, you might want to add the percentage sign.

In this effortless tutorial, I’ll show you the proven and best methods to add percentages in Excel pie charts.

Enter Data Series in Percentages

Instead of expressing your values in whole numbers like 40, 25, 20, and 15 enter the same values in actual percentages, like 40%, 25%, 20%, and 15%.

Then, if you activate the Data Labels feature for your pie chart, Excel will show the data series values in percentage.

Let’s say the dataset you’re working on looks similar to the above screenshot.

Multiply the dataset by 1%

Go to a blank cell in the same worksheet and type 1%.

Press Ctrl + C to copy the cell.

Select all the values in column B in the above dataset and press Ctrl + Alt + V.

You’ll see the Paste Special dialog box.

Click on the Multiply radio button and click OK.

You’ll see that the values have been transformed into percentages.

Choose a 2-D Pie chart

An alternative way to express your chart series data in a column in percentages instead of regular digits is the Format Cells dialog box.

Applying a custom number formatting

Select the values you want to insert in a pie chart and press Ctrl + 1 to bring up the Format Cells dialog box.

Click on the Custom number formatting option below the Category column.

Now, enter the 0% custom code into the Type field on the right side of the Format Cells Number tab.

Click OK to apply the formatting style.

Converted regular values to percentages

Now, create a pie chart from the Insert > Insert Pie drop-down and choose either 2-D Pie or 3-D Pie from the context menu.

Enable Chart Data Labels to add percentages

Click on the Chart Elements button (the plus icon) and checkmark the Data Labels checkbox.

You should see the percentage values of the input data series for the Excel pie chart.

Using the Chart Elements Tool

Let’s say you’ve already created the pie chart using the existing dataset. Also, you don’t want to list the input values in percentages. In this case, you can use the Chart Elements tool on an Excel pie chart.

Checkmark Data Labels

Click on the pie chart.

You’ll see a plus icon in the top-right corner of the chart object. That’s the Chart Elements tool. Click on that.

You should see the Data Labels checkbox. Checkmark that option.

More Options in Data Labels menu

You’ll see a left arrow on the Data Labels element. Click on that. A new context menu will open.

Click on More Options.

Choose Percentage in Format Data Labels

You’ll see the Format Data Labels sidebar on the right side.

There, click on the Percentage checkbox below the Label Options section.

You’ll now see Excel pie chart data labels with percentages instead of regular values.

Using the Data Callout Tool

When you enable data labels along with percentages on your pie chart, each slice might seem cluttered. Also, data labels on the slices are less readable.

Here comes the Data Callout element at your rescue.

Select your existing pie chart

If you’ve already created the pie chart, select that.

I’m considering that you’ve also enabled Data Labels already.

Activate Data Callouts

Now, click on the Chart Elements icon (the plus icon) and hover the cursor over the Data Labels option.

On the overflow menu, click on the Data Callout option.

Adding percentages in a pie chart using Data Callouts

You’ll see callout boxes on the outer periphery of the pie chart with percentage values of the data series.

Using the Quick Layout Tool

The Quick Layout tool of the Chart Layouts commands block in the Chart Design tab allows you to apply predefined layouts to your charts quickly. This is a convenient way to improve the chart’s appearance and readability.

You can automatically adjust elements like titles, legends, gridlines, and data labels, including percentages by selecting from various layout options. No need to manually configure each component.

Quick Layout drop-down menu

Select your pie chart object on the Excel worksheet.

Go to the Chart Layouts block on the Chart Design tab and click on the Quick Layout drop-down menu.

Showing percentages using Layout 6

You’ll see multiple pie chart examples in the drop-down menu. Choose one that has percentages in the data labels.

Using the Chart Styles Tool

When you add a pie chart using the Insert Pie tool of the Charts commands block in the Insert tab, Excel adds a generic chart.

However, you can choose from various stylish pie charts from the Chart Styles commands block in the Chart Design tab. The Chart Design tab will only show up if you’ve added the generic pie chart.

Select chart to show Chart Design

Let’s say you’ve added the generic pie chart. Click on it to bring up the Chart Design tab.

Click on the Chart Style drop-down

Go to the tab and click on the Chart Styles drop-down menu.

You’ll see a list of professional pie chart styles.

Hover the mouse cursor on a style to see a preview of the final style.

If you like the style, click on that to transform your generic pie chart into a stylish one.

Choose Style 3 in Chart Styles

Choose a style that includes data labels with percentages.

Here are the styles that include percentages:

  • Style 3
  • Style 8
  • Style 10
  • Style 11

Using the Format Data Labels Tool

Add Data Labels

Go to your pie chart and click on it. Right-click and click on the Add Data Labels option to add values for each slice.

Deselect the pie chart

Now, click outside the chart to deselect it.

Format Data Labels

Right-click again on the chart and click on the Format Data Labels option on the context menu.

Choose Percentage

The Format Data Labels sidebar will pop up on the right side.

Checkmark the Percentage checkbox in the sidebar to get percentage values for pier chart slices in Excel.

Using Excel VBA Macros

Want to learn how to add percentages to a pie chart in Excel automatically using visual instructions? You can use the Excel VBA scripting technique.

In this method, you just need to run a VBA macro. The macro will walk you through the process visually. You’ll simply need to enter simple data like the worksheet name, chart number, etc. Then, Excel will add percentages in place of regular numbers for data labels.

Setting up and running a VBA macro is super easy. First, go through this quick Excel tutorial to learn the process of creating a macro from a VBA script:

Now, use the following script to create a macro:

A VBA script to show percentages in a pie chart
Sub ShowPercentageForPieChartLabels()
    Dim ws As Worksheet
    Dim chartName As String
    Dim chartObj As ChartObject
    Dim chartSeries As series
    Dim i As Long
    
    ' Prompt for worksheet name
    On Error Resume Next
    Set ws = Worksheets(InputBox("Enter the worksheet name:"))
    On Error GoTo 0
    
    If ws Is Nothing Then
        MsgBox "Worksheet not found. Please enter a valid worksheet name.", vbExclamation
        Exit Sub
    End If
    
    ' Prompt for chart name
    chartName = InputBox("Enter the chart name to be modified:")
    
    On Error Resume Next
    Set chartObj = ws.ChartObjects(chartName)
    On Error GoTo 0
    
    If chartObj Is Nothing Then
        MsgBox "Chart not found. Please enter a valid chart name.", vbExclamation
        Exit Sub
    End If
    
    ' Modify data labels
    With chartObj.Chart
        Set chartSeries = .SeriesCollection(1)
        For i = 1 To chartSeries.Points.count
            chartSeries.Points(i).DataLabel.Text = Format(chartSeries.Values(i) / Application.WorksheetFunction.Sum(chartSeries.Values), "0.00%")
        Next i
    End With
End Sub
The Macro dialog box

If you’ve set up the macro already, press Alt + F8 to launch the Macro dialog box.

Select the ShowPercentageForPieChartLabels macro and hit Run.

Enter source sheet name

There will be a dialog box where you need to enter the target worksheet name, like Sheet1.

Then, you need to enter the chart number on the source worksheet, like Chart 7.

If you enter the values correctly, you’ll see a confirmation dialog box.

How to add percentages in Excel pie chart using VBA

When you click OK, the changes will be visible in the selected pie chart.

⚠️ Warning: You won’t be able to undo actions on an Excel workbook when you use a VBA macro. So, create a backup workbook before trying the VBA-based method.

Using Office Scripts

Office Scripts lets you automate tasks on an Excel worksheet using simple codes written in TypeScript. You can share these scripts with friends, colleagues, and collaborators online so they can also implement the automation in their workbooks.

Entering the Office Scripts code

To try out this method, go to the source worksheet containing the pie chart.

Now, go to the Automate tab and click on the New Script button inside the Scripting Tools commands block.

You’ll see the Code Editor sidebar on the right side.

Delete the existing code, if any. Then, copy and paste the following script inside the Code Editor:

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet
    const sheet = workbook.getActiveWorksheet();

    // Get all charts in the worksheet
    const charts = sheet.getCharts();

    if (charts.length > 0) {
        // Assuming there's only one chart, activate it
        const chart = charts[0];
        chart.activate();

        // Set data labels to show percentages only
        chart.getDataLabels().setShowValue(false); // Hide regular data labels
        chart.getDataLabels().setShowPercentage(true); // Show percentage values
        chart.getDataLabels().setShowCategoryName(false); // Hide category names
        console.log("Data labels set to show percentages for the active chart.");
    } else {
        console.log("No chart found. Please ensure you have an active chart.");
    }
}
Added percentages with Office Scripts

Click on the Save script button to save the code for the future.

Now, hit the Run button to add percentages to a pie chart in the active worksheet.

Conclusions

So far, you’ve learned how to add percentages to a pie chart in Excel in 8 different methods.

These techniques include Excel user interface options, Excel VBA, and Office Scripts.

Choose the method that best suits your Excel usage style and preference.

If the article helped you or you’d like to share critical feedback, comment below! Don’t forget to share any better methods than the ones mentioned here.

You may also like

howtotecklogo

Keeping you informed on current tech and tutorials 

Copyright © 2024 HowToTeck.com – All Right Reserved.