Pie and doughnut charts in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate percentage for pie chart slices

You have sales data in cells A2:A5 and total sales in cell A6. Which formula in B2 correctly calculates the percentage share of sales in A2 for a pie chart?

A =SUM(A2)/$A$6

B =A2/A6

C =A2/$A$6

D =$A$6/A2

💡 Hint

Use absolute reference for total sales to keep it fixed when copying the formula.

The formula =A2/$A$6 divides the sales in A2 by the total sales in A6 using an absolute reference to keep the denominator fixed when copying down. This gives the correct percentage for each slice in the pie chart.

❓ Function Choice

intermediate

Best function to sum categories for doughnut chart

You want to create a doughnut chart showing total sales by category. Which function best sums sales for each category listed in column A with sales in column B?

A MAX(B:B)

B COUNTIF(B:B, ">0")

C AVERAGE(B:B)

D SUMIF(A:A, "Category1", B:B)

💡 Hint

You need to add sales only for one category at a time.

The SUMIF function adds values in column B where the category in column A matches the specified category. This is perfect for summing sales by category for a doughnut chart.

🎯 Scenario

advanced

Fixing incorrect pie chart slice sizes

You created a pie chart but the slices do not match the sales data percentages. What is the most likely cause?

A The data range includes empty cells or text values.

B The chart type is set to doughnut instead of pie.

C The chart title is missing.

D The worksheet is protected.

💡 Hint

Pie charts need clean numeric data to calculate slice sizes correctly.

If the data range includes empty cells or text, Excel cannot calculate correct slice sizes, causing the pie chart to display incorrectly.

❓ data_analysis

advanced

Analyzing doughnut chart data labels

You want to show both category names and percentages on doughnut chart labels. Which setting combination achieves this?

A Enable 'Series Name' and 'Value' in data label options.

B Enable 'Category Name' and 'Percentage' in data label options.

C Enable 'Legend' only and disable data labels.

D Enable 'Value' only in data label options.

💡 Hint

Data labels can show multiple pieces of info if selected.

Choosing both 'Category Name' and 'Percentage' in the data label options displays the category and its percentage on each slice of the doughnut chart.

🧠 Conceptual

expert

Understanding doughnut chart ring levels

You have a doughnut chart with two rings showing sales by region (outer ring) and by product (inner ring). What does the size of each slice in the inner ring represent?

A The proportion of product sales within the total sales of its region.

B The total sales of the product across all regions.

C The total sales of the region regardless of product.

D The average sales of all products combined.

💡 Hint

Doughnut charts with multiple rings show hierarchical data.

The inner ring slices represent product sales as a part of their specific region's total sales shown in the outer ring. This shows the breakdown within each region.