Using Data shapes

Result in a shape

If you have a lot of information in a column you need to scroll to the bottom to find a result, you can have the contents of that cell displayed in a shape at the top of the worksheet.

To create a formula in a shape:

  • From the Insert ribbon, in the Illustrations group, choose Shapes
  • Select and draw a shape when you want to view it
  • Click on the shape to select it
  • In the Formula Bar type =
  • Then click on the cell from which you want to display the contents (probably at the bottom)
  • Press Enter, your cell contents are displayed. Any changes you make to the data will automatically appear in the shape.

Data in a shape

If you ever wanted the cell contents (e,g, the result) to appear in a shape, this is how you do it:

  • Type a number into a cell, for example in C5 type 250
  • From the Insert ribbon, in the Illustrations group, choose Shapes
  • Choose any shape a draw it on the screen
  • Select the shape and in the Formula Bar type your reference (e.g. =C5)
    Now whatever appears in C5 will appear in the shape

To generate a random number between 1 and 100:

  • Select a cell and type =RAND()*100
  • To reset it press F9

Here is an exercise that will marry these last two together:

  • In A1 type Last Number in range
  • In B1 type 100 (which will be the reference for the amount of numbers)
  • In C5 type =RAND()*B1
  • Format C5 so it has no decimals
  • From the Insert ribbon, in the Illustrations group, choose Shapes
  • Choose any shape a draw it on the screen
  • Select the shape and in the Formula Bar type =C5  Now whatever appears in C5 will appear in the shape
  • With the shape still selected, make the font size large
  • Move the shape so it hides C5
  • To choose another number press F9 on your keyboard