Chart & Visualization Issues

How to Create an Automatically Updating Top 10% Chart in Excel

Kushani Nimanthika Oct 9, 2026 869 views

Question details

The user needs an Excel pie chart that automatically updates to display the top 10% of performing records dynamically as new data is added over time.

How to Create a Dynamic Top 10% Chart in Excel

Product: Excel
Device & OS: not provided
Scenario: Visualizing the highest-performing 10% of records in a dataset that receives new entries without needing manual range adjustments.
Observed behavior: The chart needs to reference a dynamic output (via array formulas and summary tables) rather than being tied to a fixed data range.

Before you start

Ensure you are using a version of Excel that supports dynamic array formulas (such as Microsoft 365 or Excel 2021) and consider organizing your raw data into an official Excel Table (Ctrl+T) so the source range updates automatically.

Solution 1Recommended

Create a Dynamic Summary Table and Pie Chart

Use dynamic array formulas to extract the top records and a summary table to feed a dynamically updating pie chart.

To make a chart dynamically update, it cannot point directly to a static range. Instead, you need to extract the top 10% of records using dynamic formulas and aggregate them into a summary table that the pie chart references.

Convert Raw Data to a Table

Select your source data and press Ctrl+T to create an Excel Table. This ensures new entries are automatically included in your formula calculations.

Calculate the Top 10%

In a new cell, use a combination of SORT, CHOOSEROWS, SEQUENCE, and COUNTA to extract the top 10% of records based on your performance metric.

Extract Unique Categories

Use the UNIQUE function on the extracted data to pull a distinct list of categories (e.g., courses, regions, or products).

Calculate Totals with SUMIF

Next to your unique list, use the SUMIF function to calculate the total values for each distinct category extracted in the previous step.

Insert the Pie Chart

Select the dynamic summary table containing the unique categories and their SUMIF totals, go to the Insert tab on the ribbon, and click on Pie Chart. The chart will now update automatically as the source table expands.

Create a Dynamic Summary Table and Pie Chart

Dynamic Arrays: Using dynamic arrays means the summary table will automatically expand or contract based on the total number of records, keeping the chart accurate without manual adjustments.

WPS Office Chart Tools

Create Dynamic Charts Easily in WPS Spreadsheet

You can accomplish the exact same dynamic charting tasks using WPS Spreadsheet. It fully supports dynamic array formulas, advanced charting tools, and smooth data visualization, making it easy to analyze top-performing records.

1. Open your data in WPS: Launch WPS Spreadsheet and open your existing workbook containing the raw data.
2. Apply dynamic formulas: Set up your UNIQUE and SUMIF formulas in a dedicated summary area to capture the top 10% metrics.
3. Insert Chart: Highlight the summary area, navigate to the Insert tab on the ribbon, and select 'Chart' to insert a dynamically updating Pie chart.

Fully compatible with Microsoft Excel formulas and charts (.xlsx format).Supports advanced functions like SORT, UNIQUE, and SEQUENCE.Intuitive charting tools that update instantly as underlying data changes.Free, lightweight, and fast alternative for robust data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Excel chart not updating when I add new data?

If your chart references a static range (like A1:B10), new rows won't be included. Converting your source data to an Excel Table (Ctrl+T) or using dynamic array formulas ensures the chart range expands automatically.

What versions of Excel support dynamic array formulas?

Dynamic array functions like SORT, UNIQUE, CHOOSEROWS, and SEQUENCE are available in Microsoft 365 and Excel 2021 or later. Older versions require alternative methods like OFFSET or INDEX/MATCH.

How do I find the top 10% without dynamic arrays?

If using an older version of Excel, you can use the PERCENTILE.INC function in a helper column to flag records in the top 10%, then use a Pivot Chart filtered by that helper column to visualize the results.

Can I use a Pivot Table to automatically show the top 10%?

Yes! You can create a Pivot Table from your data, apply a 'Value Filter' to show the Top 10 Percent, and then insert a Pivot Chart. Just remember to right-click and 'Refresh' the Pivot Table when new data is added, as Pivot Tables do not update in real-time.

Excel & Data Problems