Chart & Visualization Issues

How to Automate Pie Chart Creation for Multiple Excel Rows

Jason MillerJason Miller Oct 20, 2026 869 views

Question details

The user needs to generate pie charts for approximately 90 rows of data (6 columns each) with specific custom colors for each slice.

How to Automate Pie Chart Creation for Multiple Rows in Excel
Product
Excel
Device & OS
not provided
Scenario
Visualizing large datasets row by row without having to manually create and format dozens of individual charts.
Observed behavior
Requires an automated workflow to either dynamically update a single chart based on row selection or batch-create 90 individual charts with custom RGB colors.
Before you start

Ensure your data is organized in clean rows and columns without blank rows in between. If you plan to use a macro, verify that the Developer tab is enabled in your ribbon to access the VBA editor.

Solution 1Recommended

Create a Single Dynamic Pie Chart

Instead of generating 90 separate charts, build one chart that updates automatically based on the selected row for better performance.

A single dynamic pie chart is much easier to manage than 90 separate charts and prevents your workbook from becoming slow and bloated.

1
Set up helper cells

Create a reference cell (e.g., A100) that holds the row number you want to view. Use the INDEX function in adjacent cells to pull data for the 6 columns based on this row number.

2
Insert the Pie Chart

Highlight the helper data row, go to the 'Insert' tab, and click the 'Pie Chart' icon to insert a standard 2D pie chart.

3
Customize slice colors

Double-click on the chart slices one by one. Go to 'Format Data Point' > 'Fill' and manually set the colors in order: blue, yellow, red, green, black, and white.

4
Change rows dynamically

Whenever you change the number in your reference cell, the INDEX function updates the data, and the chart instantly reflects the new row while keeping the custom colors.

Create a Single Dynamic Pie Chart
Interactive Viewing: You can link a Form Control Spin Button or Dropdown list to your reference cell to easily flip through your 90 rows without typing.

Automate Charts Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful charting tools, seamless dynamic data referencing, and VBA (Macro) support to help you automate visual data creation quickly and effortlessly.

  1. 1. Download and Open: Download WPS Office and open your data file in WPS Spreadsheet.
  2. 2. Set Up Dynamic Data: Use lookup formulas or the built-in drop-down lists in WPS to create a dynamic data source row.
  3. 3. Insert Pie Chart: Go to the Insert tab, select Pie Chart, and apply your desired custom colors to the slices.
  4. 4. Enable Macros (Optional): If you prefer batch creation, enable macros in WPS and paste your VBA script into the WPS Macro Editor to generate all charts at once.
Full compatibility with Microsoft Excel (.xlsx and .xlsm) formatsSupports advanced VBA macros for batch chart generationEasy-to-use chart formatting tools for custom slice colorsLightweight performance even with complex dynamic formulas
microsoft office alternative - wps office

Frequently Asked Questions

Can I change chart colors automatically without VBA?

Yes, you can define a custom color palette or theme in the Page Layout tab. However, forcing specific colors to specific data points (like blue strictly for column 1) consistently across multiple separate charts is best achieved using VBA or by formatting a single dynamic chart once.

Why is my dynamic pie chart not updating when I change the row?

Ensure that your calculation options are set to 'Automatic' in the Formulas tab. Also, verify that your dynamic named ranges or INDEX formulas correctly reference the target row and that the chart's data source points to the helper cells, not the static data.

Is creating 90 pie charts in one worksheet a good idea?

Generating a large number of charts can significantly impact workbook performance, increase file size, and make the sheet slow to navigate. Using a single dynamic chart that changes based on a dropdown selection is highly recommended for better performance.