Dashboard 101 – Working With MS Excel Shapes

Working With MS Excel Shapes

Shapes are instrumental in the design of any dashboard. Besides their use in static headers and outlines, they can also be used to display dynamic content on the dashboard. This page covers the various options that MS Excel provides for using shapes in your dashboard.

Let’s begin with the basics. Shapes can be added by going to Insert > shapes on the MS Excel window. This allows you to choose from a variety of shapes.

Inserting shapes

Once you have chosen a shape-type you can draw one according to your requirement. However, the default shape type is usually not very appealing—aesthetics point of view. MS Excel provides a variety of option to apply design to you shapes. While there are some preset designs—which are very neat—you can always apply additional effects. So let’s look at the preset ones first. While you have selected the shape you just drew, go to the drawings tab> format> shape style

shape style

Here you would find a large number of preset designs that can dramatically improve the look and feel of your shapes.

types of shapes

Apart from these presets you can control a large number of effects by going to ‘shape effects’ in the ‘shape style’ menu. This menu allows you to apply different kinds of shadow, reflections, glow, edges, and rotations to your shape.

shape effect

Till now we have only talked about how we can format shapes, but the real beauty of shapes lies in the fact that they allow you to display dynamic content. How? Rather than writing something in the shape, you can assign it a cell reference—see the image below; it’s really easy.

shapes formula

So how does it become dynamic? Simple, the cell that you refer to in the shape has to be dynamic i.e. it must contain a formula. This trick is very useful while creating dynamic headers for dashboards and it also works for text boxes and word arts. The only limitations are that:

  1. Through this method you can only show texts up to 255 characters long—my guess is that you would ideally never need a header that’s 255 characters long.
  2. The other limitation is that the shape can only refer to a cell and not a formula itself

To see how shapes can be used to enhance the visual appeal of dashboards, you can download any of our Crab Project and see what wonders shapes can do. Also, you can download this workbook for an example explaining the dynamic shapes concept.

Feel free to reach out to us at query@crabsheet.com in case you face any difficulties in this section and we would be happy to address your queries.



Have a question? just leave a comment and we will answer A.S.A.P