Create an Excel for Mac Pie Chart of Locations by Percentage
Question details
Create a pie chart that shows the percentage of people in each location from a dataset containing email addresses and locations.
- Product
- Microsoft Excel for Mac
- Device & OS
- macOS
- Scenario
- The worksheet has an Email Address column and a Location column, with repeated location names that need to be grouped and counted.
- Observed behavior
- The desired result is a pie chart with one slice per location and labels or values that represent each location's percentage of all records.
Make sure the dataset has a single header row, such as Email Address and Location, and that every record is in a continuous range with no completely blank rows or columns. Standardize location names first so equivalent entries, such as "New York" and "New York ", are counted together.
Create a PivotTable and Pie Chart from Location Counts
Use this method when your source data contains repeated locations and you need Excel to group them, count the records, and calculate percentages automatically.
A PivotTable is the most reliable way to summarize a two-column list because it combines identical location values before the pie chart is created. The Email Address field is counted as the number of people in each location.
Click any cell in the two-column dataset, or select the full range including the Email Address and Location headers.
On the Excel ribbon, select Insert > PivotTable. Confirm the table or range, choose where to place the PivotTable, and select OK.
In the PivotTable Fields pane, drag Location to the Rows area so Excel creates one row for each unique location.
Drag Email Address to the Values area. If Excel does not use a count, click the value field, choose Value Field Settings, and select Count.
Click the value field in the Values area, choose Value Field Settings > Show Values As, and select % of Grand Total. Select OK.
Select the location labels and percentage values in the PivotTable, then choose Insert > Pie Chart and select a 2-D Pie chart. Use Chart Design > Add Chart Element > Data Labels to display percentages on slices.
Use a Summary Table Before Creating a Pie Chart
Use this option when you prefer to build a separate location summary table rather than create a PivotTable.
Create a Location Percentage Pie Chart in WPS Spreadsheets
WPS Spreadsheets lets you summarize email records by location with a PivotTable, convert the counts to percentages of the grand total, and insert a pie chart with percentage labels.
- 1. Select the email and location list: Open the worksheet in WPS Spreadsheets and click any cell in the data range that includes the Email Address and Location headers.
- 2. Insert a PivotTable: Choose Insert > PivotTable, confirm the source range, select a destination, and create the PivotTable.
- 3. Set up the location count: Drag Location to Rows and Email Address to Values. Open Value Field Settings if needed and set the calculation to Count.
- 4. Convert counts to percentages: Open the value field settings, choose Show Values As, and select % of Grand Total.
- 5. Insert and label a pie chart: Select the PivotTable results, choose Insert > Chart > Pie, and enable percentage data labels in the chart settings.
Frequently Asked Questions
Why does my pie chart show a separate slice for every row instead of each location?
A pie chart does not automatically group repeated labels in the original data. Create a PivotTable with Location in Rows and Email Address counted in Values, then create the chart from that summary.
How do I show percentages instead of counts in an Excel PivotTable on Mac?
Click a value in the PivotTable, select Value Field Settings, open Show Values As, and choose % of Grand Total. The counts are then displayed as each location's share of all records.
Can I keep both the number of people and the percentage in the PivotTable?
Yes. Drag Email Address into the Values area twice. Set one value field to Count and set the second one through Value Field Settings > Show Values As > % of Grand Total.
How do I display percentage labels on an Excel pie chart for Mac?
Select the pie chart, use Chart Design > Add Chart Element > Data Labels, and then open Format Data Labels to select Percentage. Clear Value if you only want percentages.
Why are similar locations counted as separate categories?
PivotTables treat different text entries as separate values. Check the Location column for spelling differences, extra spaces, abbreviations, or inconsistent capitalization, then correct the source data and refresh the PivotTable.
