How to Instantly Improve Your Excel Charts [Watch This!]

Introduction

This tutorial will guide you through the process of creating dynamic circle charts in Excel that effectively display progress. By following these steps, you'll learn how to utilize pivot tables and various Excel features to enhance your data presentation. Additionally, we'll explore alternatives that save space while still conveying important information.

Step 1: Set Up Your Data

• Ensure your data is organized in a table format. For this tutorial, we will use ratings data from a bike company.
• If you are using a pivot table, insert it into your existing worksheet to facilitate interactivity.
• Right-click on your data table and select "Insert Pivot Table."

Step 2: Create and Format the Pivot Table

• Drag the category field into the slicer to filter your data by product.
• Change the value field from summing ratings to averaging them:
  • Right-click the value field in the pivot table, choose "Value Field Settings," and select "Average."
• Format the numbers as percentages without decimals:
  • Right-click the values, select "Format Cells," and choose "Percentage."

Step 3: Add a Balance Column

• Create a new calculated field for balance:
  • Name the field "rating Bal."
  • Use the formula =1 - [Rating] to calculate the remaining percentage.
• Add this field to your pivot table.

Step 4: Prepare Segment Values

• To create chart segments, add a new series called "Segments."
• Use the SEQUENCE function to generate 30 equal segments:

  =SEQUENCE(30,1,1,0)
  

• If SEQUENCE is unavailable, manually enter "1" in 30 cells by typing "1" in one cell, selecting it, and using Control + Enter.

Step 5: Create the Doughnut Chart

• Select the "Segments" data and insert a doughnut chart:
  • Go to the "Insert" tab, select "Pie Chart," and choose "Doughnut."
• Adjust the slicer format to display four columns for better alignment.

Step 6: Format the Chart

• To improve clarity, remove the chart legend.
• Format the segment colors:
  • Right-click on a segment, select "Format Data Series," and choose a solid fill (e.g., petrol color) with a white border.
• Link the chart title to the corresponding cell in your pivot table:
  • Select the title and enter the cell reference.

Step 7: Add Rating Values to the Chart

• Right-click the chart and select "Select Data."
• Add a new series for the rating values:
  • Name it using the appropriate cell and select the values you want to display.
• Change the chart type for this series to use a secondary axis to overlay it on the segments.

Step 8: Finalize the Chart

• To make the top series shine through, select it and set the fill to "No Fill."
• Format the balance segment with a solid fill and set transparency to 25%.
• Adjust the chart shape to have no fill and no outline for a cleaner look.

Step 9: Duplicate and Edit the Charts

• Copy the completed chart for other products by holding down Control + Shift and dragging it.
• Update the titles and linked cells for each chart.
• Use the Format Painter to copy formatting from the first chart to others.

Step 10: Add Percentage Display

• Insert a text box to show the percentage:
  • Link the text box to the appropriate cell value.
• Adjust the text box properties (font, size, color) to match your theme.

Step 11: Explore Alternative Chart Types

• If space is a concern, consider using alternative chart types:
  • Thermometer Chart: Use a stacked column chart that fills with the average rating and leaves the balance as no fill.
  • Bullet Chart: This is useful for displaying qualitative performance bands.

Conclusion

You have now created dynamic circle progress charts in Excel and explored alternatives that can fit into tighter spaces. As you apply these techniques, remember to keep your data clear and visually engaging. For additional resources, consider downloading the example file linked in the video description and explore tutorials for thermometer and bullet charts. Happy charting!