You can create more interest and give your charts infographic styling when you create Excel charts with Shapes. There’s no limit to the type of shapes you work with, but there are some techniques required to ensure they don’t get distorted when working with data of different values.
Watch the Video
Enter your email address below to download the sample workbook.
How to Build Excel Charts with Shapes
Start by inserting a regular column chart. Then insert the shape you want to use. Make sure it’s roughly the same size as the largest column in your chart.
CTRL+C to copy the Shape > Select the columns in the chart > CTRL+V to paste the shape.
Tip: add data labels and remove the gridlines and vertical axis.
Caution: the shape will be stretched to fit the different column sizes. In the chart above it’s barely noticeable, however see the next step if this is an issue.
Working with Multiple Shapes
You can use multiple shapes to create a more interesting shape. For example, in the chart below I have circle shapes at the top of my columns. This requires a bit more set up so that the shapes don’t become distorted when your chart has different sized columns.
The data for the chart is split between the column and the circle which add up to the total value for the column:
The enables you to insert a stacked column chart for the column and circle values. The ‘Value’ column is not plotted in the chart.
Create the shape for the top of the column. In my example I’m using a circle sitting in front of a rectangle which I’ve grouped together to make one shape:
Tip: you may need to zoom in to align the circle and rectangles perfectly before the next step.
Copy and paste the shape onto the red circle series:
Tip: Turn off the gridlines for a cleaner look. You won’t need them with data labels anyway.
Now you can add labels to the circle using ‘Value From Cells’ (available in Excel 2013 onward):
Tip: Repeat for the horizontal axis using the data labels for the bottom series of the chart allowing you to turn off the vertical and horizontal chart axes:
You can use more shapes and more series as required. For example, you might want a different bottom to the column:
Want more chart tips and tricks, please consider my Excel Dashboard course and check out this tutorial on infographics in Excel.
Brilliant as usual !
This time: brilliant in it’s simplicity 🙂
Thanks so much, Danny!