Chart & Visualization Issues

How to Create a Pie Chart from Totals in Multiple Excel Tables

Khadija Khan Oct 13, 2026 869 views

Question details

The user needs to create a pie chart that displays each category's total cost as a percentage of the overall budget, using source values that are stored across multiple separate Excel tables.

How to Create a Pie Chart from Totals in Multiple Excel Tables

Product: Excel
Device & OS: not provided
Scenario: Visualizing combined financial or categorical data from several distinct tables into a single percentage-based pie chart.
Observed behavior: Pie charts require a single consolidated data series to accurately display percentages, meaning the data from multiple tables must be merged or consolidated before generating the chart.

Before you start

Ensure that the multiple tables you want to consolidate have identical row and column labels, as this allows the consolidation tool to correctly match and sum the corresponding categories.

Solution 1Recommended

Consolidate Data and Insert a Pie Chart

Use the Data Consolidate feature to combine your multiple tables into one summary table, which provides the proper format needed to generate an accurate pie chart.

A pie chart can only plot one data series at a time. To chart data scattered across multiple tables, you must first calculate the combined totals. The built-in Consolidate tool is the fastest way to aggregate this data without complex formulas.

Select a destination cell

Open your spreadsheet and click on an empty cell in a blank area of your worksheet where you want the new consolidated summary table to be created.

Open the Consolidate tool

Navigate to the 'Data' tab on the top ribbon and click on 'Consolidate' in the Data Tools group.

Add table references

In the Consolidate dialog box, ensure the Function is set to 'Sum'. Click inside the 'Reference' field, select the range of your first table (including headers), and click 'Add'. Repeat this process to add the ranges of all the tables you wish to combine.

Configure labels and finalize

Under 'Use labels in', check the boxes for 'Top row' and 'Left column' so Excel correctly aligns your categories. Click 'OK' to generate the consolidated table.

Insert the Pie Chart

Highlight the newly created consolidated summary table. Go to the 'Insert' tab, click on 'Insert Pie or Doughnut Chart', and select your desired pie chart style. You can then use the Chart Elements button to add data labels and format them as percentages.

Automatic Updates: If you check 'Create links to source data' in the Consolidate dialog box, your summary table and pie chart will automatically update if the original data changes.

Easily Visualize Data with WPS Office

Create Pie Charts from Multiple Tables in WPS Spreadsheet

WPS Spreadsheet features powerful data consolidation tools and an intuitive chart creator, making it incredibly simple to combine multiple data sources and visualize them with stunning pie charts.

1. Consolidate Data in WPS: Open your workbook in WPS Spreadsheet, select a blank cell, go to the 'Data' tab, and click 'Consolidate'.
2. Select Table Ranges: Choose the 'Sum' function, add the ranges for each of your tables to the 'All references' list, check the label options, and click 'OK'.
3. Generate the Pie Chart: Select the newly combined data, navigate to the 'Insert' tab, click the 'Chart' icon, choose 'Pie', and easily apply percentage formats from the quick settings.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls) and charting structures.Built-in Data Consolidate feature for quick and accurate cross-table calculations.A rich library of professional 2D and 3D pie chart templates.Lightweight, fast, and completely free to download.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I select multiple tables directly when inserting a pie chart?

A pie chart is designed to plot a single data series to show the parts of a whole. Selecting disjointed tables confuses the charting engine because it cannot automatically sum the categories. You must first consolidate the data into one continuous table.

Can I use a PivotChart instead of the Data Consolidate feature?

Yes. If your tables are formatted identically or appended into a single master dataset using Power Query, you can create a PivotTable to summarize the data, and then insert a PivotChart to display your dynamic pie chart.

How do I display actual percentages on my newly created pie chart?

Click on the pie chart to select it, then click the 'Chart Elements' plus icon that appears next to it. Check 'Data Labels', click the arrow next to it, select 'More Options', and check the 'Percentage' box in the formatting pane.

Excel & Data Problems