VBA & Macro Problems

How to Create Concentric Circles from Cell Percentages using Excel VBA

Muhammad Talha Sep 30, 2026 871 views

Question details

The user wants to generate an infographic in Excel consisting of concentric circles whose sizes correspond to percentage values in specific cells.

How to Create Concentric Circles from Cell Percentages using Excel VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating the creation of visual data representations (infographics) using macros to draw proportional shapes based on spreadsheet data.
Observed behavior: The macro needs to draw circles, scale them properly so the largest is in the back, center them, apply specific colors and outlines, add text labels, and group the final shapes together.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and that your spreadsheet is saved as a Macro-Enabled Workbook (.xlsm) to prevent losing your VBA code.

Solution 1Recommended

Use a VBA Macro to Generate and Format Concentric Circles

Write a VBA script that loops through your data range, calculates the size of each oval shape based on the cell's percentage, and layers them sequentially from largest to smallest.

The logic requires processing the data so that the largest circles are drawn first and placed in the background. The script scales the shapes using a multiplier, centers them at specific coordinates, applies colors, adds text, and groups them into a single cohesive infographic.

Open the VBA Editor

Press ALT + F11 in your Excel workbook to launch the Visual Basic for Applications Editor.

Insert a New Module

Click 'Insert' in the top menu and select 'Module' to create a blank workspace for your macro.

Write the Loop for Shape Generation

Create a sub-routine that loops through your target range (e.g., A1:A7). Inside the loop, use the 'ActiveSheet.Shapes.AddShape(msoShapeOval, Left, Top, Width, Height)' method to create the circles.

Scale and Position the Circles

Set the Width and Height properties dynamically by multiplying the cell's percentage value by a scaling factor. Adjust the Left and Top properties to ensure all circles share the same center coordinates.

Format Colors and Add Text

Use '.Fill.ForeColor.RGB' to assign specific colors to each circle. To add text, use '.TextFrame2.TextRange.Text' and set it equal to the cell's value.

Group the Shapes

Store the names of the created shapes in an array during the loop. Outside the loop, use 'ActiveSheet.Shapes.Range(shapeArray).Group' to bundle them together, then close the editor and run the macro.

Use a VBA Macro to Generate and Format Concentric Circles

Sort Your Data First: Ensure your percentage data is sorted in descending order before running the macro. This guarantees the largest circles are drawn first, preventing them from obscuring the smaller inner circles.

Efficient Data Visualization with WPS Office

Create and Run Macros Seamlessly with WPS Spreadsheets

WPS Office provides robust support for VBA and macros, allowing you to automate tasks and create stunning infographics like concentric circles directly from your spreadsheet data with ease.

1. Open WPS Spreadsheets: Download and install WPS Office, then open your macro-enabled workbook in WPS Spreadsheets.
2. Access the Developer Tools: Navigate to the Developer tab (ensure VBA support is installed) and click on the 'Visual Basic' or 'Macro' button.
3. Run Your Visual Macro: Insert your VBA code for concentric circles, select your data range, and click 'Run' to generate the grouped infographic instantly.

Fully compatible with Microsoft Excel VBA macros and .xlsm formatsLightweight architecture runs complex loops and shape generation quicklyFamiliar user interface for immediate productivity and easy data visualizationFree basic features alongside powerful developer tools

microsoft office alternative - wps office

Frequently Asked Questions

How do I add text labels inside the VBA-generated circles?

In your VBA macro, reference the created shape object and utilize the '.TextFrame2.TextRange.Text' property. You can set this property equal to the cell's value or a custom text string to display labels inside each circle.

Why are my smaller circles hidden behind the larger ones?

Excel renders shapes in the order they are created, stacking new shapes on top of older ones. If a larger circle is drawn after a smaller one, it will cover it. You must either loop through the largest percentages first or use the '.ZOrder msoSendToBack' method on the larger shapes.

How can I automatically group the concentric circles after creating them?

During your VBA loop, store the name of each generated shape in a string array. Once the loop is complete, use the 'ActiveSheet.Shapes.Range(myShapeArray).Group' command to combine all individual circles into one movable object.

Can I assign specific colors to the inner and outer circles?

Yes. Within your VBA loop, you can use a 'Select Case' statement or an array of RGB color codes based on the loop counter. This allows you to apply unique formatting, such as a green fill for the outer circle and red for the innermost circle.

Excel & Data Problems